Excel 2007 #DIV/0! message

[robmazco]

We have a spreadsheet we use bi weekly for calls and such coming in and how many are abondoned.

So we entered info in to new sheet and saved. When we save, excel bombs and tells me theres invalid data.

It seems to be a divide by zero problem, yet all the prior worksheets work.

Column b is calls handled, column c is calls abandoned, column d (percentage of calls abandoned) is the formula =C3/SUM(B3:C3). B and C are both zero so obviously we get zero for % of calls abandoned in D, but this seems to be where it bombs. How can I rewrite the function so it returns a blank space and get no divide by zero error?

Every worksheet we open prior to this works as expected.

Any help is appreciated.

 

[SkipVought]

Hi,
[tt]
=if(SUM(B3:C3)=0,"",C3/SUM(B3:C3))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robmazco]

Excellent. Now to go in and see if this fixes the problem.

 

[robmazco]

Well heres what I was using to find the troublesome cells:

Sub FindBadCells()

Dim Sheet As Worksheet
Dim Cell As Range

For Each Sheet In ThisWorkbook.Worksheets
For Each Cell In Sheet.UsedRange
If IsError(Cell) Then MsgBox Sheet.Name & ", " & Cell.Address(0, 0)
Next Cell
Next Sheet

End Sub

It located all the cells that have the div/0 troubles. I added the =if statement into all the cells, reran the VB and it returned no errors. But on saving, it still bombs - Excel has stopped working message..

heavy sigh

 

[sidetech]

try this
=IF(AND(B3=0,C3=0),0,C3/SUM(B3:C3))

 

[SkipVought]

Why would you bother with VBA??? That is absolutely foolish!

ALL your formulas should have this type of IF() statement in that column. Enter properly in one cell and then COPY 'n paste to all others.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robmazco]

Was using it to locate the troublesome cells.

Its still bombing after using the different IF statements.

Im at the point where they just need to re enter their data, I think its just plain hosed.

Thanks for all the info.

 

[xlhelp]

If you get "Excel has stopped working"; you need to look at a few things. I don't think any of the foregoing has anything to do with the error.

Do you have Bluetooth installed? That could be a problem.

Are there any Excel add-ins. You might need to remove them.

If you have an xlb file located somewhere on your system, you need to delete it.



Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[SkipVought]

The #DIV/0! indicates that you have a problem.

You do NOT fix the problem by only locating those cells.

You fix the problem by coding the formula used in ALL THE CELLS correctly.

ONE fix in ONE cell! Then COPY 'n; PASTE to all others! Should take no more than 30 seconds!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[robmazco]

Yup, I did that.
It still fails.
They have been convinced that they need to start over and they shouldnt screw it up this time

 

[SkipVought]

There is some other issue that is causing Excel to crash.

Try a Detect & Repair.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[1726]

There is an unusual combination of circumstances where the formula provided by SkipVought would fail to detect a Division by Zero error.

The formula fails when one or more of the apparently empty cells in the range B3:C3 contain the logical value FALSE - which has been hidden using a Custom Format Code.

Make sure these 'blank' cells are genuinely empty by deleting their contents.

 

[SkipVought]

1726,

How do you get that? SUM of FALSE or TRUE cells is ZERO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]