#### 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)

Options
edited 12/09/19

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?

• ✭✭✭✭✭✭
Options

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")))))))

• Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Thanks for catching that Craig!

This discussion has been closed.