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<=2 10;19;23)))))));IF(B2<=110;1;IF(B2<=120;2;IF(B2<=1 40;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.

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


"Riki" wrote:

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<=2 10;19;23)))))));IF(B2<=110;1;IF(B2<=120;2;IF(B2<=1 40;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.

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
---------------
Jacob Skaria


"Jacob Skaria" wrote:

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


"Riki" wrote:

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<=2 10;19;23)))))));IF(B2<=110;1;IF(B2<=120;2;IF(B2<=1 40;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 you rule!

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

Thank again and best regards.

"Jacob Skaria" wrote:

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


"Riki" wrote:

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<=2 10;19;23)))))));IF(B2<=110;1;IF(B2<=120;2;IF(B2<=1 40;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.