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 <30 THEN 1
IF B-A >=200 AND <130 THEN 1.5
IF B-A >=230 AND <200 THEN 2
IF B-A >=300 AND <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<30)*(B1-A1>=130)+(B1-A1<130)*(B1-A1>=200)*1.5+(B1-A1>=230)*(B1-A1<200)*2+(B1-A1<230)*(B1-A1>=300)*2.5
PS. It's a Boolean construction instead of filtering/deciding by IF.
Aladin
=========
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 <=30 then .5
if: B-A is >30 AND <=130 Then 1
...thru
If B-A is >230 AND <=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.
Adam S.
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 <=30 THEN .5
IF B-A <=130 AND >30 THEN 1
IF B-A <=200 AND >130 THEN 1.5
IF B-A <=230 AND >200 THEN 2
IF B-A <=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...
I added some control and adapted it to the new specs:
=IF(COUNT(A1:B1)=2,(B1-A1 <= 30)*0.5+(B1-A1 > 30)*(B1-A1 <= 130)+(B1-A1 > 130)*(B1-A1 <= 200)*1.5+(B1-A1 <= 230)*(B1-A1 > 200)*2+(B1-A1 > 230)*(B1-A1 <= 300)*2.5,"")
Aladin
======
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)<2,(B1-A1)<=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
