Urgent - MS Excel Question

Factual Questions
1

Here’s my problem.

I have a spreadsheet with certain cells dividing other cells by yet further cells - e.g. :

a14 = a15 / a16.

The problem arises when a16 = 0. I get one of those “#DIV!0” jobbies in a14. Now, to me this is no problem - I understand where it’s coming from - however, my boss is not one of the Digerati, and so thinks “it looks like it’s broken”.

So I want to hide the “#DIV!0” without changing the formula. I have tried coditional formatting a14, but that doesn’t work. The problem is that the cell actually reads “a15 / a16” but what appears is “#DIV!0” - so I can’t set it to ignore “#DIV!0” because the PC knows that the cell actually reads “a15 / a16”…

What can I do? Someone please help as I have to email this sheet to someone in about 2 hours…

Thanks…

2

Finally, a question that caused me to delurk!

Two ways:

=if(a16=0,"",a15/a16)

or, more elegantly,

=if(iserror(a15/a16),"",a15/a16)

which will supress any errors.

3

Use the following formula in a14:

=If(A16=0,“Insert some text here to explain what has happened”, A15/A16)

Grim

4

I got ya, hold on a sec…

5

Dootsie, while I try your suggstion, what the hell is “delurk” ??

Just wondering… It sounds like something that happens when you’re eating a sandwich and you laugh really hard…

6

Dammitt.

Oh well, I second Dootsie.

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

7

Beaten to the punch - and more elegantly!!

:slight_smile: Grim

8

Nothing as painful! I just meant that rather than lurking around reading everyone’s posts, I finally registered and posted cause someone asked about something I know about.

:slight_smile:

9

Dootsie,

Works brilliantly. This place rocks…

Thank you so much…

:@)

10

Hey Tootsie…race ya to 1000…

11

welcome aboard dootsie!

12

I just make the cell with the “#DIV!0” error white text on a white background.

13

me too, NoGoodNamesLeft. But I tend to find the fastest and easiest way to do something. Now I know that there’s a CORRECT way too.
I think I’m sticking to fastest and easiest…

14

I dealt with the same exact problem yesterday, and dealt with it with a bunch of nested ifs!

I had no idea there was an iserror. I’m gonna try it out right now.

15

Yeah - but if you make the text white on a white background, it will still appear if you print the sheet…

16

NoGoodNamesLeft - surely you’re kidding?

What happens when the data changes, and the blanked cell is no longer an error?

Russell

(mostly convinced he’s being wooshed in some subtle way)

17

** russellm ** absolutely - that’s why I went looking for a good solution…

18

Well you can always use conditional formatting, but it really is a sledgehammer to crack a nut.

The IF(ISERROR(),) or the IF(A1=0,) methods are far better.

Also, leaving errors in cells slows down the recalculation of the workbook. When you write spreadsheets that can take upwards of a minute to recalculate (as I sometimes do), every second counts.

pan

19

Kabbes - the conditional formatting thing doesn’t usually work - cf. the OP…

Unless there is something that I am missing…

20

Tarantula, what conditional formatting mask did you use?