Getting AVERAGE to ignore zero outcome from formulae

[gdcuk]

I have the following formula to average the outcome of three calculations:

=IF(I24="","",ROUND(AVERAGE(E27*I26/F26,F27*I26/G26,G27*I26/H26),2))

How do I adapt the formula to ignore a zero outcome of any of the nested formulae?

e.g.

If (E27*I26/F26 , F27*I26/G26 , G27*I26/H26) worked out as (0, 5, 10), I want the average returned to be 7.5 rather than 5

Thanks in advance

 

[DRSteele]

Let's try this and see if it works for you.

=IF(I24="","",ROUND(AVERAGE(IF(E27:G27*I26/E26:G26=0,"",E27:G27*I26/E26:G26)),2))

 

[gdcuk]

Thanks for the reply.

I managed to work around it by turning the outcome of the formula in E27,F27 & G27 to blank if the answer to their respective formulae was zero, then adapted the formula to:

=IF(I24="","",ROUND(AVERAGE(IF(E27="",E27,E27*I26/F26),IF(F27="",F27,F27*I26/G26),IF(G27="",G27,G27*I26/H26)),2))

 

[DRSteele]

Good show. Glad it worked for you.