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 A2.
2. Actual Emissions -> column B3.
3. Emission level: lower than 3, 3, 4 or higher than 4-> column C4.
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:
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, but if value is >4 and engine is Diesel then Gasoline tax scale (abowe) applies for Diesel engines.
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.
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))))))))+LOOKUP(C2;{0;3;4;5};{3;2;1;0}) + IF(D1="Y";2;0)
Problem with this formula is that it does not apply Gasoline scale to Diesel engines who's values in column C is higher then 4.This is just to complicated for me therefore your help woul be highly appriciated.
1. Fuel type (D=diesel, G=gasoline) -> column A2.
2. Actual Emissions -> column B3.
3. Emission level: lower than 3, 3, 4 or higher than 4-> column C4.
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:
CO2 emissin (g/km) Gasoline (% tax) Diesel(% tax)
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 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, but if value is >4 and engine is Diesel then Gasoline tax scale (abowe) applies for Diesel engines.
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.
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))))))))+LOOKUP(C2;{0;3;4;5};{3;2;1;0}) + IF(D1="Y";2;0)
Problem with this formula is that it does not apply Gasoline scale to Diesel engines who's values in column C is higher then 4.This is just to complicated for me therefore your help woul be highly appriciated.
Last edited: