AverageIf Formula Counting zeros grrr

**AllenMead** · 10-26-2010, 05:20 AM

I have the following formula which is counting zeros in column BX which I don't want it to

=AVERAGEIFS(Properties!$BX$3:$BX$1002,Properties!$R$3:$R$1002,{"Y","A"},Properties!$C$3:$C$1002,"CPHA")

Any help would be much appreciated

**DonkeyOte** · 10-26-2010, 05:34 AM

Add a further test to exclude 0's

=AVERAGEIFS(Properties!$BX$3:$BX$1002,Properties!$BX$3:$BX$1002,"<>0",Properties!$R$3:$R$1002,{"Y","A"},Properties!$C$3:$C$1002,"CPHA")

edit: I should add that the in line array as used above will not work - it will only be averaging Y (A is ignored).
If you want a weighted average you would need to use SUM(2 SUMIFS) / SUM(2 COUNTIFS) else use an Array *
*assumes number of Y/A is not identical and that you want don't want the Average of the 2 AVERAGEIFS results [if you did: simply encase above within AVERAGE]

**peterjuhnke** · 10-26-2010, 07:55 AM

Hello Allen
I see you still don't attach a dummy sheet. As I've suggested before to you, it makes it so much easier for someone to help you if you attached one.
They then don't have to try and imagine they way you have your spread layed out, they don't have to recreate what you already have infront of you.
Please when you are posting another question, attach a dummy sheet with an example of what you expect as an result.
Regards
Peter