| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
average of non contigous data to also exclude zeros
I'm having trouble with working out a formula to give the average of non zero
data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
Try
=AVERAGEIF(B:B:E:E:H:H,"0") OR =SUM(B:B:E:E:H:H)/COUNTIF(B:B:E:E:H:H,"0") If this post helps click Yes --------------- Jacob Skaria "markmcd" wrote: I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
Oops.. please ignore the previous post...
=SUM(B:B,E:E,H:H)/(COUNTIF(B:B,"0")+COUNTIF(E:E,"0")+COUNTIF(H:H," 0")) I am sure there must be an easy way... If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try =AVERAGEIF(B:B:E:E:H:H,"0") OR =SUM(B:B:E:E:H:H)/COUNTIF(B:B:E:E:H:H,"0") If this post helps click Yes --------------- Jacob Skaria "markmcd" wrote: I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
=AVERAGEIF(INDIRECT({"B:B","E:E","H:H"}),"0")
If this post helps click Yes --------------- Jacob Skaria "markmcd" wrote: I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
I have messed up again..
=SUM(B:B,E:E,H:H)/ SUMPRODUCT(COUNTIF(INDIRECT({"B:B","E:E","H:H"})," 0")) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: =AVERAGEIF(INDIRECT({"B:B","E:E","H:H"}),"0") If this post helps click Yes --------------- Jacob Skaria "markmcd" wrote: I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
In this case since the columns are 2,5,8 etc; you can use MOD function to
check only only these columns. But this will be an array formula.Paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" =AVERAGE(IF(MOD(COLUMN(B2:H100),3)=2,IF(B2:H1000, B2:H100))) If this post helps click Yes --------------- Jacob Skaria "markmcd" wrote: I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
In this case since the columns are 2,5,8 you can use the MOD function to look
at only these columns. The below is an array formula copy this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" With headers in Row 1 and the range upto 100 rows... =AVERAGE(IF(MOD(COLUMN(B2:H100),3)=2,IF(B2:H1000, B2:H100))) If this post helps click Yes --------------- Jacob Skaria "markmcd" wrote: I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
average of non contigous data to also exclude zeros
Hi Mark
Instead of using the AverageIf function, use AverageIfS =AVERAGEIFS(B1:H1,B1:H1,"0") -- Regards Roger Govier "markmcd" wrote in message ... I'm having trouble with working out a formula to give the average of non zero data where the data is not in contigous cells. I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E, H. If B, E, H are zero (any one of them) then I don't want to include as part of the average. I have used the averageif as an array to not count zero values and this gives an error. I have also used the sum/countif but countif doesn't seem to like non contigous cells. I used AND to get around the non contiguous situation but that didn't work either I'm surprised by the Excel functionality when it comes to average, the most common scenarios are those where formulas have to be more complex. Basically all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case the average would be 10 and not 6. Markedly different. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average (exclude 1 min & 1 max Value) | Excel Discussion (Misc queries) | |||
| Exclude zeros from series | Charts and Charting in Excel | |||
| Average non-adjacent cells & exclude zeros | Excel Discussion (Misc queries) | |||
| Calc RSQ and exclude zeros | Excel Worksheet Functions | |||
| Limit or Exclude cells in Average and Sum formula | Excel Worksheet Functions | |||
Linear Mode
