Formula Question for Excel 2003

[Dana Lee]

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

 

[Lars-Åke Aspelin]

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

 

[T. Valko]

Try one of these. Still array entered...

=IF(ISERROR(AVERAGE(IF(A2:B5<>0,A2:B5))),"",AVERAGE(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)),"")

 

[Bernard Liengme]

=IF(ISERROR(AVERAGE(IF(A2:B5<>0,A2:B5))),"X",AVERAGE(IF(A2:B5<>0,A2:B5)))
Again you must enter with CTRL+Shift+Enter
best wishes

 

[Shane Devenshire]

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"),"")

 

[jgenzano]

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

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