Average, Excluding Zeros, BUT it's not a Range, but selection of Cells. What's the formula? I get an Error

[stlchiefs]

Please help. I've tried using the following formula options, but keep getting an error due to "too many arguments". All my searches turn up formulas for averaging a range of cells where's I'm using a selection of cells not in a range that I can set apart with a colon. Please help. Thanks in advance as I know the excel gurus here will have a solution! See below for what I've tried: Old School: =SUM(L77,L83,L89,L95,L101,L107)/COUNTIF(L77,L83,L89,L95,L101,L107,"<>0"); Average Formula: =AVERAGEIF(L77,L83,L89,L95,L101,L107,"<>0").

 

[Jonmo1]

Avg of the Averages would be accurate.

I have to disagree, because you said..

because I don't always know the denominator

If the denominator of each average is different, then the average of those averages is NOT accurate.
See below.

Excel Workbook
	A	B	C	D
1	1
2	2
3	3
4	4
5	5
6
7
8	Averge A1:A2	Average A3:A5	Average of Averages	Overall Average
9	1.5	4	2.75	3
Sheet1

 

[Weazel]

thanks Aladin, I missed that

 

[stlchiefs]

That didn't work still. I'm thinking the issue is what JonMo1 hit on earlier and that's wih some of the listed cells having the #DIV/0 error in them due to there not being any data for those years. If I take those years and just change them to zero I get a response of "True" in the cell with Aladin's formula. Obviously, "True" is not the response I want, but something is working. If I take JonMo's other idea and set up a set of 5 consecutive cells (outside of my table) that carry over the 5 annual averages and then use the original =AVERAGEIF(U72:U76,"<>0") formula the formula works IF I manually change any #DIV/0 errors to 0. So I guess the question is then how to get something besides for #DIV/0 error to appear in each of the 5 annual avg cells if there's no data for that year. The current formula I use is =AVERAGEIF(L72:L76,">0").

 

[stlchiefs]

I don't know the denominator in advance therefore I can't put it in a set formula as a numeric value (i.e. "5" for the number of years). On the other hand, because I can create a formula that will ignore zeros it's possible for the formula itself to figure out the denominator.

I have to disagree, because you said..


If the denominator of each average is different, then the average of those averages is NOT accurate.
See below.

Sheet1

	A	B	C	D
Averge A1:A2	Average A3:A5	Average of Averages	Overall Average

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 145px;"><col style="width: 145px;"><col style="width: 145px;"><col style="width: 145px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2.75[/TD]
[TD="align: right"]3[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A9 =AVERAGE(A1:A2) B9 =AVERAGE(A3:A5) C9 =AVERAGE(A9:B9) D9 =AVERAGE(A1:A5) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[stlchiefs]

Ah, I see what you're saying now JonMo regarding an overall avg over the 5 years vs the avg of the averages. So, back to trying to get an overall average over the 5 years using no consecutive cells and excluding any cells with zero? That would be getting the avg of cells L72:L76, L78:L82, L84:88, L90:94, L96:100, L102:L106. The B column for those same ranges contains text (no numbers) if that helps and would only be filled if there was not a zero in the L column.

 

[Aladin Akyurek]

That didn't work still. I'm thinking the issue is what JonMo1 hit on earlier and that's wih some of the listed cells having the #DIV/0 error in them due to there not being any data for those years. If I take those years and just change them to zero I get a response of "True" in the cell with Aladin's formula. Obviously, "True" is not the response I want, but something is working. If I take JonMo's other idea and set up a set of 5 consecutive cells (outside of my table) that carry over the 5 annual averages and then use the original =AVERAGEIF(U72:U76,"<>0") formula the formula works IF I manually change any #DIV/0 errors to 0. So I guess the question is then how to get something besides for #DIV/0 error to appear in each of the 5 annual avg cells if there's no data for that year. The current formula I use is =AVERAGEIF(L72:L76,">0").

Given every 6th value, that is, L83,L89,L95,L101, and L107 and #DIV/0! errors...

Control+shift+enter, not just enter:

=AVERAGE(IF(MOD(ROW(L83:L107)-ROW(L83),6)=0,IF(ISNUMBER(1/L83:L107),L83:L107)))

 

[Jonmo1]

That would be getting the avg of cells L72:L76, L78:L82, L84:88, L90:94, L96:100, L102:L106. The B column for those same ranges contains text (no numbers) if that helps and would only be filled if there was not a zero in the L column.

What about the individual averages, L77 L83 L89 etc that you were originally trying to average..
Do those cells have text in column B?
If NO, then you could do
=AVERAGEIFS(L72:L106,B72:B106,"?*",L72:L106,"<>0")