Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help with if,and,else statement
Good morning all!
I need help creating an equation that will do the following. Here is a line out of a contract that our company has just won. I have a spreadsheet currently of the current rate, and also the # of months that the machine has been in place for. However, I'm not sure how to create a statement based on the following: Curently placed units in service less than 36 months: 30% discount from current rate Units in service from 36-48 months, 20% discount. Units in service greater than 48 months, 10% discount. Any help would be greatly appreciated! Gary |
#2
|
|||
|
|||
One way:
=IF(A1<36,30%,IF(A1<=48,20%, 10%) where A1 contains the number of months. In article , "Dexter" wrote: Good morning all! I need help creating an equation that will do the following. Here is a line out of a contract that our company has just won. I have a spreadsheet currently of the current rate, and also the # of months that the machine has been in place for. However, I'm not sure how to create a statement based on the following: Curently placed units in service less than 36 months: 30% discount from current rate Units in service from 36-48 months, 20% discount. Units in service greater than 48 months, 10% discount. Any help would be greatly appreciated! Gary |
#3
|
|||
|
|||
Hi Dexter
with number of months in A1 and current rate in B1 one option would be =IF(A1<36,B1*0.7,IF(A1<=48,B1*0.8,B1*0.9)) Cheers JulieD "Dexter" wrote in message ... Good morning all! I need help creating an equation that will do the following. Here is a line out of a contract that our company has just won. I have a spreadsheet currently of the current rate, and also the # of months that the machine has been in place for. However, I'm not sure how to create a statement based on the following: Curently placed units in service less than 36 months: 30% discount from current rate Units in service from 36-48 months, 20% discount. Units in service greater than 48 months, 10% discount. Any help would be greatly appreciated! Gary |
#4
|
|||
|
|||
just a bit shorter:
=B1*IF(A1<36,0.7,IF(A1<=48,0.8,0.9)) In article , "JulieD" wrote: =IF(A1<36,B1*0.7,IF(A1<=48,B1*0.8,B1*0.9)) |
#5
|
|||
|
|||
Thank you! I understand what you are doing now. Only one question though.
Shouldn't it be A1= 48? Rather than <= 48? Dex "JE McGimpsey" wrote in message ... just a bit shorter: =B1*IF(A1<36,0.7,IF(A1<=48,0.8,0.9)) In article , "JulieD" wrote: =IF(A1<36,B1*0.7,IF(A1<=48,B1*0.8,B1*0.9)) |
#6
|
|||
|
|||
Not according to your specification:
Units in service from 36-48 months, 20% discount. Units in service greater than 48 months, 10% discount. So if A1<=48, the result should be 0.8. only if A148 (i.e., A1<=48 is false) should the result be 0.9. In article , "Dexter" wrote: Thank you! I understand what you are doing now. Only one question though. Shouldn't it be A1= 48? Rather than <= 48? |
#7
|
|||
|
|||
Dexter wrote:
=B1*IF(A1<36,0.7,IF(A1<=48,0.8,0.9)) Thank you! I understand what you are doing now. Only one question though. Shouldn't it be A1= 48? Rather than <= 48? No :) if(expr, TRUE, FALSE).. if A1<=48 same as NOT (A148) then it's B1 * 0.8 (for the 20%) else then it's B1 * 0.9 (for the 10%) Just another way of doing the math. |
#8
|
|||
|
|||
Ahhh, I see the light. Just another way of looking at things. I forgot that
if the "IF" calculation is performed, then it won't use that again, so I was confused by the fact that you were saying less than 48 months, since there are 2 different prices that are less than 48 months. I now understand. Thanks for your help this had made my life and job a lot easier! Have a good day. Dex "JE McGimpsey" wrote in message ... Not according to your specification: Units in service from 36-48 months, 20% discount. Units in service greater than 48 months, 10% discount. So if A1<=48, the result should be 0.8. only if A148 (i.e., A1<=48 is false) should the result be 0.9. In article , "Dexter" wrote: Thank you! I understand what you are doing now. Only one question though. Shouldn't it be A1= 48? Rather than <= 48? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement with Average Function results in #Value! | Excel Discussion (Misc queries) | |||
7+ nested if statement? | Excel Worksheet Functions | |||
Statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
If statement needed | Excel Worksheet Functions |