# Working out averages

#### RichardMGreen

##### Well-known Member
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!

Assuming data in A1:A10:

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

Two ways.....

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

or

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

confirmed with CTRL+SHIFT+ENTER

The array formula( ctrl shift enter)

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

should do it.

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.

Thanks guys - wasn't quite expecting so many replies or so fast!!
ray: ray:

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

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?

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?

Replies
6
Views
211
Replies
1
Views
548
Replies
2
Views
529
Replies
14
Views
2K
Replies
1
Views
380

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?

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