Getting a average value without including zeros

[steve]

Hello,
This is kind of complicated so I will try to explain this the best I can.
Lets say I have a bunch of numbers with zero's in columns c2,d2,e2 and this
has 407 rows. (Maybe this is the correct way of saying it
=sheet2!c$2:$e$407)
From this I would like that it calculates the average for me but I don't
want the zeros to be included in the average just all the other numbers.
(See example below)
test test1 test2 test3 test4 Avg.
2/12/04 0
2/12/04 5
2/12/04 100
2/12/04 300
135
(this is what I am trying to get)
test test1 test2 test3 test4
2/13/04 10
2/13/04 15
2/13/04 10
2/13/04 300
2/13/04 83.75
(this is what I am trying to get
From the above example the average number will always be different.
Thanks for all your help in the past and I hope I explain this pretty well
if not let me know.

 

[Paul B]

Steve, try this array-formula, enter with Ctrl+shift+enter

=AVERAGE(IF(Sheet2!C2:E8<>0,Sheet2!C2:E8))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[steve]

Hello,
Thanks for your help but I am kind of new to this stuff especially using
arrays and when I placed the formula it gave me a #Value! instead of a
number.

 

[Frank Kabel]

Hi
you have to enter this formula with CTRL+SHIFT+ENTER after
inserting/entering it in your cell. See:
http://www.cpearson.com/excel/array.htm

 

[Max]

... when I placed the formula it gave me a #Value! instead of a

number

As Paul mentioned, it's an array-formula,
to be entered with Ctrl+Shift+Enter
instead of just pressing Enter

"Ctrl+Shift+Enter" means:
Hold down CTRL and SHIFT keys, and press ENTER key
(instead of just pressing ENTER key)

 

[CLR]

Maybe.........

=SUM(range)/COUNTIF(range,">0")

Vaya con Dios,
Chuck, CABGx3