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

stlchiefs

New Member
Joined
Dec 4, 2014
Messages
13
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

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe something like

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

stlchiefs

New Member
Joined
Dec 4, 2014
Messages
13
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

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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

Board Regular
Joined
Jun 17, 2014
Messages
80
Try this:
Code:
=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.

L
74$10
75$20
76$0
77#DIV/0!

<tbody>
</tbody>

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.

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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Dec 4, 2014
Messages
13
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 
Last edited:

stlchiefs

New Member
Joined
Dec 4, 2014
Messages
13
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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))
 

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top