# nested if statement

Posted by Stephanie Bicknell on January 03, 2002 12:03 PM

How would I write my if statement if this is what I'm looking for?

IF B-A >=30 THEN .5
IF B-A >=130 AND &LT;30 THEN 1
IF B-A >=200 AND &LT;130 THEN 1.5
IF B-A >=230 AND &LT;200 THEN 2
IF B-A >=300 AND &LT;230 THEN 2.5

Thanks ahead of time for the help...

Posted by Aladin Akyurek on January 03, 2002 12:13 PM

Stephanie --

Strange conversions. :)

Try what follows to see if it meets the specs:

=(B1-A1>=30)*.5+(B1-A1&LT;30)*(B1-A1>=130)+(B1-A1&LT;130)*(B1-A1>=200)*1.5+(B1-A1>=230)*(B1-A1&LT;200)*2+(B1-A1&LT;230)*(B1-A1>=300)*2.5

PS. It's a Boolean construction instead of filtering/deciding by IF.

=========

Posted by Adam S. on January 03, 2002 12:24 PM

Here's the classic approach

I'm not sure I understood your list(B-A less than 30 AND Greater than or equal to 130?). I assume you meant something like:

If: B-A is &LT;=30 then .5
if: B-A is >30 AND &LT;=130 Then 1
...thru
If B-A is >230 AND &LT;=300 Then 2.5
IF B-A is >300 then 3(?)

This'll do it:

=IF((B-A)>300,3,if((B-A)>230,2.5,if((B-A)>200,2,if((B-A)>130,1.5,if((B-A)>30,1,0.5)))))

Hope that helps out.

Posted by Stephanie Bicknell on January 03, 2002 1:54 PM

Sorry. This is what I meant. See that? I have been working on this too long and I don't even remember what I'm supposed to be doing. Now am I not only confused, I'm confusing everyone else out there!!!!

IF B-A &LT;=30 THEN .5
IF B-A &LT;=130 AND >30 THEN 1
IF B-A &LT;=200 AND >130 THEN 1.5
IF B-A &LT;=230 AND >200 THEN 2
IF B-A &LT;=300 AND >230 THEN 2.5

So, when I do the nested if statement Adam suggested, I just get 0.5? UGH... I will look some more but if anyone has another idea or can figure out my silly mistake, I would appreciate it! I'm just driving myself nuts... (and that doesn't take much...

Posted by Aladin Akyurek on January 03, 2002 2:25 PM

The Boolean Version...

=IF(COUNT(A1:B1)=2,(B1-A1 &LT;= 30)*0.5+(B1-A1 > 30)*(B1-A1 &LT;= 130)+(B1-A1 > 130)*(B1-A1 &LT;= 200)*1.5+(B1-A1 &LT;= 230)*(B1-A1 > 200)*2+(B1-A1 > 230)*(B1-A1 &LT;= 300)*2.5,"")

======

Posted by Scott on January 03, 2002 2:32 PM

Try this

ERR

Posted by IML on January 03, 2002 3:01 PM

skinning cats - vlookup

You could also go with
=IF(OR(COUNT(A1:B1)&LT;2,(B1-A1)&LT;=300),VLOOKUP(B1-A1,{0,0.5;30.01,1;130.01,1.5;200.01,2;230.01,2.5},2),"")

but this doesn't have as much accuracy as the other offerings. This is good the hundredth place.

good luck