I am having difficulty creating a formula. My list has clients who has been
with a company for x number of months. Depending on how long they have been
clients, results how much discount they receive.

eg:

A B C D E
FIRST LAST MTHS MTHS PERCENTAGE
REGD
Joe Blog 19 5 5%
10 10%
15 12%
20 15%

Can anyone help me?

Many thanks,
Marty

try:

A1=Months

in B1 (formatted as %):

=LOOKUP(A1,{0,5,10,15,20},{0,0.05,0.1,0.12,0.15})

<5 months==0
5-9 months==5%
etc

HTH

"mda19652" wrote:

I am having difficulty creating a formula. My list has clients who has been
with a company for x number of months. Depending on how long they have been
clients, results how much discount they receive.

eg:

A B C D E
FIRST LAST MTHS MTHS PERCENTAGE
REGD
Joe Blog 19 5 5%
10 10%
15 12%
20 15%

Can anyone help me?

Many thanks,
Marty

One way is to use vlookup ..

Assuming col C contains the number of months that cust have been your
clients (data in C2 down), then you could put in say, D2:
=VLOOKUP(C2,{0,0;5,0.05;10,0.1;15,0.12;20,0.15},2)
Format D2 as percentage, copy down

Col D will return the relevant discount percentages
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mda19652" wrote:
I am having difficulty creating a formula. My list has clients who has been
with a company for x number of months. Depending on how long they have been
clients, results how much discount they receive.

eg:

A B C D E
FIRST LAST MTHS MTHS PERCENTAGE
REGD
Joe Blog 19 5 5%
10 10%
15 12%
20 15%

Can anyone help me?

Many thanks,
Marty

Looking at your data table below you seem to be starting from the wrong
place. For example yo have a column 'Months Registered' that contains a
number of months. If you approach it that way it will be wrong next month.

What you need is 2 tables:-

Customer - Date registered - Months registered

Bloggs 1/6/2007 =MONTH(NOW())-MONTH(b2)

Month regsitered now updates automatically.

In a second table you need:-

Months - Discount
5 5%
10 10%
15 12%
20 15%

A simple Vlookup now calculates the discount per Customer

=VLOOKUP(C2,A1:B9,2,TRUE)


Where c2 is the months registered.
a1:b9 is the discount table
True makes it return the nearest value if there isn't an exact match.


Mike
"mda19652" wrote:

I am having difficulty creating a formula. My list has clients who has been
with a company for x number of months. Depending on how long they have been
clients, results how much discount they receive.

eg:

A B C D E
FIRST LAST MTHS MTHS PERCENTAGE
REGD
Joe Blog 19 5 5%
10 10%
15 12%
20 15%

Can anyone help me?

Many thanks,
Marty