Averaging a certain criteria.

**noodles1984** · 07-15-2010, 03:04 PM

Evening Folks,

This is only my second post since joining this site and like last time i have a predicament i need help with.

I'm fairly new to Excel and i'm picking up as i go along. So heres my predicament............

I've been tasked with monitoring how long my distribution centres drivers are delayed at stores based on an average. The problem is that i have a list a mile long of all the delays we've had this year.

What i want to do is calculate an average by identifying the unique store number then ONLY average the delays for that specific store.

The form is laid out:

Column1 = Store ID No.
Column2 = Store Name
Column3 = Volume delivered
Column4 = Volume collected
Column5 = Length of delay (Mins)

So i need to indentify the unique ID (column1) and average all the Delays (column4) for that store.

Any ideas guys??

**NBVC** · 07-15-2010, 03:08 PM

You can use a pivot table to do that...

Data|Pivot Tables...Next... make sure correct 4 column range.. click Next...

Click Layout, drag Store ID No from right side to Row area... then drag Length of Delay to Data Area... double click that dragged button and select Average.

Choose where you want Pivot table to go... click Finish.

**noodles1984** · 07-15-2010, 03:11 PM

Thanks for the help mate, that is the obvious choice and the best solution.....

But lets say for learning purposes, what would the formula be to achieve the same result?

Thanks again

**NBVC** · 07-15-2010, 03:22 PM

For learning purposes...

Make a list of unique IDs.. say in column F.. starting at F2...

then =Average(If($A$2:$A$1000=F2,$E$2:$E$1000))

Confirmed with CTRL+SHIFT+ENTER not, just ENTER.

In XL2007... AVERAGEIF(A:A,F2,E:E) copied down

or, in any XL version...

=SUMIF(A:A,F2,E:E)/COUNTIF(A:A,F2) if your list is very large...

copied down