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" wrote:

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


"Colin" wrote in message
...
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(C22000,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" wrote:

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


--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Colin, for the range of values you have, the following should
work
=IF(C2="2000",IF(D2="Petrol",16,12),IF(AND(C2="Be tween
1401-2000",D2="Petrol"),11,9))

--
Regards

Roger Govier


"Colin" wrote in message
...
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 requi
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
2000 0.16 0.12

Many thanks,

Colin.


"Toppers" wrote:

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(?)
2000 0..16 0.12

"q3pd" wrote:

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(C22000,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.
--
Q3PD