#DIV/0! message...

[misterg]

Brain numb...need help.

I have used a formula to get information from two cells (one is inputs, the other is hours worked) and display the average amount in a third cell.
However, until data is entered into both the input and hours worked cells, the third cell keeps showing a #DIV/0 error message.

Can i get rid of this by customising the format of the third cell?

Many thanks

G

[Tim Francis-Wright]

On 2002-05-20 10:10, misterg wrote:
Brain numb...need help.

I have used a formula to get information from two cells (one is inputs, the other is hours worked) and display the average amount in a third cell.
However, until data is entered into both the input and hours worked cells, the third cell keeps showing a #DIV/0 error message.

Can i get rid of this by customising the format of the third cell?

Many thanks

G

Why not change the formula instead and get rid of the errors altogether?
Instead of
=A1/B1
sue
=IF(B1=0,0,A1/B1)

HTH

[Asala42]

Hey,

You might want to tweak your formula a bit to bypass the errors. For example instead of:

=B2/C2

Use:

=IF(C2=0,"",B2/C2)

or alternatively:

=IF(ISERROR(B2/C2),"",B2/C2)

Adam

[dk]

Try something like this:-

=IF(A1=0,0,B1/A1)

If the value in the denominator (A1) is 0 then return 0, otherwise return B1/A1.

HTH,
Dan

[Ludovico]

Here's a macro I use all the time to easily convert formulas to prevent DIV/0 and other error messages. Just highlight the cell or cells you want to covert and ru the macro:

Sub IFISERROR()
'
Dim FormulaChanged As String

For Each cell In Selection
FormulaChanged = Mid(cell.Formula, 2)
FormulaChanged = "=IF(ISERROR(" & FormulaChanged & "),""""," & FormulaChanged & _
")"
cell.Formula = FormulaChanged
Next cell
End Sub

[misterg]

Guys,

Many many thanks for your help...the formula correction worked!

Appreciate your help guys.

G