Average of cell values - Ignore if 0

[Les]

Hi all,

I have a spreadsheet with 7 calculations in, from columns F to L. Tha
calculations are "=G36-G40". The results can be variable that have values
greater than 0 in.
What i want to do is to get the average value of the cells with a value
greater than 0.

1049.96 1049.96 1083.46 1049.96 1049.96 0.00 0.00

The above should give me an average of 1, 056.66

Any help would be much appreciated.

 

[Harald Staff]

Hi Les

One way among several:

=SUM(F1:L1)/COUNTIF(F1:L1,">"&0)

Note that you've excluded negative numbers. Change to "<>"&0 if you change
your mind.

HTH. Best wishes Harald

 

[Les]

Thanks Harald, Much appreciated
--
Les

Hi Les

One way among several:

=SUM(F1:L1)/COUNTIF(F1:L1,">"&0)

Note that you've excluded negative numbers. Change to "<>"&0 if you change
your mind.

HTH. Best wishes Harald

 

[Harald Staff]

Apologies. Negative values will affect the sum but not the count. Change to

=SUMIF(F1:L1,">"&0)/COUNTIF(F1:L1,">"&0)

Best wishes Harald