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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try...

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

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

Hope this helps!
 
Upvote 0
Two ways.....

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

or

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

confirmed with CTRL+SHIFT+ENTER
 
Upvote 0
The array formula( ctrl shift enter)

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


should do it.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,211,454
Messages
6,101,947
Members
447,765
Latest member
bhutta5437

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
Back
Top