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

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

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

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))

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))

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?

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.

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?