Forum Discussion
Calculating taxes backwards
See the attached version; it uses XLOOKUP.
See the attached version.
Hi again,
Double checked the results and and got an error of 1 cell for certain values. See attached file for test algorithm.
This formula is lightyears from my Excel comprehension so I'd never be able to figure out how and if it can be tweaked.
Thanks again!
That's a matter of interpretation, I think,
For an amount before tax of 18001 kr, the tax is 3384, so the amount after tax is 14617 kr.
You want 14618 kr. That is more than 14617 kr, so the formula rounds up to the next higher amount before tax.
Hello.
HansVogelaar wrote:For an amount before tax of 18001 kr, the tax is 3384, so the amount after tax is 14617 kr.
Correct. I'm with you so far - and as far as I can see the your formula works fine for the exakt value of 18001. However, if the value increased with 1kr to 18002 (up to 18100), we are still in the same tax interval (18001-18100) and the value in your formula goes to the next interval.
I hear what you are saying, but I'm not able to intuitively see the logic in it. In my head the values when counting forwards and backwards should match - but I've been wrong before when it comes to math..
/Q
OK, try this (confirmed with Ctrl+Shift+Enter:
=U162+INDEX(C3:R488,MATCH(INDEX(A3:A488,MATCH(U162,A3:A488-INDEX(C3:R488,0,MATCH(U159,C1:R1,0)))),A3:A488,0),MATCH(U159,C1:R1,0))