If(And( Greater Than Less Than)

tsteele

I am having trouble with the following formula:

=IF(AND(Percent1 > 0, Percent1 < 6), "Scope", IF(AND(Percent1 > 5, Percent1 < 11), "AE Selection", IF(AND(Percent1 > 10, Percent1 < 21), "Design", IF(AND(Percent1 > 20, Percent1 < 26), "Bid", IF(AND(Percent1 > 25, Percent1 < 96), "Construction", IF(AND(Percent1 > 95, Percent1 < 100), "Closeout", IF(Percent1 = 100, "Complete", "Hold")))))))

It works in Excel when I named the cell the same as the Smartsheet reference, however it will not work if the number is anything other than 0 or 100. Everything else returns Scope as the in the Status column. Does anyone have any suggestions?

Thanks in advance!

Mike Wilday

The issue is probably how Smartsheets calculates Percentages. Smartsheets shows percentages like 10% but its actally storing that data in decimals. .10 = 10% .9 = 90%... so you will need to adjust your formula to account for the decimal points. 

Try this one!

=IF(AND(Percent1 > .0, Percent1 < .6), "Scope", IF(AND(Percent1 > .5, Percent1 < .11), "AE Selection", IF(AND(Percent1 > .10, Percent1 < .21), "Design", IF(AND(Percent1 > .20, Percent1 < .26), "Bid", IF(AND(Percent1 > .25, Percent1 < .96), "Construction", IF(AND(Percent1 > .95, Percent1 < 1), "Closeout", IF(Percent1 = 1, "Complete", "Hold")))))))

tsteele

Thanks! I should have checked that before uploading the question. I appreciate the help though!

J. Craig Williams

Also, your formula may give you odd results at the median points (like 5.4%, 10.5%, etc...).

By reordering, you can get rid of the AND() and remove one IF statement (anything equal to or greater than 1 is Complete and make a bit more compact. Your formula also has errors because 0.6 is 60%, not 6%

=IF(Percent1 = 0,"Hold", IF(Percent1 < .06, "Scope", IF(Percent1 < .11, "AE Selection", IF(Percent1 < .21, "Design", IF(Percent1 < .26, "Bid", IF(Percent1 < .96, "Construction", IF(Percent1 < 1, "Closeout", "Complete")))))))

0 = Hold

0.001% - 5.999% = Scope

6% - 10.999% = AE Selection

etc...

(to be more accurate, change to <=)

Craig

 

Mike Wilday

Thanks for catching that Craig!