Help with nested function

[Guest]

Help please.
I have cell C2 as an option of either less then 1400cc, more than 2000cc or
a number in between.
I have cell D2 as 'Petrol' or 'Diesel'.
I have cell K2 as a mileage number (ie: 10).
Under certain conditions, I need to calculate the pence per mile for a given
mileage.

This is the function I have compiled
=IF(C2<=1400,K2*0.09,IF(D2="Petrol",K2*0.11,IF(C2<=2000,K2*0.09,IF(C2>2000,IF(D2="Diesel",K2*0.12,K2*0.16)))))

This function returns all correct answers unless I select petrol, with more
than 2000cc. It will not return K2*0.16 which is what I am after?

I have tried many times but I am stuck.

Please can anyone help with completing this nested function, or is there
another way of solving my requirements?

Many thanks,

Colin.

 

[Bob Phillips]

=IF(C2<=1400,K2*0.09,IF(D2="Petrol",IF(C2<2000,K2*0.11,K2*0.16),IF(C2<=2000,K2*0.09,K2*0.12)))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

If D2 is Petrol and C2 > 1400 e.g 2001 the 'IF(D2="Petrol' clause will be
executed and hence you will never get the corret result.

It is not clear (to me) what the rules are for commbination of capacity (cc)
and fuel type.

Do you need AND conditions e.g IF Petrol AND > 2000?

What are K2 values for the following

Petrol Diesel
<=1400 0.09 0.09
<=2000 0.11 (?) 0.0(?)

 

[Guest]

Hello,

The K2 value has no restriction on it and is inputted by the user - it is
the mileage they have done that day in their company vehicle.

C2 - This is either '1400cc or less', '1401 - 2000cc' or '2001 and above'.
The number is inputted by the user.
D2 - This is either Petrol or Diesel. This value is chosen from a list.

I require:
Petrol AND <=1400
Diesel AND <=1400
Petrol AND between 1401 - 2000
Diesel AND between 1401 - 2000
Petrol AND >= 2001
Diesel AND >=2001

Petrol Diesel
<=1400 0.09 0.09
Between 1401-2000 0.11 0.09

Many thanks,

Colin.

 

[Guest]

I meant the conversion factors for K2 but anyway Bob's formula does the trick.

 

[Roger Govier]

Hi Colin, for the range of values you have, the following should work
=IF(C2=">2000",IF(D2="Petrol",16,12),IF(AND(C2="Between
1401-2000",D2="Petrol"),11,9))

 

[Roger Govier]

Sorry, forgot to put *K2 on the end of the formula

=IF(C2=">2000",IF(D2="Petrol",16,12),
IF(AND(C2="Between 1401-2000",D2="Petrol"),11,9))*K2

 

[Guest]

Hello,

Thank you for your suggestions with this problem. Unfortunately none of them
are solving my problem.

The following information refers to the data I am working with. I have a
company car scheme which I have put into a spreadsheet.
I have a column (K) where the daily mileage is recorded.
Column C which holds takes the engine size of the car being driven.
Column D which holds the two fuel options - petrol or diesel.

Engine Size Petrol Diesel
1400cc or less 9p 9p
1401cc to 2000cc 11p 9p
Over 2000cc 16p 12p

This is the function I have compiled:
=IF(C2<=1400,K2*0.09,IF(D2="Petrol",K2*0.11,IF(C2<=2000,K2*0.09,IF(C2>2000,IF(D2="Diesel",K2*0.12,K2*0.16)))))

This function returns all correct answers unless I select petrol, with more
than 2000cc. It will not return K2*0.16 which is what I am after?

I am aware that < means 'less than', that > means 'more than' and that <=
means less than or equal.
BUT how is the term 'between 1401 and 2000' written (1401 and 2000 need to
be included in the numbers excel would work with)?

Any help is extremely appreciated as I am pulling my hair out over this.

Thank you,

Colin.

 

[Roger Govier]

Hi Colin

I had assumed (wrongly) that your cells contained text values ="Between
1401-2000"
If they just contain numeric's then
=IF(C2=>2000,IF(D2="Petrol",16,12),
IF(AND(C2>=1400,C2<=2000,D2="Petrol"),11,9))*K2


--
Regards

Roger Govier

Hello,

Thank you for your suggestions with this problem. Unfortunately none
of them
are solving my problem.

The following information refers to the data I am working with. I have
a
company car scheme which I have put into a spreadsheet.
I have a column (K) where the daily mileage is recorded.
Column C which holds takes the engine size of the car being driven.
Column D which holds the two fuel options - petrol or diesel.

Engine Size Petrol Diesel
1400cc or less 9p 9p
1401cc to 2000cc 11p 9p
Over 2000cc 16p 12p

This is the function I have compiled:
=IF(C2<=1400,K2*0.09,IF(D2="Petrol",K2*0.11,IF(C2<=2000,K2*0.09,IF(C2>2000,IF(D2="Diesel",K2*0.12,K2*0.16)))))

This function returns all correct answers unless I select petrol, with
more
than 2000cc. It will not return K2*0.16 which is what I am after?

I am aware that < means 'less than', that > means 'more than' and
that <=
means less than or equal.
BUT how is the term 'between 1401 and 2000' written (1401 and 2000
need to
be included in the numbers excel would work with)?

Any help is extremely appreciated as I am pulling my hair out over
this.

Thank you,

Colin.

 

[Guest]

Hi Roger,

A very big thank you for your help.
It's only when you see the answer that it looks so easy.
I've spent hours trying to get this to work.
Cheers,

Colin.

 

[Roger Govier]

Hi Colin

You're very welcome. Thanks for the feedback letting us know that it
worked.