Confused with AverageIf Function

**CBJason** · 03-31-2013, 07:53 AM

Hi, new to the forum, been trying to pick up some advanced functions by reading and watching examples online (YouTube, Lynda.com etc., but none really speak to my problem...

So, I've got a table of roughly 20,000 rows and 30 columns of data. The two columns I am interested in are the ones titled "Driver" (in the B column) and "Idle Time" (in the R column). Each driver has multiple entries in the table of data, so I am trying to get an average "Idle Time" for each driver.

The first driver, "Aaron Anderson" for instance has approximately 209 entries so I used this formula:

=AVERAGE(R2:R211)

which produced his average. It quickly got very tedious to find the start and end fields for each of the nearly 100 drivers, so figured I might be able to improve my efficiency if I could get a more advanced formula in place - started researching and learned about AVERAGE IF

I watched a few videos and read some details on Microsoft help pages and believe I've got it conceptually:

AVERAGEIF(RANGE OF CELLS TO AVERAGE, LIMITER CRITERIA, and optionally the ACTUAL CELLS TO BE USED).

Since I was already using the actual cells, and the limiter criteria seemed to be what I would need, I tried this:

=AVERAGEIF(R2:R20068,"Aaron Anderson")

but got the dreaded #DIV/0 error.

So, I tried modifying the formula to specify both the Driver header title and the column letter

=AVERAGEIF(R2:R20068,Driver="Aaron Anderson")

=AVERAGEIF(R2:R20068,B="Aaron Anderson")

Same results...my head is starting to hurt. So, can someone help me correct my construction here?

**daddylonglegs** · 03-31-2013, 07:57 AM

Hello CBJason, AVERAGEIF has the range to average at the end (unlike AVERAGEIFS) so try this formula

=AVERAGEIF(B:B,"Aaron Anderson",R:R)

....or simpler to put your names in a list of cells e,g, Y2 down and use this formula in Z2 copied down

=AVERAGEIF(B:B,Y2,R:R)

**CBJason** · 03-31-2013, 08:09 AM

Awesome! Thanks, my logic must've been a bit off...

So, then, let's get more advanced...say I want to put the results of this data in a new sheet in the same workbook where I already have the names parsed out?

So, sheet1 has the actual data and sheet 2 has the column of names (as you suggested) parsed down to a single entry per name?

I tried this:

=AVERAGEIF('sheet1'!'B:B,'sheet2'!A4,'sheet1'!R:R)

But it got borked again. What would I need to fix to make that work?

**daddylonglegs** · 03-31-2013, 08:16 AM

You have an extra quote in there that you don't need (after the first !), so make it

=AVERAGEIF('sheet1'!B:B,'sheet2'!A4,'sheet1'!R:R)

and that should work, although you only need the quotes for sheet names with spaces (or numeric sheet names) so that could be just

=AVERAGEIF(Sheet1!B:B,A4,Sheet1!R:R)

**CBJason** · 03-31-2013, 08:21 AM

Talk about an awesome forum - I spent a few hours trying to learn it on my own, when I shoulda come here first and just asked away! LOL

And yeah, I made the sheet name generic in my above, it's a longer sheet name that gives out client data, which I obviously am a little leery about divulging in a public setting...LOL

**CBJason** · 03-31-2013, 08:33 AM

Ok, so now the next gotcha *sigh*

I pumped out the data into a new sheet, which consisted of a pivot table that included Driver, Summary Idle Time, and now Average Idle Time. If I want to do a pivot pie chart that shows how many drivers fall into certain threshholds (say top 33%, middle 33% and bottom 33%? Because the first two columns of data in the new sheet are a part of a generated pivot table, but the third set is a formula, it's not included in the previous pivot table data, so I am wondering how I can combine the two data sets into the new pivot chart.

Did that make sense?

Basically, if the Idle Time could be broken into 3rds (0-400 seconds, 401-800 seconds, and 801 - 1200 seconds) how many drivers would fall into each group?

**JosephP** · 04-01-2013, 03:49 AM

it would be simpler with a sample workbook but if you revert to calculating the average in the source data for the pivot you can use that as a row field in a pivot and group it or perform the grouping in the source data using a lookup table
by the way you could do the original average as part of a pivot without formulas but it won't then allow you to group those for your chart