MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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