dividing by 0 error

[Donna]

I need to have a worksheet that other people can use with out changing and my
problem is that I need to have a certain amount of blank cells that when a
number is entered will fill in cells that contain formulas.
Some of the formulas contain division and so I'm getting the #DIV/0! error
which prevents the columns from summing. How can I say to only sum if there
is a number in the cell so that it won't include the error. Or how can I keep
the error from showing but also have the blank cells for people to use? In
other words only "formulate" when there are numbers. I hope this makes
sense....

# of Rolls LengthM Widthmm Total M2 Width of Source Material # of cuts
across
10 500 20 100 1290 65.00
5 600 30 90 43.00
20 200 80 320 17.00
- #DIV/0!
- #DIV/0!
- #DIV/0!
Total #DIV/0!

 

[Mike H]

Donna,

You could test for Div/0 and return "" if it is or use this to sum with
those errors in the range

= SUMIF(F1:F6,">"&0)

Mike

 

[Donna]

Thank you so much, that worked great! One more thing. Is there a way to do
like a "conditional format" that turns the error #DIV/0! the same color as
the background so that you can't see it? I thought I had done this before but
now I can't get it to work.

 

[Jan Kronsell]

In the conditional format box

The formula is =ISERROR(A1)

Jan

 

[Shane Devenshire]

Hi,

This depends on the version of Excel you are using, lets suppose the first
cell of the selected range is A1:

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula only cells that contain
4. From the Format only cells with pick Errors
=ISERR(A1)
5. Click the Format button
6. Choose a White (or whatever color the cell background is) on the Font tab
7. Click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:

5. Click the Format button and choose the Number tab.
6. Pick the Custom category and
7. On the Type line enter ;;;
8. Click OK as many times as necessary

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Donna]

This doesn't work. I highlighted the area went to format/conditional
formatting and put formula and then =ISERROR(A1) and I formatted it to be the
same color as the background to hide the error but it just doesn't seem to
work.

I have tried =ISERROR before, could it be the type of error (#DIV/0!)?

 

[Peo Sjoblom]

Why would you use A1? You should obviously use the same cell where the error
is
so if the error range is M1:M100 you select that range with M1 as the active
cell and then use

=ISERROR($M1)




--


Regards,


Peo Sjoblom