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
jb@59069
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.

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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

     

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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.

    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 

This discussion has been closed.