Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
Can someone please help with construction of a formula to find an average for
cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 ***Blank Row*** 3 8 0 0 4 5 2 12 ***Blank Row*** 5 0 8 10 6 3 0 11 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
=SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered
or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 ***Blank Row*** 3 8 0 0 4 5 2 12 ***Blank Row*** 5 0 8 10 6 3 0 11 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
I'm not sure I understand your question correctly. First, what are those
numbers on the left of your data? They can't be row numbers because they are sequential and you clearly show blank rows between some of them. When your later example refers to "rows" 1, 3 and 5... I presume you are referring to those sequential numbers and not Excel's actual row numbers, right? Second... the numbers you are averaging are not consecutive... are they always every other "row" or could there be variations? If there could be variations, how do we know which "row" numbers to use? If they are always every other "row", are they always the odd numbered "rows" and do they always go to the end of your data? -- Rick (MVP - Excel) "Excel-User-RR" wrote in message ... Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 ***Blank Row*** 3 8 0 0 4 5 2 12 ***Blank Row*** 5 0 8 10 6 3 0 11 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
Hi, Thanks for the reply - I forgot to mention that the reason why I was
looking for a formula for non-consecutive cells is because some rows have values that I do not want to include in the average (I also forgot to number the blank rows; I corrected this in the example below). If It helps, I only want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero values. Is there a way to do that with a formula? Thanks again. "Teethless mama" wrote: =SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 3***Blank Row*** 4 8 0 0 5 5 2 12 6***Blank Row*** 7 0 8 10 8 3 0 11 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
With a formula? No. Can you use VB code? If so, here is a macro that will
average only the non-zero value in the selected cells (although, since you appear to want to treat zero cells as if they were blank, then I wonder why you would be selecting them in the first place)... Sub AveragePositiveValues() Dim R As Range Dim Count As Long Dim Total As Double For Each R In Selection If R.Value 0 Then Count = Count + 1 Total = Total + R.Value End If Next MsgBox "Average of selected cells: " & Total / Count End Sub Instead of showing the average in a MessageBox (as my example does), you can assign it to a specific cell (just let us know which cell and we will modify the code to do that). -- Rick (MVP - Excel) "Excel-User-RR" wrote in message ... Hi, Thanks for the reply - I forgot to mention that the reason why I was looking for a formula for non-consecutive cells is because some rows have values that I do not want to include in the average (I also forgot to number the blank rows; I corrected this in the example below). If It helps, I only want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero values. Is there a way to do that with a formula? Thanks again. "Teethless mama" wrote: =SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 3***Blank Row*** 4 8 0 0 5 5 2 12 6***Blank Row*** 7 0 8 10 8 3 0 11 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
To average A1, A4, A7, A10, etc and exclude 0 values...
Array entered** : =AVERAGE(IF((MOD(ROW(A1:A100)-ROW(A1),3)=0)*(A1:A1000),A1:A100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Hi, Thanks for the reply - I forgot to mention that the reason why I was looking for a formula for non-consecutive cells is because some rows have values that I do not want to include in the average (I also forgot to number the blank rows; I corrected this in the example below). If It helps, I only want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero values. Is there a way to do that with a formula? Thanks again. "Teethless mama" wrote: =SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 3***Blank Row*** 4 8 0 0 5 5 2 12 6***Blank Row*** 7 0 8 10 8 3 0 11 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
In light of Biff's posting, let me clarify my "No" answer to you... you
can't **select** various cells (as I read your post as indicating you wanted to do) and then average those with a formula. Biff interpreted your words "selected cells" as being *specified cells* as opposed to my interpretation of selecting cells to form a Selection... as Biff showed, if the cells you wanted to average were at a fixed offset from each other, you could handle that situation with a formula (as he showed). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... With a formula? No. Can you use VB code? If so, here is a macro that will average only the non-zero value in the selected cells (although, since you appear to want to treat zero cells as if they were blank, then I wonder why you would be selecting them in the first place)... Sub AveragePositiveValues() Dim R As Range Dim Count As Long Dim Total As Double For Each R In Selection If R.Value 0 Then Count = Count + 1 Total = Total + R.Value End If Next MsgBox "Average of selected cells: " & Total / Count End Sub Instead of showing the average in a MessageBox (as my example does), you can assign it to a specific cell (just let us know which cell and we will modify the code to do that). -- Rick (MVP - Excel) "Excel-User-RR" wrote in message ... Hi, Thanks for the reply - I forgot to mention that the reason why I was looking for a formula for non-consecutive cells is because some rows have values that I do not want to include in the average (I also forgot to number the blank rows; I corrected this in the example below). If It helps, I only want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero values. Is there a way to do that with a formula? Thanks again. "Teethless mama" wrote: =SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 3***Blank Row*** 4 8 0 0 5 5 2 12 6***Blank Row*** 7 0 8 10 8 3 0 11 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
Thank You! And I apologize for my convoluted explanation of the problem.
Your formula is exactly what I was looking for but was unable to figure out on my own. "T. Valko" wrote: To average A1, A4, A7, A10, etc and exclude 0 values... Array entered** : =AVERAGE(IF((MOD(ROW(A1:A100)-ROW(A1),3)=0)*(A1:A1000),A1:A100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Hi, Thanks for the reply - I forgot to mention that the reason why I was looking for a formula for non-consecutive cells is because some rows have values that I do not want to include in the average (I also forgot to number the blank rows; I corrected this in the example below). If It helps, I only want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero values. Is there a way to do that with a formula? Thanks again. "Teethless mama" wrote: =SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 3***Blank Row*** 4 8 0 0 5 5 2 12 6***Blank Row*** 7 0 8 10 8 3 0 11 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average non-consecutive cells excluding zero
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Thank You! And I apologize for my convoluted explanation of the problem. Your formula is exactly what I was looking for but was unable to figure out on my own. "T. Valko" wrote: To average A1, A4, A7, A10, etc and exclude 0 values... Array entered** : =AVERAGE(IF((MOD(ROW(A1:A100)-ROW(A1),3)=0)*(A1:A1000),A1:A100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Excel-User-RR" wrote in message ... Hi, Thanks for the reply - I forgot to mention that the reason why I was looking for a formula for non-consecutive cells is because some rows have values that I do not want to include in the average (I also forgot to number the blank rows; I corrected this in the example below). If It helps, I only want to average selected cells (e.g.-A1, A4, A7,...) excluding any zero values. Is there a way to do that with a formula? Thanks again. "Teethless mama" wrote: =SUM(A1:A8)/COUNTIF(A1:A8,"0") normally entered or =AVERAGE(IF(A1:A80,A1:A8)) ctrl+shift+enter, not just enter "Excel-User-RR" wrote: Can someone please help with construction of a formula to find an average for cells that are in the same column but are not in the same range and may include zero values & blank rows? I am looking for something that will average only the values that are greater than zero. The cells with zero values will vary and will be separated by blank rows. For instance I would like to average column A cells in rows 1, 3, & 5 for a result of 7. Thanks. For example see below: A B C 1 6 9 2 2 0 1 4 3***Blank Row*** 4 8 0 0 5 5 2 12 6***Blank Row*** 7 0 8 10 8 3 0 11 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Non-consecutive Cells | Excel Discussion (Misc queries) | |||
Calculating the Average for non consecutive cells using custom for | Excel Discussion (Misc queries) | |||
average of several cells excluding the minimum | Excel Discussion (Misc queries) | |||
Average, Excluding Zeros, Non-Consecutive Range | Excel Discussion (Misc queries) | |||
Average non continguous cells, excluding zero's | Excel Worksheet Functions |