Numbers income tax in column only after a cumulative base amount reached

I'm trying to make a formula to reflect a 6% tax rate of a monthly salary only after a cumulative total has been reached on a month by month basis.


So column A is monthly salary. Column B accumulative total. Column C I want to reflect 6% of the monthly salary after column B reaches 10,000.


This is a company cost spreadsheet not an individuals accumulation so if a new person started in the same position the cumulative would revert to zero. Likewise if a person moved position their accumulation to date would transfer with them.


All this to say I believe the only way to construct the function is to reference cells in the same row. I am used to making hidden columns to extend the logic of the functions but can't get my head around how to express this sum as a function. That is Cx =6% of any portion of Ax only when Bx is over 10,000.

MacBook Pro 16″, macOS 13.5

Posted on Sep 14, 2023 4:04 PM

Reply
Question marked as Best answer

It works perfectly well here. You did not post a screenshot of the actual formula in the formula editor. Maybe it includes some preceding equals signs that need to be deleted or there is some other problem. Why no syntax errors for H2 through H16? They should all have the formula, too.


Your problem statement was "Cx =6% of any portion of Ax only when Bx is over 10,000". "Any portion of Ax" didn't quite make sense.


G17 ($9,600) is still less than $10000. According to your problem statement, the result for H17 should be zero, not 6% of 2800. The next row is partially hidden but it looks like G18 is > 10000 so it will be the first row that gets a value.


It seems there should be a more concise formula but this one does what you asked:

=IF(G17>10000,MIN(G17−10000,F17)×0.06,"")

or

=MIN(F17,MAX(0,G17−10000))×0.06


The second one will put 0's until the 10K mark is met, the first will put null strings "".

Posted on Sep 14, 2023 6:08 PM

4 replies

Sep 14, 2023 5:39 PM in response to Badunit

Thanks for the suggestion Badunit.


When I copy and paste (and correct for actual cell number/letter) I get a syntax error message.


IF(G17>=10000,6%*F17,"")



But also there is no allowance for the amount over 10,000. In the highlighted example the accumulated total is 9,600, that means in H17 I would need 6% of 2,800 not the full 3,200 value of F17.

Question marked as Best answer

Sep 14, 2023 6:08 PM in response to privatebydesign

It works perfectly well here. You did not post a screenshot of the actual formula in the formula editor. Maybe it includes some preceding equals signs that need to be deleted or there is some other problem. Why no syntax errors for H2 through H16? They should all have the formula, too.


Your problem statement was "Cx =6% of any portion of Ax only when Bx is over 10,000". "Any portion of Ax" didn't quite make sense.


G17 ($9,600) is still less than $10000. According to your problem statement, the result for H17 should be zero, not 6% of 2800. The next row is partially hidden but it looks like G18 is > 10000 so it will be the first row that gets a value.


It seems there should be a more concise formula but this one does what you asked:

=IF(G17>10000,MIN(G17−10000,F17)×0.06,"")

or

=MIN(F17,MAX(0,G17−10000))×0.06


The second one will put 0's until the 10K mark is met, the first will put null strings "".

Numbers income tax in column only after a cumulative base amount reached

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.