Nested IF for Numbers with Greater Than, Less Than, Etc.


Hello SmartSheet Community!

I am trying to get a number that has it's own formula in it, to then be the number that is done in another formula on another cell in the same sheet. 

I'll go over this to ensure there is no confusion about what I am doing. 


First, my nested IF statement that is not working:

=IF(System5, >3.25, 0.25, IF(System5, <3.24, 0.18, IF(System5, <2.99, 0.1))) [ OLD ]

=IF(System5 = >3.25, 0.25, IF(System5 = <3.24, 0.18, IF(System5 = <2.99, 0.1)))  [ NEW ]



Second, what is in [System]3 :

=SUM(System2 / System3) 


What I am trying to accomplish:

I would like the column for Closer Rate Per Watt's value to be placed under System column. 

I would like the Closer Rate to change based on the Price Per Watt 

 - IF Price Per Watt is $3.25 or greater THEN make Closer Rate Per Watt = $0.25

 - IF Price Per Watt is $3.00 TO $3.24 THEN make Closer Rate Per Watt = $0.18

 - IF Price Per Watt is $2.99 OR less THEN make Closer Rate Per Watt = $0.10


The Sheet Itself

Smartsheet Nested IF Error showing in sheet


Theory for Error:

I do not believe SmartSheets is currently able to do math based on conditional values. But I am really really hoping I am super wrong. 


  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 01/17/19

    You may just need a helper column to dump data in and use in your other formulas. 

    Your first formula just needs to be rearranged: 

    **Edited =IF(System1 >= 3.25, 0.25, IF(System1 <= 2.99, 0.1, 0.18))


  • Joseph Edwards
    edited 01/18/19

    You're the best, thank you! 


    Here is where I am at: 



    So, I tried doing the holding column like you had suggested by using the:


    formula, and then plugging it into the cell like so:

    =SUM([System]1 / [Holding]1) 

    where HOLDING column held the =([Installation]1) formula.

    and still it returns with #INVALID OPERATION


    Like I said, video does a much better job of showing what's going on inside the sheet.

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    I might need another screenshot or share the sheet temporarily.

    Should your formula be only =([System]1 / [Holding]1). I'm not sure what the SUM function is helping you with. 


  • Joseph Edwards

    I'm fairly new to formulas, so I was using the SUM function to make it do math, and when I took it out, it still has the same result, #INVALID OPERATION

    I am happy to temporarily give you access to see if you can figure out what I have going on. What's the best way for me to go about sharing this with you?

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Share to :

    I'll take a look and see if i can solve for it. You can also make a copy of your sheet and share the copy. I don't want to break anything. 

  • Joseph Edwards

    Awesome, I just sent off the invitation, thank you for your help!

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Fixed. Your results were being applied as text rather than a number. removed quotes and it works. You may need to adjust how you do the % if want to actually show the % in that column but otherwise, it looks like it works now. 

  • Joseph Edwards

    Awesome, thank you for your time and effort, I really appreciate the help. 

  • Joseph Edwards

    I would like to share what happened here for anyone else with this issue:


    Things I did wrong:

    I put quotes in my code, that makes it so it identifies as a TEXT value, rather than numerical, which is why it didn't want to do math. Because you don't do math with TEXT you do math with numbers. Right? 

    The written SmartSheet Formula For the DropDown Giving a Number:

    For those who would like to see the resulting code, here it is:

    =IF([Loan Code]@row = "EB - 18 Months - SAC", 7.32, IF([Loan Code]@row = "EB - 24 Months - SAC", 9.87, IF([Loan Code]@row = "EB - FIXED 4.99 - 20 yr", 14.55, IF([Loan Code]@row = "EB - FIXED 5.99 - 20 yr", 11.8, IF([Loan Code]@row = "EB - FIXED 6.99 - 20 yr", 8.7, IF([Loan Code]@row = "EB - COMBO 7030 - 20 yr 4.99", 11.63, IF([Loan Code]@row = "EB - COMBO 7030 - 20 yr 5.99", 9.71, IF([Loan Code]@row = "EB - COMBO 7030 - 20 yr 6.99", 7.54, IF([Loan Code]@row = "DD - 20 yr - 6.99", 9, IF([Loan Code]@row = "DD - 20 yr - 5.99", 14, IF([Loan Code]@row = "DD - 12 yr - 5.99", 9, IF([Loan Code]@row = "DD - 12 yr - 4.99", 14, IF([Loan Code]@row = "RF - 20 yr - 6.59", 0, IF([Loan Code]@row = "RF - 25 yr - 6.59", 0))))))))))))))


    What a thing right? Thank you @NLarson for this!!

    The written SmartSheet Formula For The Greater Than, Less Than Nested Formula

    =IF(System5 >= 3.25, 0.25, IF(System5 <= 2.99, 0.1, 0.18)) 


    Really clean formula, very nice work provided by @NLarson

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!