3 Variable Progressive Tax

[fscardoelli]

Hello,

I have been trying to solve a problem that involves using three different variables (Depth, Production level, and Year) to retrieve a number from a table.

What I want is, given a certain production level (which can be any number), a depth (which will be one of the three options below), and a year (which will be either 1,2,3, or 4+) to find the value in the third column ("Deduction") of the appropriate table below.

I have tried using IF/OR, IF/AND, and INDEX/MATCH functions, but so far have had no luck. I would greatly appreciate any help with this problem!

Onshore					Shallow Water (<400m Depth)			Deep Water (>400m Depth)
Year 1					Year 1					Year 1
Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate
0.0	31.0	0	0.0%		0.0	61.9	0	0.0%		0.0	92.9	0	0.0%
31.0	62.0	450	10.0%		61.9	92.8	900	10.0%		92.9	123.8	1350	10.0%
62.0	92.9	675	20.0%		92.8	123.8	1125	20.0%		123.8	154.7	1575	20.0%
92.9	123.8	900	30.0%		123.8	154.7	1350	30.0%		154.7	185.7	1800	30.0%
123.8	154.7	1029	35.0%		154.7	185.6	1479	35.0%		185.7	216.6	1929	35.0%
>154.7		1181	40.0%		>185.6		1631	40.0%		>216.6		2081	40.0%
Year 2					Year 2					Year 2
Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate
0.0	24.1	0	0.0%		0.0	51.6	0	0.0%		0.0	72.2	0	0.0%
24.1	55.0	350	10.0%		51.6	82.5	750	10.0%		72.2	103.1	1050	10.0%
55.0	86.0	575	20.0%		82.0	113.4	975	20.0%		103.1	134.1	1275	20.0%
86.0	116.9	800	30.0%		113.4	144.4	1200	30.0%		134.1	165.0	1500	30.0%
116.9	147.8	929	35.0%		144.4	175.3	1329	35.0%		165.0	195.9	1629	35.0%
>147.8		1081	40.0%		>175.3		1481	40.0%		>195.9		1781	40.0%
Year 3					Year 3					Year 3
Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate
0.0	17.2	0	0.0%		0.0	34.4	0	0.0%		0.0	51.6	0	0.0%
17.2	48.2	250	10.0%		34.4	65.3	500	10.0%		51.6	82.5	750	10.0%
48.2	79.1	475	20.0%		65.3	96.3	775	20.0%		82.5	113.4	975	20.0%
79.1	110.0	700	30.0%		96.3	127.2	950	30.0%		113.4	144.4	1200	30.0%
110.0	140.9	829	35.0%		127.2	158.1	1079	35.0%		144.4	175.3	1329	35.0%
>140.9		981	40.0%		>158.1		1231	40.0%		>175.3		1481	40.0%
Year 4					Year 4+					Year 4+
Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate		Prod Range (000 b/d)	Deduction	Rate
0.0	10.3	0	0.0%		0.0	20.6	0	0.0%		0.0	31.0	0	0.0%
10.3	41.3	150	10.0%		20.6	51.6	300	10.0%		31.0	61.9	450	10.0%
41.3	72.2	375	20.0%		51.6	82.5	525	20.0%		61.9	92.8	675	20.0%
72.2	103.1	600	30.0%		82.5	113.4	750	30.0%		92.8	123.7	900	30.0%
103.1	134.0	729	35.0%		113.4	144.4	879	35.0%		123.7	154.7	1029	35.0%
>134.0		881	40.0%		>144.4		1031	40.0%		>154.7		1181	40.0%

<colgroup><col width="68" style="width:51pt"> <col width="65" style="width:49pt" span="13"> </colgroup><tbody>
</tbody>

 

[ranman256]

You need a single table,
depth,year,RngStart,RngEnd,Deduct,Rate

put all tables into the sheet. Make a custom function that gets the input values ,
reads the table, then returns the value.

 

[Eric W]

Welcome to the forum.

As long as you make sure that the tables are the same size and are positioned exactly the same relative to each other, you can do it.

Consider this sheet:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Onshore					Shallow Water (<400m Depth)					Deep Water (>400m Depth)
2
3	Year 1					Year 1					Year 1
4	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
5	0	31	0	0.00%		0	61.9	0	0.00%		0	92.9	0	0.00%
6	31	62	450	10.00%		61.9	92.8	900	10.00%		92.9	123.8	1350	10.00%
7	62	92.9	675	20.00%		92.8	123.8	1125	20.00%		123.8	154.7	1575	20.00%
8	92.9	123.8	900	30.00%		123.8	154.7	1350	30.00%		154.7	185.7	1800	30.00%
9	123.8	154.7	1029	35.00%		154.7	185.6	1479	35.00%		185.7	216.6	1929	35.00%
10	154.7		1181	40.00%		185.6		1631	40.00%		216.6		2081	40.00%
11
12	Year 2					Year 2					Year 2
13	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
14	0	24.1	0	0.00%		0	51.6	0	0.00%		0	72.2	0	0.00%
15	24.1	55	350	10.00%		51.6	82.5	750	10.00%		72.2	103.1	1050	10.00%
16	55	86	575	20.00%		82	113.4	975	20.00%		103.1	134.1	1275	20.00%
17	86	116.9	800	30.00%		113.4	144.4	1200	30.00%		134.1	165	1500	30.00%
18	116.9	147.8	929	35.00%		144.4	175.3	1329	35.00%		165	195.9	1629	35.00%
19	147.8		1081	40.00%		175.3		1481	40.00%		195.9		1781	40.00%
20
21	Year 3					Year 3					Year 3
22	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
23	0	17.2	0	0.00%		0	34.4	0	0.00%		0	51.6	0	0.00%
24	17.2	48.2	250	10.00%		34.4	65.3	500	10.00%		51.6	82.5	750	10.00%
25	48.2	79.1	475	20.00%		65.3	96.3	775	20.00%		82.5	113.4	975	20.00%
26	79.1	110	700	30.00%		96.3	127.2	950	30.00%		113.4	144.4	1200	30.00%
27	110	140.9	829	35.00%		127.2	158.1	1079	35.00%		144.4	175.3	1329	35.00%
28	140.9		981	40.00%		158.1		1231	40.00%		175.3		1481	40.00%
29
30	Year 4+					Year 4+					Year 4+
31	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
32	0	10.3	0	0.00%		0	20.6	0	0.00%		0	31	0	0.00%
33	10.3	41.3	150	10.00%		20.6	51.6	300	10.00%		31	61.9	450	10.00%
34	41.3	72.2	375	20.00%		51.6	82.5	525	20.00%		61.9	92.8	675	20.00%
35	72.2	103.1	600	30.00%		82.5	113.4	750	30.00%		92.8	123.7	900	30.00%
36	103.1	134	729	35.00%		113.4	144.4	879	35.00%		123.7	154.7	1029	35.00%
37	134		881	40.00%		144.4		1031	40.00%		154.7		1181	40.00%
38

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

I copied your tables, and made one tiny change to it, by removing the > in front of the last range value of each sub-table.

Then I built the inquiry on Sheet2 (you can put it anywhere of course) like this:

Excel 2012

	A	B	C	D
1	Onshore
2	Shallow Water (<400m Depth)
3	Deep Water (>400m Depth)
4
5	Depth	Year	Production	Rate
6	Deep Water (>400m Depth)	3	55	10%

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A1 =Sheet1!A1 A2 =Sheet1!F1 A3 =Sheet1!K1 D6 =VLOOKUP(C6,OFFSET(Sheet1!$A$5:$D$10,(B6-1)*9,(MATCH(A6,$A$1:$A$3,0)-1)*5),4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

A1:A3 is a mini-table I created for 2 purposes. First, I used it for Data Validation in A6. So the user can easily select the depth he wants with a drop-down. Next, it's used in the formula in D6. B6 could easily be a drop-down too if you want. So enter the values in A6:C6, then enter the formula in D6, and you should have what you want.

Hope this helps!

 

[fscardoelli]

Welcome to the forum.

As long as you make sure that the tables are the same size and are positioned exactly the same relative to each other, you can do it.

Consider this sheet:

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Onshore					Shallow Water (<400m Depth)					Deep Water (>400m Depth)
2
3	Year 1					Year 1					Year 1
4	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
5	0	31	0	0.00%		0	61.9	0	0.00%		0	92.9	0	0.00%
6	31	62	450	10.00%		61.9	92.8	900	10.00%		92.9	123.8	1350	10.00%
7	62	92.9	675	20.00%		92.8	123.8	1125	20.00%		123.8	154.7	1575	20.00%
8	92.9	123.8	900	30.00%		123.8	154.7	1350	30.00%		154.7	185.7	1800	30.00%
9	123.8	154.7	1029	35.00%		154.7	185.6	1479	35.00%		185.7	216.6	1929	35.00%
10	154.7		1181	40.00%		185.6		1631	40.00%		216.6		2081	40.00%
11
12	Year 2					Year 2					Year 2
13	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
14	0	24.1	0	0.00%		0	51.6	0	0.00%		0	72.2	0	0.00%
15	24.1	55	350	10.00%		51.6	82.5	750	10.00%		72.2	103.1	1050	10.00%
16	55	86	575	20.00%		82	113.4	975	20.00%		103.1	134.1	1275	20.00%
17	86	116.9	800	30.00%		113.4	144.4	1200	30.00%		134.1	165	1500	30.00%
18	116.9	147.8	929	35.00%		144.4	175.3	1329	35.00%		165	195.9	1629	35.00%
19	147.8		1081	40.00%		175.3		1481	40.00%		195.9		1781	40.00%
20
21	Year 3					Year 3					Year 3
22	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
23	0	17.2	0	0.00%		0	34.4	0	0.00%		0	51.6	0	0.00%
24	17.2	48.2	250	10.00%		34.4	65.3	500	10.00%		51.6	82.5	750	10.00%
25	48.2	79.1	475	20.00%		65.3	96.3	775	20.00%		82.5	113.4	975	20.00%
26	79.1	110	700	30.00%		96.3	127.2	950	30.00%		113.4	144.4	1200	30.00%
27	110	140.9	829	35.00%		127.2	158.1	1079	35.00%		144.4	175.3	1329	35.00%
28	140.9		981	40.00%		158.1		1231	40.00%		175.3		1481	40.00%
29
30	Year 4+					Year 4+					Year 4+
31	Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate		Prod Range (000 b/d)		Deduction	Rate
32	0	10.3	0	0.00%		0	20.6	0	0.00%		0	31	0	0.00%
33	10.3	41.3	150	10.00%		20.6	51.6	300	10.00%		31	61.9	450	10.00%
34	41.3	72.2	375	20.00%		51.6	82.5	525	20.00%		61.9	92.8	675	20.00%
35	72.2	103.1	600	30.00%		82.5	113.4	750	30.00%		92.8	123.7	900	30.00%
36	103.1	134	729	35.00%		113.4	144.4	879	35.00%		123.7	154.7	1029	35.00%
37	134		881	40.00%		144.4		1031	40.00%		154.7		1181	40.00%
38

<tbody>
</tbody>

Sheet1​

I copied your tables, and made one tiny change to it, by removing the > in front of the last range value of each sub-table.

Then I built the inquiry on Sheet2 (you can put it anywhere of course) like this:

Excel 2012

	A	B	C	D
1	Onshore
2	Shallow Water (<400m Depth)
3	Deep Water (>400m Depth)
4
5	Depth	Year	Production	Rate
6	Deep Water (>400m Depth)	3	55	10%

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A1 =Sheet1!A1 A2 =Sheet1!F1 A3 =Sheet1!K1 D6 =VLOOKUP(C6,OFFSET(Sheet1!$A$5:$D$10,(B6-1)*9,(MATCH(A6,$A$1:$A$3,0)-1)*5),4) <tbody> </tbody>

<tbody>
</tbody>

A1:A3 is a mini-table I created for 2 purposes. First, I used it for Data Validation in A6. So the user can easily select the depth he wants with a drop-down. Next, it's used in the formula in D6. B6 could easily be a drop-down too if you want. So enter the values in A6:C6, then enter the formula in D6, and you should have what you want.

Hope this helps!

Hello Eric,

Thank you so much for your help! This solved the problem and is exactly what I was looking for, very helpful!

Best,

 

[Eric W]

Happy to help! Thanks for the feedback.