AVG Function

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a spreadsheet calculating avg hours worked per day.
column b5 is where the function is located. the avg
function runs from cells c5:ag5. My problem is
this....Make this go away please: #DIV/0!. It is sitting
in cell b5 waiting for me to enter a value in the function
area. But I want b5 to show no text until I enter a value.
Can anyone help?

Thanks in advance for your time,
Steve
 
Two options:

Option 1

Enter this formula in B5:

=IF(SUM(C5:AG5)=0,"",AVERAGE(C5:AG5))

Option 2

Hide the error using conditional formatting by choosing white as your font:

Format > Conditional Formatting > Formula Is

=ISERROR(B5)

Hope this helps!
 
try this ARRAY formula that must be entered using control+shift+enter
=average(if(c5:ag5>0,c5:ag5))
 
Don Guillett said:
try this ARRAY formula that must be entered using
control+shift+enter
=average(if(c5:ag5>0,c5:ag5))
Click to expand...
....

Testing is good.

The OP's problem is AVERAGE returning #DIV/0! when there are no numbers in
its argument(s). How would your formula help?
 
It looks like my formula won't do. I like Jason's formula though. That
will not only take care of situations such as your example, it will also
take care of the #DIV/0 error when no values are input.
 
Back
Top