Averageif query

[fable]

Hey guys,

Just looking for a quick bit of help.

Looking to use averageif for two conditions. Normally I'd use the averageifs formula, but, it's not two conditions on one value it's one value where it can be A or B. I've even tried the OR function with no success...

So, what I've tried to do is compensate and do it manually. This is giving me different answers no matter how I do it.

=(SUMIF(L:L,"A",A:A)+SUMIF(L:L,"B",A:A))/(COUNTIF(L:L,"A")+COUNTIF(L:L,"B"))

That should be a manual average, unless I'm mistaken, but the value it's giving me is way off what I'd be expecting. (i.e the above =77.... when the two individually average 90 & 84 respectively.)

So, I'm pretty sure this is wrong. anyone mind pointing out what I'm doing and a neater way of doing this?

Thanks in advance!

-fable

 

[Andrew Poulsom]

You didn't mention that you had text in column C. Those entries will be treated as zero. Try this:

=SUM(SUMIF(E:E,{"C","G"},C:C))/SUMPRODUCT(((E:E="C")+(E:E="G")),--ISNUMBER(C:C))

 

[fable]

I wasn't aware it would make a big difference as you can only average numbers. But, having tried that, curiously the averages still do not match. It now comes to 88.42 as opposed to the expected 88.05. Not a big difference, but there must be an error somewhere around there...

I would have expected there to be a simpler way of doing this.

 

[Andrew Poulsom]

88.05 is the average of all the data, not just the filtered data. If you want an average of the filtered data use:

=SUBTOTAL(1,C:C)