An alternative to SUMIF to calculate the average of cells.

[BobTheRocker]

I am currently using the following code to find an average of all the numbers in the B column that contain an MALE in the A column. Cell A50 contains the total number of MALES in column A6:A45. This falls down when there is no corresponding data in the B column for one of the males. Is there anyway of calculating this so it only divides or averages by the number of cells that contain data?



[code]
=SUMIF(A6:A45,"MALE",(B6:B45))/A50
[code]

[ChemistB]

There might be a simpler formula but I came up with

=SUMPRODUCT(--($A$6:$A$49="male"),$B$6:$B$49)/SUMPRODUCT(--(ISNUMBER($B$6:$B$49))*($A$6:$A$49="male"))
Does that work for you?

[jeffreybrown]

You could also use...

=AVERAGE(IF($A$6:$A$45="MALE",IF($B$6:$B$45<>"",$B$6:$B$45)))

This is an array formula which needs to be confirmed with Ctrl + Shift + Enter.

If entered successfully the formula will be surrounded by {}

Do not enter {} manually...

[BobTheRocker]

Spot on, I've just tested that. Ive used SUMPRODUCT before but could not get my head round it. Thats brilliant thank you.

[BobTheRocker]

=SUMPRODUCT(--($A$6:$A$49="male"),$B$6:$B$49)/SUMPRODUCT(--(ISNUMBER($B$6:$B$49))*($A$6:$A$49="male"))

Ok is this acceptable to avoid errors, or is there an easier way?

=IF(ISERROR(SUMPRODUCT(--($A$6:$A$49="male"),$B$6:$B$49)/SUMPRODUCT(--(ISNUMBER($B$6:$B$49))*($A$6:$A$49="male"))),"NONE",=SUMPRODUCT(--($A$6:$A$49="male"),$B$6:$B$49)/SUMPRODUCT(--(ISNUMBER($B$6:$B$49))*($A$6:$A$49="male"))))

I've used this method of IF(ISERROR many times all over my spreadsheets but I'm not sure is very efficient.

[jeffreybrown]

Not sure your formula will work you have an extra equal sign in the middle. I do not know of a more efficient method; however, in 2007 you could incorportate the IFERROR function which would cut down on the formula.

One of...

=IF(ISERROR(SUMPRODUCT(--($A$6:$A$49="male"),$B$6:$B$49)/SUMPRODUCT(--(ISNUMBER($B$6:$B$49))*($A$6:$A$49="male"))),"None",SUMPRODUCT(--($A$6:$A$49="male"),$B$6:$B$49)/SUMPRODUCT(--(ISNUMBER($B$6:$B$49))*($A$6:$A$49="male")))

or

=IF(ISERROR(AVERAGE(IF($A$6:$A$45="MALE",IF($B$6:$B$45<>"",$B$6:$B$45)))),"None",AVERAGE(IF($A$6:$A$45="MALE",IF($B$6:$B$45<>"",$B$6:$B$45))))

Enter with Ctrl + Shift + Enter

[daddylonglegs]

If the error you want to avoid is #DIV/0! try

=SUMPRODUCT(ISNUMBER($B$6:$B$49)*($A$6:$A$49="male"),$B$6:$B$49)/MAX(1,SUMPRODUCT(ISNUMBER($B$6:$B$49)*($A$6:$A$49="male")))

or

=LOOKUP(9.9E+307,CHOOSE({1,2},0,AVERAGE(IF($A$6:$A$45="MALE",IF($B$6:$B$45<>"",$B$6:$B$45)))))

confirmed with CTRL+SHIFT+ENTER