IF Formula
I'm trying to write a formula that is technically an IF/OR formula. I have 3 IF statements that need to be considered. I've added each separately to make sure my statement is correct. It's when I pull all three together it blows up. I have 3 columns that are being used in this formula
Day of Month, Current Month and Pay Periods Per Month
=IF([Day of Month]@row >= 30, [Current Month]@row * [Pay Periods Per Month]@row, IF([Day of Month]@row < 15, [Current Month]@row * [Pay Periods Per Month]@row - 2), IF([Day of Month]@row >= 15, [Current Month]@row * [Pay Periods Per Month]@row - 1))
The current error message is #incorrect argument set, I've also had #invalid operation errors as well in my efforts to make this work.. If reordered the formula and tried an IF(OR in the middle (IF(OR(>= 15, < 30). The error seems to occur related to the middle range - which is technically greater than or equal to 15 but less than 30. I can make it work with greater than/equal to 30 and less than 15, but it returns blank for the last IF statement. I tried adding a false answer for this but I think that gives me #unparseable.
Any help would be appreciated.
Best Answer
-
Thank you, your suggestion helped a lot. I had to make a couple of adjustments, as I had tried so many variations, what a posted was wrong, too. Using your suggested formula, I was able to correct my error and tweak it to account for varied Accrual Start Dates and my results are now correct. Here is my final formula:
=SUM([Current Month]@row - [Accrual Start Month]@row) * [Pay Periods Per Month]@row + IF([Day of Month]@row >= 30, 2, IF([Day of Month]@row >= 15, 1, 0))
Thanks so much!
Answers
-
Your existing formula just has a misplace parenthesis after the -2 portion. That should be at the end of the formula.
Your formula could also be written this way:
=[Current Month]@row * [Pay Periods Per Month]@row - IF([Day of Month]@row >= 30, 0, IF([Day of Month]@row >= 15, 1, 2))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you, your suggestion helped a lot. I had to make a couple of adjustments, as I had tried so many variations, what a posted was wrong, too. Using your suggested formula, I was able to correct my error and tweak it to account for varied Accrual Start Dates and my results are now correct. Here is my final formula:
=SUM([Current Month]@row - [Accrual Start Month]@row) * [Pay Periods Per Month]@row + IF([Day of Month]@row >= 30, 2, IF([Day of Month]@row >= 15, 1, 0))
Thanks so much!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!