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

Joseph Edwards

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, .24, 0.18, IF(System5, <2.99, 0.1))) [ OLD ]

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

Returns error: #INCORRECT ARGUMENT  [OLD]

Returns error: #INVALID OPERATION [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

--------------------

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

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

You're the best, thank you! 

UPDATE:

Here is where I am at:

https://youtu.be/A1ThxZgiYDM 

 

---------- VIDEO HELPS MAKE IT EASY BUT HERE IS THE TEXT SIDE ---

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

=([Installation]1) 

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

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

Share to : nicolai.larsen@heritagebanknw.com

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

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