Working out averages

[RichardMGreen]

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

 

[Domenic]

Try...

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

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

Hope this helps!

 

[hatman]

Assuming data in A1:A10:

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

 

[barry houdini]

Two ways.....

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

or

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

confirmed with CTRL+SHIFT+ENTER

 

[GorD]

The array formula( ctrl shift enter)

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


should do it.

 

[SteveO59L]

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]

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

 

[pooj_malh]

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]

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]

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?