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, .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

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.

Find more posts tagged with

Sheets

Formulas

Services

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…