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").

 

[Weazel]

maybe something like

=SUM(L83,L89,L95,L101,L107/INDEX(FREQUENCY((L83,L89,L95,L101,L107),0),2))

 

[stlchiefs]

Hmm. That didn't work for me. The cell is showing #DIV/0!. I'm not sure if it matters, but some of the listed cells have #DIV/0! in them as well, not all cells have data in them. To help explan what I'm trying to do, all the listed cells are annual averages. I'm trying to take these annual averages for these 5 years and provide an overall total average. Thanks

 

[Weazel]

you should probably look at resolving the the divide by zero errors in the individual cells either with a variation of an if or an iferror

 

[Grummet]

Try this:

=SUM([COLOR=#574123]L77,L83,L89,L95,L101,L107[/COLOR])/(COUNT([COLOR=#574123]L77,L83,L89,L95,L101,L107[/COLOR])-FREQUENCY(([COLOR=#574123]L77,L83,L89,L95,L101,L107[/COLOR]),0))

Note: If any of those cells have an error then this formula will also return an error. You can fix this by adjusting each of those cells to include an IFERROR formula.

So if the formula in L77 was =(L74+L75)/L76 and L76 was a zero it would give you #DIV/0! error.

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]74[/TD]
[TD="align: center"]$10[/TD]
[/TR]
[TR]
[TD="align: center"]75[/TD]
[TD="align: center"]$20[/TD]
[/TR]
[TR]
[TD="align: center"]76[/TD]
[TD="align: center"]$0[/TD]
[/TR]
[TR]
[TD="align: center"]77[/TD]
[TD="align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]

If you changed it to =IFERROR((L74+L75)/L76,0) it would return a 0 instead of an error.


Another tip. Instead of having to select every cell each time you want to make a formula you could do that one time and name that selection as a range. Before you start entering a formula, select each cell (hold down the Ctrl key) that you want to include. Then click in the white box above the header for column A on the toolbar and type in a name (ie. Annual). Afterwards you can write your formula out like this which is much cleaner.

=SUM(Annual)/(COUNT(Annual)-FREQUENCY((Annual),0))

 

[Jonmo1]

I always have to ask this when I see these formulas using non contiguous cells.

What is in the 'Between' Cells (L78:L82, L84:L88, etc) ?

If the between cells are NOT numeric, then you can just use the whole range and a standard averageif will ignore them.
=AVERAGIF(L77:L107,"<>0")


If the between cells ARE numeric, what about other columns?
Is there anything in another column that can be used to identify which cells should be included in the average?
Say if the cell in column A = "X" (or whatever) then average the cell in column L
=AVERAGEIFS(L77:L107,A77:A107,"X",L77:L107,">0")

Is there anything in another column that is common in the rows you want to averge of column L?

 

[stlchiefs]

The formula in Cells L77, etc is as follows: =AVERAGEIF(L72:L76,">0"). So what it's doing is averaging the data in the 5 cells above it excluding any zeros. Because the formula for these cells also needs to exclude zero I'm not sure how I'd incorporate Grummet's idea of using the IFERROR formula instead because I don't always know the denominator which would allow me to get rid of the Error reporting he mentioned. By using the AVGIF formula and excluding zeros the formula determines this for me. Why I can't have L72:L107 for my average and make it easy is because 5 of these cells (L77, 107, etc) are already calculating annual averages which I don't want included in my overall 5 year average formula that I'm trying to get at. Jonmo1: Column B will be filled with names (no numbers) if there's a number to be included in the average, but again, I can't use the range because it would again be including the annual averages (cells L77, 107, etc) as well as the individual occurences. I'm sure this is confusing so let me know what I need to clarify. Thanks guys.

 

[Jonmo1]

If the denominator for each individual average is different, then the Average of the Averages will not be accurate anyway, making this all a moot point.

My honest and best recommendation...
Put the individual averages in another column outside of the overall range.
Then the simple =AVERAGIF(L77:L107,"<>0") will work just fine to achieve the overall 5 year average.

 

[stlchiefs]

Avg of the Averages would be accurate. If the 5 yearly averages are accurate (which I know they are as that formula is working for these) getting the avg of those 5 is easy: /5 (excluding zeros). I don't have any open columns in this chart that I'm trying to produce this data in to try to go the route of JonMo and adding a column so that woudn't be the ideal method. I'd think (and hope) there's a way to get the avg of 5 separate cells within a single formula in 1 cell. Appreciate the ideas and help so far guys.

 

[Aladin Akyurek]

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").

maybe something like

=SUM(L83,L89,L95,L101,L107/INDEX(FREQUENCY((L83,L89,L95,L101,L107),0),2))

A paren missing:

=SUM(L83,L89,L95,L101,L107)/INDEX(FREQUENCY((L83,L89,L95,L101,L107),0),2))