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 forCloser Rate Per Watt's value to be placed underSystemcolumn.
I would like the Closer Rate to change based on thePrice Per Watt
-IFPrice Per Wattis $3.25 or greaterTHENmakeCloser Rate Per Watt= $0.25
-IFPrice Per Wattis $3.00 TO $3.24THENmakeCloser Rate Per Watt= $0.18
-IFPrice Per Wattis $2.99 OR lessTHENmakeCloser 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.
Comments
-
Nic Larsen ✭✭✭✭✭✭你可能只需要一个辅助转储数据列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))
-
You're the best, thank you!
UPDATE:
Here is where I am at:
---------- 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)
whereHOLDINGcolumn 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.
-
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 :[email protected]
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.
-
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.
-
Awesome, thank you for your time and effort, I really appreciate the help.
-
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@NLarsonfor 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
Categories
Check out theFormula Handbook template!
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"
Hi, <\/p>
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
