AVERAGE with IF statement

**ComradeBT** · 02-01-2011, 11:58 AM

Hope somebody can quickly aid me on this one. Having a memory blank.
Please see attached example.

I wish to calculate the AVERAGE of a list of 'VALUE' of each 'TYPE'

e.g. IF 'TYPE' = 'A', then AVERAGE 'VALUE' = n

So I need to combine the above statement in one cell to apply in the example I've given.

Thanks,

Ben.

**NBVC** · 02-01-2011, 12:00 PM

Try:

=SUMIF($A$2:$A$17,D2,$B$2:$B$17)/COUNTIF($A$2:$A$17,D2)

where D2 contains just A or B or C...

**ComradeBT** · 02-01-2011, 12:05 PM

Thanks for the ultra-quick response. I had forgotten that there is an 'AVERAGEIF' function that works in exactly the same manner as 'SUMIF'.

Is there also a 'MAXIF' function to find the 'MAX' of a range of values to work in the same fashion?

**NBVC** · 02-01-2011, 12:23 PM

By your profile and attachment, I figured you were not using 2003, so that is why I did not mention AVERAGEIF.

There is no MAXIF function, however...

You would need an array formula:

=MAX(IF($A$2:$A$17=D2,$B$2:$B$17))

confirmed with CTRL+SHIFT+ENTER not just ENTER

**ComradeBT** · 02-02-2011, 06:28 AM

Genius! Thanks for that.

Why does the confirmation of CTRL+SHIFT+ENTER come into play on this when a normal 'ENTER' doesn't confirm the function?

Ben.