help with if then statement

[CCSSJM]

https://docs.google.com/spreadsheets/d/1REi1NFguL-hD4O6_ZDJuel20Z2aybUJPUWA_ZLdJ0Rc/edit?usp=sharing


i am really sorry to bother you. i dont understand why this if statement is not working

looking to reward efficiency goals with scores of 1, 2, 3 etc etc.

please help!

 

[jtakw]

Hi,

Change all your number requirements referencing M2 to decimals (i.e. 85 to 0.85, 90 to 0.9, etc.), since M2 is a % percentage, 90% is actually 0.9

 

[CCSSJM]

Hi,

Change all your number requirements referencing M2 to decimals (i.e. 85 to 0.85, 90 to 0.9, etc.), since M2 is a % percentage, 90% is actually 0.9

Thank you very much!! Have a great weekend.

 

[jtakw]

You're welcome, welcome to the forum.This is a simplified version of your IF formula, and a LOOKUP alternative:

Book1
	M	N	O	P
2	90.00%		N/A	Your original formula
3			2	My shorter version
4			2	Using LOOKUP
Sheet36

Cell Formulas
Range	Formula
O2	=IF(AND(M2>85,M2<90),1,IF(AND(M2>89.99,M2<95),2,IF(AND(M2>94.99,M2<100),3,IF(AND(M2>99.99,M2<105),4.5,IF(AND(M2>104.99,M2<110),6.5,IF(AND(M2>109.99,M2<120),8.5,IF(AND(M2>119.99,M2<130),10.5,IF(M2>129.99,13.5,"N/A"))))))))
O3	=IF(M2>0.85,IF(M2<0.9,1,IF(M2<0.95,2,IF(M2<1,3,IF(M2<1.05,4.5,IF(M2<1.1,6.5,IF(M2<1.2,8.5,IF(M2<1.3,10.5,13.5))))))),"N/A")
O4	=LOOKUP(M2,{0,0.8501,0.9,0.95,1,1.05,1.1,1.2,1.3},{"N/A",1,2,3,4.5,6.5,8.5,10.5,13.5})