Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
HELP! Nested IF(OR formula
Hello, I desperately need help with getting a nested formula to work. Not sure if it needs to be an IF(OR (OR or an IF(OR (AND. Here is what is currently working:
=IF(OR([Project/Event]1 = "NRM-1117", [Project ID]1 = "NRM Logistics"), "RATE5", "RATE2")
Now what I need is to sum up the total hours for a specific employee to insert RATE6. This same employee can have RATE5 as determined by the existing formula.
RATE6 for this employee can be determined by several columns; Employee/Contractor, Project/Event (anything other than an "NRM-xxxx" value would trigger RATE6, Charge Description/Code (anything other that "7560" would trigger RATE6, or Project ID (anything other than "NRM Logistics" would trigger RATE6.
Please help!
Thank you.
Comments
-
Which takes precedence, RATE2 or RATE6? I'm assuming RATE5 takes precedence over RATE6.
It looks like when Project ID = "NRM Logistics" then RATE5 and when not "NRM Logistics" then RATE6.
When it is ever RATE2?
Update 2017-11-01 - sheet deleted
See this editable version of the logic I as I understand it.
(deleted)
(Only Expected Value is editable)
Craig
-
It appears what you need is a little design work. In the short term, I would use LOOKUP tables. I'm working on dynamic dropdown, but it isn't ready for prime time yet.
It also seems like there is way too much info provided when you have only a few questions to answer
NRM Logistics? If yes RATE5
TG (I assume that is a person)? If yes, RATE6
Otherwise, RATE2
everything else just a red herring.
Craig
-
Here's a thing about Nested IF's .... once they get to a solution, they stop looking.
So
NRM Logistics - if yes, RATE5 (for TG too)
If TG RATE6
Otherwise - RATE2
So in Smartsheet formula like thing
=IF([Project ID]23 = "NRM Logistics", "RATE5", IF([Employee]23 = "TG", "RATE6", "RATE2"))
as simple as that (for row 23)
If instead TG was always RATE6, then you'd just swap the order
=IF([Employee]23 = "TG", "RATE6", IF([Project ID]23 = "NRM Logistics", "RATE5", "RATE2"))
I didn't test that and know I don't have the column names correct but you should get the idea.
Craig
-
Yep, that was my first example, the RATE6 for TG was only further explanation of how they work. Sorry if that was not clear.
Glad it is working and glad the LOOKUP suggestion helped too.
The sheet I shared is an example of how I'm trying to do the harder / most likely to be expanded NestedIF's and was a big part of my talk at ENGAGE. I'm still trying to find a decent way to explain logic for formulas to people not well versed in control & logic, Boolean logic, and programming. I'm unsharing the sheet. If you want a copy before I delete it, drop me an email at jcwill23@gmail.com and I'll share it with you to copy.
Craig
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives