Conditional formula

[atmicmu]

I need some help

(don't worry about the dutch words, not important for the question)

So I'm making a sheet that can keep my income and costs every month and calculate my taxes etc automaticly.
Fot taxes and other stuff in Belgium we use a system based on net income according to the following table;

Belastingschijf	Inkomensschijf	Tarief belasting
Schijf 1	€ 0 tot €12 990	25%
Schijf 2	€ 12 990,01 t/m € 22 290	40%
Schijf 3	€ 22 290,01 t/m € 39 660	45%
Schijf 4	Meer dan 39 660,01 euro	50%

<tbody style="box-sizing: border-box;">
</tbody>

So, what I can't find is how to formulate in 1 cell that the % tax has to be according to this table?

Thanks for helping!

 

[wideboydixon]

Not sure how this converts to Dutch format formula but here's what I'd do:

Book1
	A	B	C
1	Amount	Tax %	Tax Value
2	€ 10,000.00	25%	€ 2,500.00
3	€ 12,990.00	25%	€ 3,247.50
4	€ 12,990.01	40%	€ 5,196.00
5	€ 15,000.00	40%	€ 6,000.00
6	€ 20,000.00	40%	€ 8,000.00
7	€ 25,000.00	45%	€ 11,250.00
8	€ 30,000.00	45%	€ 13,500.00
9	€ 40,000.00	50%	€ 20,000.00
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE)
C2	=$A2*$B2

Maybe it's:

=VLOOKUP($A2,{0\0.25;12990,01\0.4;22290,01\0.45;39660,01\0.5},2,TRUE)

WBD

 

[atmicmu]

Thank you WBD!

I think I understand where you are going with this. What I need is the following;

When my total income minus costs = X
And the calculation you make is Y

How do i: Create 1 cell that automaticly looks at X and decides what % to use (Y) to give me a total that is (gros-taxes=net)

 

[wideboydixon]

I guess that would be column D:

Book1
	A	B	C	D
1	Amount	Tax %	Tax Value	Net
2	€ 10,000.00	25%	€ 2,500.00	€ 7,500.00
3	€ 12,990.00	25%	€ 3,247.50	€ 9,742.50
4	€ 12,990.01	40%	€ 5,196.00	€ 7,794.01
5	€ 15,000.00	40%	€ 6,000.00	€ 9,000.00
6	€ 20,000.00	40%	€ 8,000.00	€ 12,000.00
7	€ 25,000.00	45%	€ 11,250.00	€ 13,750.00
8	€ 30,000.00	45%	€ 13,500.00	€ 16,500.00
9	€ 40,000.00	50%	€ 20,000.00	€ 20,000.00
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE)
C2	=$A2*$B2
D2	=$A2*(1-VLOOKUP($A2,{0,0.25;12990.01,0.4;22290.01,0.45;39660.01,0.5},2,TRUE))

WBD