using OR function with long formula

HSabin

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?

Mr. Chris

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

Mr. Chris

https://community.smartsheet.com/discussion/comment/418154#Comment_418154

Awesome! Glad it's working for you.

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

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

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

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

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

Mr. Chris

https://community.smartsheet.com/discussion/comment/418154#Comment_418154

Awesome! Glad it's working for you.