Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
If(And( Greater Than Less Than)
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!
Comments
-
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")))))))
-
Thanks! I should have checked that before uploading the question. I appreciate the help though!
-
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
-
Thanks for catching that Craig!
