| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want a formula to ignore text values in cell references | New Users to Excel | |||
| MIN & AVERAGE ignore 0 | New Users to Excel | |||
| Ignore text but count values in same cell | Excel Discussion (Misc queries) | |||
| How do I ignore cell values of zero? | Excel Worksheet Functions | |||
| ignore MAX and MIN values in a set to calculate average | Excel Discussion (Misc queries) | |||
Hybrid Mode
