Average (if)
 

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

Average (if)
 

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


"Ed" wrote in message
...
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

Average (if)
 

Hi,

Maybe you can use something like:

=AVERAGE(IF(A1:A50,A1:A5)

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

Regards,
Bondi

Average (if)
 

=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)

"Ed" wrote in message
...
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

Average (if)
 

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:A1000,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


"Ed" wrote:

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

Average (if)
 

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Ed" wrote in message
...
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