Nested If statement Help! halfway got it figured out

**T_BOLT** · 01-25-2013, 10:59 PM

Will be so simple for most but me I just cant get it figured out.
In cell e20 I want a if (d20-c20)*10000 is between -75 and +75 (d20-c20)*10000 but if (d20-c20)*10000 is greater than -75 or +75 leave cell blank

other words if (d20-c20)*10000 falls between 75 and negative 75 I want it to put the value of (d20-c20)*10000 if it is outside of 75 and negative 75 I want it to put a empty space in cell " "

I can have two seperate formulas that seem to work but I can not combine the two forumlas into one

=IF((D20-C20)*10000>+-75," ",(D20-C20)*10000)
=IF((D20-C20)*10000<+-75," ",(D20-C20)*10000)

Thanks in advance!

**Moo the Dog** · 01-25-2013, 11:17 PM

Will this work:

=IF(ABS((D20-C20)*10000)<=75,(D20-C20)*10000,"")

The formula above is a shorter way of writing:
=IF(AND((D20-C20)*10000>=-75,(D20-C20)*10000<=75),(D20-C20)*10000,"")

- Moo

**T_BOLT** · 01-25-2013, 11:33 PM

That did not work I get a False listed no matter what the value of (D20-C20)*10000 is.

**Moo the Dog** · 01-25-2013, 11:45 PM

That's odd, both formulas seem to work for me... see my attached sheet.
- Moo

**benishiryo** · 01-25-2013, 11:53 PM

@Moo:
i thought your initial formula was the right one since OP wanted

between 75 and negative 75 I want it to put the value of (d20-c20)*10000 if it is outside of 75 and negative 75 I want it to put a empty space in cell " "

so anything that equates to less than or equals to 75 should be using that equation. mine was as below but i removed since i was a minute later than you

=IF(ABS((D20-C20)*10000)>75,"",(D20-C20)*10000)

**dredwolf** · 01-26-2013, 12:27 AM

I just have one question ..If you want >+- 75 AND <+-75 at the same time...

I can have two seperate formulas that seem to work but I can not combine the two forumlas into one

=IF((D20-C20)*10000>+-75," ",(D20-C20)*10000)
=IF((D20-C20)*10000<+-75," ",(D20-C20)*10000)

then all results should be =(D20-C20)*10000...

maybe I am missing something ?

Edit-
Actually, relooking at the quote... they should all return ""...?????

**T_BOLT** · 01-26-2013, 02:41 AM

Originally Posted by Moo the Dog

Will this work:

=IF(ABS((D20-C20)*10000)<=75,(D20-C20)*10000,"")

The formula above is a shorter way of writing:
=IF(AND((D20-C20)*10000>=-75,(D20-C20)*10000<=75),(D20-C20)*10000,"")

- Moo

Yea that did work sorry. I must have done something in my copy and paste of the formula. I tried the long version but I was thinking since I cne do it in two seperate forumlas that I had to put in two IF statements in it (see below)and could not get to work.

=IF(D20-C20)*10000>=-75,(D20-C20)*10000,IF(D20-C20)*10000<=+75,(d20-C20)*1000,"")

THNAKS