Trouble with nested IF formula

Mike Tomei
Mike Tomei ✭✭
in Formulas and Functions

I'm trying to "round" a value from one cell to conform to a series of selected values using a nested IF formula. The attached image shows the original value in cell [Column2]78 and the nested IF formula is in cell [Column2]85. Based on my formula, [Column2]85 should be showing a value of 164, but it's stuck showing 94. Hoping someone can point out what I'm doing wrong. Thanks! Here's the formula:

=IF([Column2]78 <= "94", "94", IF(AND([Column2]78 > "94", [Column2]78 <= "109"), "109", IF(AND([Column2]78 > "109", [Column2]78 <= "113"), "113", IF(AND([Column2]78 > "113", [Column2]78 <= "123"), "123", IF(AND([Column2]78 > "123", [Column2]78 <= "130"), "130", IF(AND([Column2]78 > "130", [Column2]78 <= "137"), "137", IF(AND([Column2]78 > "139", [Column2]78 <= "164"), "164"), IF(AND([Column2]78 > "164", [Column2]78 <= "189"), "189")))))))

Nested IF.jpg


· Share on FacebookShare on Twitter

Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓

    Hi @Mike Tomei ,


    Try removing the quotation marks you have around the numbers. I think putting quotation marks around the numbers makes them function as text rather than a value, which changes the formula completely.


    Let me know if it that fixes it.


    Best,

    Heather

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!