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)

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
"dernspiker" wrote:

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 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!

"Mike H" wrote:

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
"dernspiker" wrote:

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 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!

"Mike H" wrote:

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
"dernspiker" wrote:

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)

Hi,

If you are using 2007 this is your formula

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

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"dernspiker" wrote:

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)

To exclude cells containing zero form an average try

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

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


HTH

dernspiker;268653 Wrote:
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)


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74963