Office & Productivity Software forum

General discussion

Ignore Zero when Averaging (BUT not a1:a10)

by sweetie3235 / June 23, 2009 9:17 AM PDT

I am working on a spreadsheet to take an average of prices. However, they are not consecutive (a1:10). It looks more like this
This information will be input on a weekly basis so there are currently $0.00 most of the cells. When I go to average it, it takes all the cells into consideration, including the zero cell and therefore causes the average to be much lower than it should be. I've tried =IF(ISERRROR) but it doesn't want more than (4) commas. I hope this makes sense. Your help is GREATLY appreciated.

Thank you!

Discussion is locked
You are posting a reply to: Ignore Zero when Averaging (BUT not a1:a10)
The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our CNET Forums policies for details. All submitted content is subject to our Terms of Use.
Track this discussion and email me when there are updates

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

You are reporting the following post: Ignore Zero when Averaging (BUT not a1:a10)
This post has been flagged and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.
Sorry, there was a problem flagging this post. Please try again now or at a later time.
If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.
Collapse -
Right from Excel Help .....
by VAPCMD / June 23, 2009 10:58 AM PDT

I searched on "average and omit zero"


Worked for me....should do same for you too.



Worksheet example

The example may be easier to understand if you copy it to a blank worksheet.


Create a blank workbook or worksheet.

Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.


Formula Description (Result)
=AVERAGE(IF(A2:A7<>0, A2:A7,"")) Averages the numbers in the list except those that contain zero, such as cell A6 (11.4)

*************Note: The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the cell A9. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Collapse -
Re: array formula
by Kees Bakker / June 24, 2009 5:32 AM PDT

I've never learned the details of an array formula, so I can't comment on VAPCMD's solution.

Let me comment that I don't understand the problem quite well. It's not an average of 10 consecutive cells. It's the average of 7 non-consecutive cells. Or is it? That would be simple: just list all 7 cells as a parameter: =average(d6,d12,d18,d24,d30,d36,d42).

Or do you mean that some of that 7 cells contain 0 and those shouldn't be included. If only d6 and d24 happen to contain real prices, you would want the average of those two cells only. So the average of (4,0,0,3,0,0,0) would be 3.5, not 1.0.

As I said, I've never managed to understand array formulas. What I would do: in column E make formulas like =IF(D1=0;"";D1). Copy that down. This makes E6 equal to 4, E12 equal to an zero-length string. A zero-length string (any string in fact) is left out of account. That would make the formula =average(e6,e12,e18,e24,e30,e36,e42) return 3.5.


Collapse -
Previous response did not paste and align all data
by VAPCMD / June 24, 2009 12:23 PM PDT
In reply to: Re: array formula

I searched Excel help using "Average and omit zero"

Under "Calculate the average of numbers" I selected "Calculate the average of numbers, ignoring zero(0)values"

The formula is . . . =AVERAGE(IF(A2:A7<>0,A2:A7,""))

Row COL "A"
2 ---- 10
3 ---- 7
4 ---- 9
5 ---- 27
6 ---- 0
7 ---- 4
8 ---- 11.4 Average excluding zero

9.5 Average including zero (using formula above)

Note The formula in the example must be entered as an array formula. Enter the formula as shown above but before hitting 'Enter', press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the error #VALUE! is returned.

Hope this helps.


Collapse -
(NT) That surely helps. Thanks.
by Kees Bakker / June 24, 2009 6:25 PM PDT
Collapse -
Use Countif
by yilmazrona / June 25, 2009 2:12 AM PDT

A formula like ==SUM(inputrange)/COUNTIF(inputrange,">0") should do the trick, the denominator of the mean is only the cells > than 0. This assumes that there are no cells <0 that you want included.

Collapse -
May I?
by MarkFlax Forum moderator / June 26, 2009 6:57 AM PDT

I have a question about those averages.

Are you sure that the zero figure cells should not be included in the averaging? Only, 0 is a valid number, and is also an indication of the status of the series.

What I mean is this. Say you have 10 weeks worth of sales, A1 to A10 shows the weekly sales;

A1 = $27
A2 = $451
A3 = $0
A4 = $98
A5 = $218
A6 = $0
A7 = $63
A8 = $71
A9 = $5
A10= $83

Over those 10 weeks, the 'average' sales per week is $1016 / 10, or $101.60
eg: The statement would read;
"Over the period 6/1/09 to 8/9/09 the average weekly sales were $101.60".

If you remove the zero weeks, then the average over the '8' weeks becomes $127.00
eg: The statement would read;
"Over the period 6/1/09 to 8/9/09 the average weekly sales were $127.60".

Unless you clearly state that the averaging does not include weeks where no sales were made, does that not give a false impression? That isn't what averaging is normally about.

Just a thought, and of course the final decision is yours.


{Note: Although in Europe myself, I used the US date convention. For me they would be 1/6/09 to 9/8/09, Happy }.

Collapse -
Great idea.
by Kees Bakker / June 26, 2009 8:01 AM PDT
In reply to: May I?

My average lottery winning = 10,000 (euro) a day.
Meaning: of the 21.770 days I lived, 1 lucky day I won 10,000 and the other 21.669 I won nothing.

Well, even the first sentence is sadly untrue. But, today I won a 24-CD case!


Collapse -
You won a CD Case?
by MarkFlax Forum moderator / June 26, 2009 9:53 PM PDT
In reply to: Great idea.

That's more than me. I've never won anything!

Nice one Kees.


Collapse -
Definitely agree ...the narrative has to explain the
by VAPCMD / June 26, 2009 11:47 AM PDT
In reply to: May I?

results, whatever they may be. There may be some occasions where it may be appropriate to exclude zero results but they seem more the exception than the rule.

Nice to know how to do it though...just in case.


Popular Forums
Computer Newbies 10,686 discussions
Computer Help 54,365 discussions
Laptops 21,181 discussions
Networking & Wireless 16,313 discussions
Phones 17,137 discussions
Security 31,287 discussions
TVs & Home Theaters 22,101 discussions
Windows 7 8,164 discussions
Windows 10 2,657 discussions


Help, my PC with Windows 10 won't shut down properly

Since upgrading to Windows 10 my computer won't shut down properly. I use the menu button shutdown and the screen goes blank, but the system does not fully shut down. The only way to get it to shut down is to hold the physical power button down till it shuts down. Any suggestions?