IF statement - almost there!

[Guest]

I have an IF statement that looks up the value in a cell, and returns one of
two values in other cells. It is:

=IF((OR(H9>10%,H9<-20%)),$B$40,$B$39)

i.e. if H9 is either greater than 10% or less than or equal to -20%, return
the contents of cell B40. If not, return the contents of B39. So far so good.

Sometimes the contents of H9 is blank (formula in H9 is
=IF(G9=0,"",((F9-G9)/G9)).

i.e. if G9 is zero, return a blank (" ").

My problem is that the above should return the contents of cell B39 (cause
it's NOT greater than 10% or less than or equal to -20%), but it doesn't; it
returns the contents of B40. HELP!

Thanks for taking the time to read this.

 

[Sandy Mann]

I get the contents of B40 if there is a space in H9 and the contents of B40
if H9 is truely blank

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

So how do I change the formula to get the contents of B39 if the contents of
H9 is
the result of =IF(G9=0,"",((F9-G9)/G9)). - i.e. "" ?

 

[Sandy Mann]

Hi James,

So how do I change the formula to get the contents of B39 if the contents
of
H9 is
the result of =IF(G9=0,"",((F9-G9)/G9)). - i.e. "" ?

I would be quite good at this if I could learn to read peoples posts
properly! Sorry I missed that part completely - I was too busy concentating
on the other formula.

To solve the problem I would approach it the other way round and add a test
for an empty string:

=IF(OR(H9="",AND(H9>-20%,H9<10%)),$B$39,$B$40)

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Guest]

Yes, it works. But I don't know why.

Can you walk me through the formula please?

=IF(OR(H9="",AND(H9>-20%,H9<10%)),$B$39,$B$40)

I don't understand how, if H9 does in fact equal "", why the formula picks
up B39 and not B40.

Thanks again.

 

[Sandy Mann]

Hi James,

I know that you know how to write an *IF* function:

IF(Test, Do if Test is TRUE, Do if Test is FALSE)

because you wrote the originals one:

=IF((OR(H9>10%,H9<-20%)),$B$40,$B$39)

This is saying if the value in H9 is *anything* but a value between -20% and
+10% the Test will be TRUE so it will return the value in B40

I turned this around:

=IF(AND(H9>-20%,H9<10%),$B$39,$B$40)

which says if the value in H9 *is* between -20% and +10% then the Test will
be TRUE so return the value in B39

So far so good but we also want to test for the case of H9 being an empty
string ("")

The test H9="" wil return TRUE if it contains an empty string so the
formula:

=IF(OR(H9="",AND(H9>-20%,H9<10%)),$B$39,$B$40)

is saying: If H9 contains an empty string, *OR* is within the range we want
to test for, then the test is TRUE so return the TRUE part which is B39
otherwise return B40.

--

Does that help?

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

<snip>