Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


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

Try this array formula

=IF(AND(A2:B5=0),"",AVERAGE(IF(A2:B5<0,A2:B5)))

Hope this helps / Lars-Åke

On Sat, 14 Mar 2009 10:33:01 -0700, Dana Lee <Dana
wrote:


Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


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

Try one of these. Still array entered...

=IF(ISERROR(AVERAGE(IF(A2:B5<0,A2:B5))),"",AVERAG E(IF(A2:B5<0,A2:B5)))

If the numbers will only be positive numbers:

=IF(COUNTIF(A2:B5,"0"),AVERAGE(IF(A2:B5<0,A2:B5) ),"")


--
Biff
Microsoft Excel MVP


"Dana Lee" <Dana wrote in message
...

Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the
cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


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

=IF(ISERROR(AVERAGE(IF(A2:B5<0,A2:B5))),"X",AVERA GE(IF(A2:B5<0,A2:B5)))
Again you must enter with CTRL+Shift+Enter
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Dana Lee" <Dana wrote in message
...

Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the
cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


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,

I know you are using 2003 but here is one of the reasons to someday consider
2007:

your first formula would be a non-array:

=AVERAGEIF(B2:B4,"<0")

the second formula would also be a non-array but also much simplier:

=IFERROR(AVERAGEIF(B2:B4,"<0"),"")

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

Cheers,
Shane Devenshire


"Dana Lee" wrote:


Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


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

"Dana Lee" wrote:


Below is my original question and an answer from "Mike" on here but I have
one more problem. When I use the formula if there is all zero's in the cells
being averaged I get the DIV error. What can I do to stop that from
happening

Thanks!


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

If your actual problem is for C2 to have the average of C3:C6, then what you
have is fine, use =AVERAGE(C3:C6) and cells C5 and C6 will be ignored. If you
want a true average, change the formulas to return a 0 instead of the "0.00%"
and you will get the true average (0.5).