Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
=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) "q3pd" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
I meant the conversion factors for K2 but anyway Bob's formula does the trick.
"Colin" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with nested function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Function | Excel Discussion (Misc queries) | |||
can you nested sum and round function within if function? | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Nested Function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |