Forum Discussion
How to built exemption formula
mathetes Had a look at it also and wondered about the purpose of so many columns with seemingly redundant information. I changed the formulae in P and Q and added three columns (U, V and W), just to demonstrate how I would have approached it. But perhaps I missed the point altogether. Up to Shaitaan to judge.
I apologize for the long delay in responding. We had guests last evening, so I had to put this aside.
This was an interesting illustration of how difficult it can be to try to make sense of a spreadsheet somebody else has designed. So although I've come up with what I hope is a step in the direction of resolving your problem, it's clearly not altogether right.
IF, big "if", your reversal of a payroll ALWAYS is in the row right after the instance that is being reversed, it seemed to me that maybe the simplest way to reverse things was to add an IF clause to the formulas involved, such that
-IF(C12="RE",-1*valueInRowAbove,YourPreviousFormula)
which takes whatever the value was in the prior row and simply negates it (multiplies by -1); if it's not an RE row, then your prior formula still works.
I don't fully get what's happening in column O, so you'll need to weigh in.
The basic idea, though, is just to, well, reverse that prior row, since that's what you are doing.
mathetes Had a look at it also and wondered about the purpose of so many columns with seemingly redundant information. I changed the formulae in P and Q and added three columns (U, V and W), just to demonstrate how I would have approached it. But perhaps I missed the point altogether. Up to Shaitaan to judge.
Hello Riny_van_Eekelen ,
The working you did really helped. The additional columns in the excels were made to understand the step results as what is going on and how the final formula will look like.
The formula you made is good but I need other way round like you are calculating tax on YTD subject wages (column V) whereas, column V should be aggregate of column W.
Shaitaan Not sure I follow your last sentence. I calculate the YTD tax number and the monthly tax amount (or actually, the tax amount due after each transaction) equals current YTD minus prior year to date. In your example, you have three transactions in July (+30000 -30000 +40000), thus +40000 in total. Of these 40000, 30000 are taxable at 1.95%, thus 585. On rows 24:26 column W, this is reflected by +390 - 390 + 585 = 585.
In other words, the reversal of the initial 30000 cancels out the initial tax payable (390) and the corrected payment of 40000 leads to a new tax amount of 585. In your own example you present it as if the initial 390 remains and 195 is added for the extra 10000. Same end result, but not consistently calculated, resulting in the incorrect amount for the YTD exception used.
