Struggling with nested IF formula
Looking to leave cell blank if current month is blank, if not return a value based on the formula. Variable based on what the current month is. I keep getting an incorrect formula error. It works with one month but when I try to add the next month it errors out. Needing this for all 12 months. Thank you in advance!
=IF([Current Month for Collection]@row = "July", IF(July@row = "", "", (July@row - [Monthly Average]@row) / [Monthly Average]@row, IF([Current Month for Collection]@row = "August", IF(August@row = "", "", (August@row - [Monthly Average]@row) / [Monthly Average]@row, ""))))
Answers
-
Is the Current Month for Collection in date format? If yes, use numbers 1-12 for the month instead of month name.
...
-
It isn't in date format, it is the month spelled out, however that cell is populated through another formula if that matters? I did get this to work with only the July piece, but when I started nesting it failed.
-
Try this..
=IF([Current Month for Collection]@row = "July", IF(July@row = "", "", (July@row - [Monthly Average]@row) / [Monthly Average]@row), IF([Current Month for Collection]@row = "August", IF(August@row = "", "", (August@row - [Monthly Average]@row) / [Monthly Average]@row), ""))
...
-
Thank you! That is exactly what I have but with two less parentheses. When I try it it just adds back the other two parentheses and doesn't work…
-
Your formula is missing the ) after the
/ [Monthly Average]@row),
Yours -
(July@row - [Monthly Average]@row) / [Monthly Average]@row,
Mine -
(July@row - [Monthly Average]@row) / [Monthly Average]@row),
...
-
Oh jeez how did I miss that? Thank you so much, that fixed it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!