How can a logical test equal one cell?

[Mtyler]

I'm confused.

I'm reviewing someone else's work and they have used the following if formula

=+IF(D15,D15/E15-1,"")

Cell D15 does not contain anything other than a hard coded number.

Surely this if function is useless, even if D15 contains nothing?

Thanks

Matt

 

[Jonmo1]

As far as excel is concerned,
0 (or Blank) = FALSE
ANY OTHER NUMBER = TRUE

Hope this helps...

 

[Geoff Taylor]

The one effect I can see is that a zero result is not displayed as "0", but as a blank.

 

[Jonmo1]

Surely this if function is useless

Most definately NOT useless...

Say you want to Devide B1/A1
=B1/A1
But if A1 is Blank or 0, you get #DIV/0! Error

Some would use
=If(Iserror(B1/A1),"",B1/A1)
to account for that...

But it is much easier to do this instead

=If(A1,B1/A1,"")

 

[Mtyler]

Thanks for your replies...

So, If(D5,.... essentially removes any errors that may result from the logical test cell being 0 or blank.

I see.

Thank you!

 

[Geoff Taylor]

Hi Jonmo,
This formula isn't suppressing a division by zero error. Look again; it's testing the numerator, not the demoninator.

 

[Jonmo1]

You'd better check again Geoff....

the number to the RIGHT of the / is the number that causes #DIV/0 Errors.

 

[Geoff Taylor]

Yes, the number to the RIGHT is the one that causes the error, but in the formula the number to the LEFT is the one being tested, so the formula can't be a test for a DIV/0 error.

Or am I just being thick?

 

[Jonmo1]

VISUALLY in the spreadsheet, yes A1 is to the LEFT of B1...

But in my example Formula, it's the other way around..

B1/A1

 

[Geoff Taylor]

I'm definitely being thick, but bear with me...

In the formula posted at the top of the thread, the logic is "If D15's got something in it, divide it by E15 and then take away 1". In that formula, E15 would be the one causing the DIV/0 error, so why test D15?