Formula Request

[dernspiker]

Excel 2003 - In the portion below I need Cell C2 to average the 4 rows below.
The last to rows equal zero. So when I average all the rows of course it
sees those cells as zero. I tried using N/A but it stil sees those as a
zero. Please help I have been working on this huge spreadsheet for a while
now and really need to get it right.

A B C

5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3)
5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4)
0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5)
0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6)

 

[Mike H]

Hi,

I'm not sure I fully understand but perhaps this

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

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Pecoflyer]

To exclude cells containing zero form an average try

=average(if(your_range<>0,your_range)) and enter as an array formul
with Ctrl+Shift+Enter

More info at 'Working With Lists
(http://www.cpearson.com/excel/lists.htm#Functions)


HTH

Excel 2003 - In the portion below I need Cell C2 to average the 4 row
below.
The last to rows equal zero. So when I average all the rows of cours
it
sees those cells as zero. I tried using N/A but it stil sees those a
a
zero. Please help I have been working on this huge spreadsheet for
while
now and really need to get it right.

A B C

5 5 =IF((B3=0)*AND(A3=0),"N/A",B3/A3)
5 5 =IF((B4=0)*AND(A4=0),"N/A",B4/A4)
0 0 =IF((B5=0)*AND(A5=0),"0.00%",B5/A5)
0 0 =IF((B6=0)*AND(A6=0),"0.00%",B6/A6

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[dernspiker]

Mike thank you so much. I had to change the cell names but then it worked
beautifully. This has been a huge project and I am at the end of it.

Thanks!

 

[Shane Devenshire]

Hi,

If you are using 2007 this is your formula

=AVERAGEIF(C1:C4,"<>0")

 

[dernspiker]

Mike this works perfectly for what I asked for but if all the cells are 0
then the cell returns a #DIV/0! error. I need for it to be 0. If you can
help me with this I think I will have it.

Thanks!