Average if for formula outputs

[Manikandan Arumugam]

Hi Good Noon All,

Expecting One more help from you all. I have an existing formula's that gives numeric values. Based on the values I would like to get an average values with criteria.
Ex: My existing formula (Manually Mocked Up data)
=(A1*A3+B1*B3+C1*C4+D1*D6+E5*E7)
Output
=0+5+10+0+15

The expected formula will have to average the values by excluding Zero's. So for the above scenario the output will be 10. (30/3). Please help me out. Thanks in advance

[XLent]

would something like:

=AVERAGE(IF(CHOOSE({1,2,3,4,5},A1*A3,B1*B3,C1*C4,D1*D6,E5*E7),CHOOSE({1,2,3,4,5},A1*A3,B1*B3,C1*C4,D1*D6,E5*E7)))
confirmed with CTRL + SHIFT + ENTER

work for you?

[Manikandan Arumugam]

Thanks for your time, Mate. Will check and get back to you..

[Limor_OP]

Nice Xlent.
For knowledge enrichment - can you please explain how does your IF formula return FALSE for the zeros in the array?

Thank you.

[XLent]

The FALSEs are generated by the IF; in XL any number other than 0 evaluates to TRUE so: IF(number,...) will only return FALSE if number is exactly 0.

As you infer - you want the individual results to be treated as an array - as we know:

=AVERAGE(1,2,FALSE) --> 1

whereas

=AVERAGE({1,2,FALSE}) --> 1.5

So, as the pattern of the individual calculations was inconsistent I used CHOOSE to enforce an 'array' of values (such that FALSE is ignored).

In scenarios where there is a pattern to the calculations the CHOOSE isn't required - e.g. if it were always row 1 * row 3 then you would just use:

=AVERAGE(IF(A1:E1*A3:E3,A1:E1*A3:E3))
CTRL + SHIFT + ENTER

I am sure there are other methods, the CHOOSE option was the first that came to mind.

[Manikandan Arumugam]

Thanks XLent. Its working fine. Thanks Again..

[Limor_OP]

XLent, thank you very much for the clear explanation!