IF(AND Equation - Comma Interrupts Greater Than Logic


Hello - I have an IF AND equation that is based on a "Tier" and "Max Amount." For example, if someone is "Tier 1", their max amount is "$100"; If they are "Tier 2", their max amount is "$200", etc.

My equation works properly, except when I reach the thousands. For the equation below, if someone is Tier 4 and enters any amount above 600, the cell should display $600. This works through 999; at 1000, the cell displays "$1,000":

=IF(AND([Mileage Tier]@row = "Tier 4", [Amount Requested by Employee]@row <= "600"), [Amount Requested by Employee]@row, IF(AND([Mileage Tier]@row = "Tier 4", [Amount Requested by Employee]@row > "600"), "$600.00", " "))

I've tried using the "thousands" format on the Smartsheet toolbar to ensure the amount entered is being recognized as "1000" instead of "1,000", but this amount (and larger) still breaks the equation's logic.

Any ideas how to get around this?

Best Answer

  • ahook
    ahook ✭✭
    Answer ✓

    Try removing the quotation marks around the three 600 values in your formula. You want the logical comparison to be to the number value 600 and the quotes make it the string value 600.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!