Average of cell values - Ignore if 0
 

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.

--
Les

Average of cell values - Ignore if 0
 

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" skrev i melding
...
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.

--
Les

Average of cell values - Ignore if 0
 

Thanks Harald, Much appreciated
--
Les


"Harald Staff" wrote:

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" skrev i melding
...
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.

--
Les

Average of cell values - Ignore if 0
 

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

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

Best wishes Harald

"Les" skrev i melding
...
Thanks Harald, Much appreciated
--
Les


"Harald Staff" wrote:

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" skrev i melding
...
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.

--
Les