ways around nested IF functions...

[msouthall]

Hi guys,

This is my first post and i'm not particularly advanced at Excel so apologies if this is a stupid question.

I'm using nested IF functions to reduce a large number of bands to a smaller set using the following formula:

=IF(LEFT(D4,1)="<","< 150",IF(LEFT(D4,3)="150","150-300",IF(LEFT(D4,3)="175","150-300",IF(LEFT(D4,3)="200","150-300",IF(LEFT(D4,3)="225","150-300",IF(LEFT(D4,3)="250","150-300",IF(LEFT(D4,3)="275","150-300","NO")))))))

However im stuck at 275 and need to go to 2000!

All help much appreciated

Matt

 

[RoryA]

=LOOKUP(IF(LEFT(D4,1)="<",0,IF(LEFT(D4,1)=">",2000,--(TRIM(LEFT(D4,FIND("<",D4)-1))))),{0,"< 150";150,"150-300";300,"300-450";450,"450-600";600,"600-750";750,"750-900";900,"900+"})
though I suspect Barry's next version will be neater...

 

[barry houdini]

I'm not sure if Rory's LOOKUP might be a better approach but sticking with my original method perhaps

=IF(LEFT(D4)="<","<150",IF(LEFT(D4)=">",">900",IF(LEFT(D4,4)+0>=900,">900",FLOOR(LEFT(D4,3),150)&"-"&FLOOR(LEFT(D4,3),150)+150)))

 

[msouthall]

how do i send a virtual beer to you two chaps!!?!

thanks so much!

MS

 

[RoryA]

There's no need. Just Fedex the real thing...