I am trying to calculate the tax Payable for person A. Formula to calculate in Table 2, cell D12, and calculate from D10. Salary in D10 will change depending on user input.

I have used few formula but amount comes out wrong. I think i should use looping but i don't know how, and how to stop looping based on the salary in D10? (I would like to use excel formula and not VBA as I have to handover to someone else)

Table 1

Table 2

Formula that I have used but wrong amount: =SUMPRODUCT((Q4:Q13<=D10) * (D10-Q4:Q13) * (Sheet1!R4:R13)/100)

I am trying to calculate the tax Payable for person A. Formula to calculate in Table 2, cell D12, and calculate from D10. Salary in D10 will change depending on user input.

I have used few formula but amount comes out wrong. I think i should use looping but i don't know how, and how to stop looping based on the salary in D10? (I would like to use excel formula and not VBA as I have to handover to someone else)

Table 1

Table 2

Formula that I have used but wrong amount: =SUMPRODUCT((Q4:Q13<=D10) * (D10-Q4:Q13) * (Sheet1!R4:R13)/100)

• excel
• loops
• excel-formula
• sumproduct

Share Improve this question Follow asked Mar 13 at 2:21 Asifa.KAsifa.K 3555 bronze badges 2

• Is Table1 complete? If the first row shows 'amounts from 0 to 5000 attract 0%', then Row 13 would show 'amounts from 220000 to 2M attract 30%', then what happens to amounts over 2000000? – tinazmu Commented Mar 13 at 3:47
• Please, add data input as table Markdown and add expected output. Right now your question cannot be fully reproduced. – Foxfire And Burns And Burns Commented Mar 13 at 12:32

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Instead of using your table, I copied this table of Malaysia Individual Tax Rates which is set up much more efficiently for a simple formula:

I added the first row (0 taxable income).

I NAMEd the table: taxMYR

The formula then becomes simply, with the taxable income in A2:

=(A2-VLOOKUP(A2,taxMYR,1))*VLOOKUP(A2,taxMYR,4)+VLOOKUP(A2,taxMYR,3)

Note that the second column of the table is not used at all.

Using the information from this URL to plug the gap in your table, I proffer this formula:

=LET(inc,D10,tinc,SCAN(0,Q4:Q12,LAMBDA(a,c,a+c)),ttax,SCAN(0,S4:S12,LAMBDA(a,c,a+c)),pos,XMATCH(inc,tinc,-1),IF(inc>MAX(tinc),MAX(ttax)+0.3*(inc-MAX(tinc)),INDEX(ttax,pos+1)-(INDEX(tinc,pos+1)-inc)*INDEX(R4:R12,pos+1)))