IF Formula

Linda Hoydic

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.

Linda Hoydic

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!

Paul Newcome

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

Linda Hoydic

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!