Average a range based on criteria

**Plumby** · 02-14-2012, 05:44 AM

Hi,

I’m looking for a way to average a range of data based on criteria. I’ve attached an example worksheet (2007).

The result being that if the formula looked for “Test1” then it would return 25.7 (as the blue highlighted cells average 27.5).

I tried the below (inputting with CSE) but for some reason it gives me 9.7?!

=AVERAGE(IF(A:A="Test1",B:F ))

**daddylonglegs** · 02-14-2012, 06:01 AM

That formula will treat the blank cells as zero and distort the average. You can add another "clause" to filter out blanks, e.g.

=AVERAGE(IF(A2:A6="Test1",IF(B2:F6<>"",B2:F6)))

confirmed with CTRL+SHIFT+ENTER

Note: that will include any zeroes but not blanks. if you want to exclude zeroes too then change "" to 0

**Plumby** · 02-14-2012, 06:11 AM

Thanks ever so much DDL! Thats very quick and works perfectly! May I ask - what does this bit do (trying to learn)

<>""

I shall mark it as solved, etc.