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

[DonkeyOte]

Re: SUMPRODUCT - you only need one SUMPRODUCT I think (Divisor). SUMIF should suffice as before for the first part of the equation.

Of course if the ranges were vast it would make sense to create a "key" to negate need for Array/Sumproduct altogether.

[jeffreybrown]

Of course if the ranges were vast it would make sense to create a "key" to negate need for Array/Sumproduct altogether.

Hi Luke,

This part lost me...What do you mean by a "key"?

Thanks, Jeff

[DonkeyOte]

What I'm getting at is that where the data sets being processed are vast (they aren't here) it's often advisable to create a key which contains all pertinent info. such that you can conduct multi conditional tests without need for Array/SUMPRODUCT (more so preXL2007 of course)

For ex. here we could create a key along the lines of:

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

It follows that given COUNTIF & SUMIF can work with wildcards one key can facilitate a whole variety of different tests where some of the concatenated fields are irrelevant etc...
(of course pending the tests creating an appropriate key may require some thought)

Mike Girvin has posted an excellent youtube clip of this approach if interested: http://www.youtube.com/watch?v=ry3vSqN1TQ0

This approach is something I use quite regularly on the boards on those threads related to poor performance (as they generally result from overuse of SUMPRODUCT / Arrays)

[jeffreybrown]

Thanks Luke I understand now.

[daddylonglegs]

Re: SUMPRODUCT - you only need one SUMPRODUCT I think (Divisor). SUMIF should suffice as before for the first part of the equation.

Correct - thanks Luke, I came over all SUMPRODUCT there, my suggestion could be just

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