Help in calculating tax % for Co2 emissions?

[Riki]

I'm trying to calculate degree of tax for car CO2 emissions. There are
several conditions I need to take into account.

1. Fuel type (D=diesel, G=gasoline) -> column A
2. Actual Emissions -> column B
3. Emission level: lower than 3, 3, 4 or higher than 4 -> column C
4. Level of particles in disel engine, higher or lower than 0.005 g/km (Yes
or No)
5. Calculating degree of tax -> column E

A B C D E

1 G 117 4 N %
2 D 193 3 Y ....
3 G 131 5 N

Degree of tax for Gasoline and Diesel engines is set as following:

Gasoline(% tax) Diesel (%tax)
CO2 emission(g/km)

0<=110 1 2

110<120 2 4

120<140 3 5
140<150 5 7
150<170 8 11
170<190 12 15
190<210 16 19

230 20 23

I got so far with the formula:
=(IF(A1="D";IF(B2<=110;2;IF(B2<=120;4;IF(B2<=140;5;IF(B2<=150;7;IF(B2<=170;11;IF(B2<=190;15;IF(B2<=210;19;23)))))));IF(B2<=110;1;IF(B2<=120;2;IF(B2<=140;3;IF(B2<=150;5;IF(B2<=170;8;IF(B2<=190;12;IF(B2<=210;16;20))))))))

This actually works fine. Problem occurs when I have to use other 2
conditions (in column C and D). Namely if in column C value is less then 3,
3% tax is added to result in column E. If value is 3, 2% is added and if
value is 4, 1% is added, if value is >4 nothing is added. Condition in column
D applies only to diesel engines: if value is Y, additional 2% is added to
column E, in value is N nothing is added.

This is just to complicated for me therefore your help woul be highly
appriciated.

 

[Jacob Skaria]

Once you get in the % based on CO2 emission...use the below formula to add
the condition based on column C and Column D

In E2
=<your formula> + LOOKUP(C2,{0,3,4,5},{3,2,1,0}) + IF(D1="Y",2,0)

If you break it down
=LOOKUP(C2,{0,3,4,5},{3,2,1,0}) will return the additional % based on
emission level and
=IF(D1="Y",2,0) will return 2 for diesel

I would suggest using a LOOKUP rather then using multiple IF conditions in
your formula.


If this post helps click Yes

 

[Jacob Skaria]

Something like this. (To be tested)

=LOOKUP(B1,{0,111,121,141,151,171,191,231},{1,2,3,5,8,12,16,20})
+IF(D1="Y",LOOKUP(B1,{0,111,151},{1,2,3}),0)
+LOOKUP(C1,{0,3,4,5},{3,2,1,0})
+IF(D1="Y",2,0)

If this post helps click Yes

 

[Riki]

Jacob you rule!

Thank you for your help, with some adjustments I've made it work perfectly.

Thank again and best regards.