Working out averages

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Afternoon folks

Hopefully a nice simple question.
I havea list of values that I need to work averages out for. Unfortunately, sometimes the values will be a zero and these should be ignored.
I can work out the averages easily enough using the average function, but can't quite figure out a way of ignoring the zeros. Sample data would be :-
week 1 - 3.42
week 2 - 0 (zero)

Using the standard average function, I get an average of 1.71 when I actually want it to have an average of 3.42.

Anyone any ideas? I would prefer a function for this but would settle for a macro even though it would increase the program run-time.

Cheers

Richard
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
Try...

=AVERAGE(IF(A2:A100>0,A2:A100))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly.

Hope this helps!
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Assuming data in A1:A10:

Code:
=SUM(A1:A10)/COUNTIF(A1:A10,">0")
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Two ways.....

=SUM(A1:A10)/MAX(1,COUNTIF(A1:A10,">0"))

or

=AVERAGE(IF(A1:A10,A1:A10))

confirmed with CTRL+SHIFT+ENTER
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446

ADVERTISEMENT

The array formula( ctrl shift enter)

=AVERAGE(IF(A1:A8>0,A1:A8))


should do it.
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
Could you not use an if statement to return a null string, if the value is zero ?

Something like

=IF(G8=0,"",G8)

as the null string would be ignored by the Average function.
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177

ADVERTISEMENT

Thanks guys - wasn't quite expecting so many replies or so fast!!
:pray: (y) :pray:
 

pooj_malh

New Member
Joined
Sep 25, 2006
Messages
26
Hi ,

Please find attached the formula for your problem

"SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0))"

Below is the data on which I checked it



  • Column A
    1
    0
    3
    0
    0
    6
    7
    0
    9
    4
A11=SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0))
And the reult was 5

Check it of it works.

Let me know if you need more help

Regards
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
I've resurrected this to save starting another thread.
is it possible to alter this formula :-

=AVERAGE(IF(A2:A100>0,A2:A100)) confirmed with Ctrl-Shft-Enter

to check cells on different worksheets and still ignore any zeros to give me an average of what has actually been entered?
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Someone will correct me if I am wrong, but I dod not believe that CSE formulas will work across sheets, just as they will not work for a full column.

Can you do the avearage on each sheet, then average the averages?
 

Forum statistics

Threads
1,136,613
Messages
5,676,820
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top