using OR function with long formula

I have a "ledger" sheet that I use to calculate real-time budgets based on any changes and shifts that come through a request system. Currently, it's built for F24, however as we move towards F25, I want to add in options to include F25 budget change requests/shifts. In order for my sheet to calculate correctly, I need to add in "F25" options to the formulas on the calculation columns. I've been unsuccessful thus far. Below is my original formula that is working great in my sheet:

=IF([Budget Change Type]@row = "Shift w/in Media", IF([From Fiscal H:]@row = "F24.H2", 0 - [$ Amount**]@row)) + (IF([Budget Change Type]@row = "Shift w/in Media", IF([From Fiscal H:]@row = "F24.H1", 0 + [$ Amount**]@row))) + (IF([Budget Change Type]@row = "Shift Media to Non-Media", IF([From Fiscal H:]@row = "F24.H2", 0 - [$ Amount**]@row))) + IF([Budget Change Type]@row = "Shift Non-Media to Media", IF([To Fiscal H:]@row = "F24.H2", 0 + [$ Amount**]@row))

What I'm wanting to do is add in the same options for F25 that I have for F24 so it will work across both fiscals. Example: @row = "F24.H2" OR "F25.H2" - I'd want to include F25.XX as an option on each place it notates F24.

Any ideas on how to accomplish this one?

Tags:

Best Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Answer ✓

    Based on what you pasted above you need to remove what is in bold below.

    =IF(OR(=IF([Budget Change Type]@row = "Shift w/in Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, IF(OR([From Fiscal H:]@row = "F24.H1", [From Fiscal H:]@row = "F25.H1"), 0 + [$ Amount**]@row, 0)), IF([Budget Change Type]@row = "Shift Media to Non-Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, 0), IF([Budget Change Type]@row = "Shift Non-Media to Media", IF(OR([To Fiscal H:]@row = "F24.H2", [To Fiscal H:]@row = "F25.H2"), 0 + [$ Amount**]@row, 0))))))

Answers

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭

    Hi @HSabin,

    Try this formula below which uses the =IF(OR(

    =IF([Budget Change Type]@row = "Shift w/in Media",

     IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row,

     IF(OR([From Fiscal H:]@row = "F24.H1",[From Fiscal H:]@row = "F25.H1"), 0 + [$ Amount**]@row, 0)),

     IF([Budget Change Type]@row = "Shift Media to Non-Media",

     IF(OR([From Fiscal H:]@row = "F24.H2",[From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, 0),

     IF([Budget Change Type]@row = "Shift Non-Media to Media",

     IF(OR([To Fiscal H:]@row = "F24.H2", [To Fiscal H:]@row = "F25.H2"), 0 + [$ Amount**]@row, 0))))

  • HSabin
    HSabin ✭✭✭✭
    edited 02/06/24

    Hi @Mr. Chris! Thank you for the quick reply and the help.

    When using the complete formula:

    =IF(OR(=IF([Budget Change Type]@row = "Shift w/in Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, IF(OR([From Fiscal H:]@row = "F24.H1", [From Fiscal H:]@row = "F25.H1"), 0 + [$ Amount**]@row, 0)), IF([Budget Change Type]@row = "Shift Media to Non-Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, 0), IF([Budget Change Type]@row = "Shift Non-Media to Media", IF(OR([To Fiscal H:]@row = "F24.H2", [To Fiscal H:]@row = "F25.H2"), 0 + [$ Amount**]@row, 0))))))

    The system returns an "#Incorrect argument set" error.

    Did I paste the formula incorrectly?

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Answer ✓

    Based on what you pasted above you need to remove what is in bold below.

    =IF(OR(=IF([Budget Change Type]@row = "Shift w/in Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, IF(OR([From Fiscal H:]@row = "F24.H1", [From Fiscal H:]@row = "F25.H1"), 0 + [$ Amount**]@row, 0)), IF([Budget Change Type]@row = "Shift Media to Non-Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, 0), IF([Budget Change Type]@row = "Shift Non-Media to Media", IF(OR([To Fiscal H:]@row = "F24.H2", [To Fiscal H:]@row = "F25.H2"), 0 + [$ Amount**]@row, 0))))))

  • HSabin
    HSabin ✭✭✭✭

    Okay. Just tried again and now its returning the #unparseable error.

    =IF([Budget Change Type]@row = "Shift w/in Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, IF(OR([From Fiscal H:]@row = "F24.H1", [From Fiscal H:]@row = "F25.H1"), 0 + [$ Amount**]@row, 0)), IF([Budget Change Type]@row = "Shift Media to Non-Media", IF(OR([From Fiscal H:]@row = "F24.H2", [From Fiscal H:]@row = "F25.H2"), 0 - [$ Amount**]@row, 0), IF([Budget Change Type]@row = "Shift Non-Media to Media", IF(OR([To Fiscal H:]@row = "F24.H2", [To Fiscal H:]@row = "F25.H2"), 0 + [$ Amount**]@row, 0))))))

    The above is what I pasted directly in the cell. I'll keep working on this and try to make sure spaces and commas are all correct. Thanks again for your help in trying to solve this one @Mr. Chris

  • HSabin
    HSabin ✭✭✭✭

    @Mr. Chris - looks like there was a couple extra ")" at the end and it's working now. Thank you SO much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!