Excel help! Calculating averages across multiple sub-categories
February 25, 2015 5:15 PM Subscribe
I only have a basic understanding of Excel functions. I know that it's possible to do what I want, but I'm not sure how to go about doing it. I need help figuring out the average amount of time that passes between the dates two particular actions happen across multiple different people. I'm looking for the overall average, as well as the average for each person. Complication, some of the rows don't have the action I'm interested in tracking, so that column reads as N/A and is messing up my formula.
I have a spreadsheet that contains details on work assignments for projects assigned to multiple employees. The basic data in the spreadsheet is among other things, 1) Name of the employee assigned the project, 2) Date the project was assigned, 3) Date a specific step in the project was taken, and 4) Date the project was completed.
I want to figure out the overall average for the department for the length of time that passes from the date assigned to the date the specific step was taken. I also want to see the average amount of time it takes each person to get to that specific step.
The problem I'm running into is that not all of the projects in the spreadsheet require that specific step. So some rows have a date in that step's field, and some rows have N/A. So if I just add a new column to calculate number of days between the two dates, the N/A sections mess up my results and throws off the averages calculation.
How do I set this up to get the data I want?
I have a spreadsheet that contains details on work assignments for projects assigned to multiple employees. The basic data in the spreadsheet is among other things, 1) Name of the employee assigned the project, 2) Date the project was assigned, 3) Date a specific step in the project was taken, and 4) Date the project was completed.
I want to figure out the overall average for the department for the length of time that passes from the date assigned to the date the specific step was taken. I also want to see the average amount of time it takes each person to get to that specific step.
The problem I'm running into is that not all of the projects in the spreadsheet require that specific step. So some rows have a date in that step's field, and some rows have N/A. So if I just add a new column to calculate number of days between the two dates, the N/A sections mess up my results and throws off the averages calculation.
How do I set this up to get the data I want?
You can start your formula with an if statement that says,
=if(isna( formula here),"",formula here)
That will remove #n/a and leave those spaces blank.
posted by Ruthless Bunny at 5:43 PM on February 25, 2015
=if(isna( formula here),"",formula here)
That will remove #n/a and leave those spaces blank.
posted by Ruthless Bunny at 5:43 PM on February 25, 2015
I'd start with Brainmouse's suggestion but, if you want to go the formula route and you have a reasonably current version of excel, Ruthless Bunny's error trapping can be simplified to:
posted by pompomtom at 6:47 PM on February 25, 2015
=iferror(formula_here,"")posted by pompomtom at 6:47 PM on February 25, 2015
Response by poster: brainmouse, that's the approach I tried at first, and the Average Difference Column in the pivot table populates as all #VALUE!
Not sure what the issue is then. I have a time to complete first step column, which is just "=Column2Value-Column1Value" where Column2Value = Date the first step was taken and Column1Value = Date the project was assigned. I've run the pivot table with Employee as the row label and the Time to Complete First Step as the value, with the Field Setting as summarize by Average.
Even adding the error trapping suggested above still results in a pivot table full of #VALUE!
posted by Arbac at 2:00 PM on February 26, 2015
Not sure what the issue is then. I have a time to complete first step column, which is just "=Column2Value-Column1Value" where Column2Value = Date the first step was taken and Column1Value = Date the project was assigned. I've run the pivot table with Employee as the row label and the Time to Complete First Step as the value, with the Field Setting as summarize by Average.
Even adding the error trapping suggested above still results in a pivot table full of #VALUE!
posted by Arbac at 2:00 PM on February 26, 2015
hmm, try again but when you're calculating the day column, make it
That will leave n/a's instead of errors or blanks in your cells, and the pivot table should definitely ignore those...
posted by brainmouse at 3:08 PM on February 26, 2015
=iferror(step_date-start_date,na())That will leave n/a's instead of errors or blanks in your cells, and the pivot table should definitely ignore those...
posted by brainmouse at 3:08 PM on February 26, 2015
OK, fine, I hate pivot-tables. Let's do this properly.
A: Name of employee
B: Date of assignment
C: Date specific step was taken (which may be an N/A error)
D: Date of completion
E:
F:
Which means that your department average would be:
and your employee average would be in column F.
(This all assumes setting everything up in row 1 without headers, and filling down. Season to taste.)
posted by pompomtom at 9:15 PM on February 26, 2015
A: Name of employee
B: Date of assignment
C: Date specific step was taken (which may be an N/A error)
D: Date of completion
E:
=IFERROR(IF(C1="","",C1-B1),"")F:
=SUMIF(A:A,A1,E:E)/COUNTIF(A:A,A1)Which means that your department average would be:
=AVERAGE(F:F)and your employee average would be in column F.
(This all assumes setting everything up in row 1 without headers, and filling down. Season to taste.)
posted by pompomtom at 9:15 PM on February 26, 2015
This thread is closed to new comments.
As usual with Excel, you almost certainly want to do this with Pivot Tables. Create that column to calculate the number of days between the two dates, the ones that have text or whatever in the date column should just calculate to "#VALUE", which is totally fine. Then create a pivot table, put Employee as the row label, and the difference column as the value. By default it will give you a sum, which you don't want, you'll need to right click on the values, select "Field Settings", and summarize by Average. The fields that are errors will just be ignored and not included in your averages, which is perfect.
posted by brainmouse at 5:20 PM on February 25, 2015 [4 favorites]