HELP! Nested IF(OR formula

jb@59069

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.

J. Craig Williams

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

J. Craig Williams

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

J. Craig Williams

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

 

J. Craig Williams

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