#### 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

Options
✭✭✭✭✭✭

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.

Thank you.

Tags:

• ✭✭✭✭✭✭
edited 11/01/17
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Yep, that was my first example, the RATE6 for TG was only further explanation of how they work. Sorry if that was not clear.