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?
Best Answers
-
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))))))
https://www.linkedin.com/in/zchrispalmer/
-
Awesome! Glad it's working for you.
https://www.linkedin.com/in/zchrispalmer/
Answers
-
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))))
https://www.linkedin.com/in/zchrispalmer/
-
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?
-
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))))))
https://www.linkedin.com/in/zchrispalmer/
-
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
-
@Mr. Chris - looks like there was a couple extra ")" at the end and it's working now. Thank you SO much!!
-
Awesome! Glad it's working for you.
https://www.linkedin.com/in/zchrispalmer/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
