Trouble with nested IF formula

Mike Tomei

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

Heather Duff

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

Mike Tomei

Update: I made a change to the formula, but I'm still having issues. I'm still getting only 94 as a result, when I'm expecting 164 to be the correct result. Am I missing something simple here? Thanks. Revised formula:

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

Heather Duff

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

Mike Tomei

That was it. Quotes were the issue. Thanks for the help!

Heather Duff

@Mike Tomei Glad it worked! Happy to help.