Average (if)

[Guest]

Hi, how can I set a cell to get the AVERAGE of a row, but to exclude the
cells that contain 0, rather than use those 0's for the calculation?

thanks in advance

 

[Larry S]

A quick solution woud be to use the SUMIF and COUNTIF functions with the
condition of each set to not equal to zero..."<>0"

Then you would compute as follows SUMIF/COUNTIF

 

[Bondi]

Hi,

Maybe you can use something like:

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

It is an array function so you have to enter it with: Ctrl + Shift +
Enter

Regards,
Bondi

 

[Bob Phillips]

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

Try one of these:

For values in A1:A100

B1: =SUMIF(A1:A100,">0",A1:A100)/COUNTIF(A1:A100,">0")

OR

ARRAY FORMULA*
B1: =AVERAGE(IF(A1:A100>0,A1:A100))

*Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Those assume that if you have negative numbers you don't want them included
in the average.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Bernard Liengme]

Use
=AVERAGE(IF(A1:Z1<>0,A1:Z1,""))
but complete it with SHIFT+CTRL+ENTER not just ENTER; in the Formula Bar the
formula will be encased within { }
best wishes