Is it possible to simplify

[gdrowell]

Hello All,

Is there a way to simple this nested IF statement?

=IF(D180="","",IF(AND(B180>=25),"6",IF(AND(B180<25,B180>=24.1),"5+1",IF(AND(B180<24.1,B180>=20),"5",IF(AND(B180<20,B180>=19.1),"4+1",IF(AND(B180<19.1,B180>=15),"4",IF(AND(B180<15,B180>=14.1),"3+1",IF(AND(B180<14.1,B180>=10),"3",IF(AND(B180<10,B180>=9.1),"2+1",IF(AND(B180<9.1,B180>=2),"2",IF(AND(B180<2,B180>=1.1),"1+1",IF(AND(B180<1.1,B180>=0),"1","0"))))))))))))

 

[Aladin Akyurek]

Try:

=IF(D180="","",LOOKUP(B180,{0,1.1,2,9.1,10,14.1,15,19.1,20,24.1,25},{0,"1+1",2,"2+1",3,"3.1",4,"4+1",5,"5+1",6}&""))

 

[Dave Patton]

With a Lookup table or self-contained lookup.

Excel 2010
	D	E
1	6	6
3c

Cell Formulas
Range	Formula
D1	=LOOKUP(B180,A1:B12)
E1	=LOOKUP(B180,{0,0;0.00001,1;1.1,"1+1";2.01,"2";9.1,"2+1";10,"3";14.11,"3+1";15,"4";19.1,"4+1";20,"5";24.1,"5+1";25,"6"})

Add your part about D180 if required.