Assigning scores to a date range

[Koen]

Dear reader

I am trying to set up a function that identifies if the date or value of a
certain customer is in a pre-defined target range to give it a score from 1
to 5. For example, if last time the customer bought something before 01/01/05
he will be assigned a score 1, did he buy something before 01/03/05 (but
after 01/01/05) he will be assigned a score 2 etc. Do you know the function
for that in Excel?

Date range=

<01/01/2005

= 01/01/2005 and <01/03/2005
= 01/03/2005 and <01/05/2005
= 01/05/2005 and <01/07/2005
= 01/07/2005 and <01/09/2005

I was also thinking about having this model updated regularly so built in a
function like: if the customer bought track(s) in the last month, assign
score 1. This way the model is dynamic.

Thanks,
Koen

 

[Stefi]

Set up this table in sheet Daterange
A B
1/1/1900 1 lowest Excel date
1/1/2005 2
1/3/2005 3
1/5/2005 4
1/7/2005 5

In another sheet
A1: date of last purchase
A2-...: purchase dates
B1: Score
B2: =VLOOKUP(A2,Daterange!$A$1:$B$5,2) and fill down as necessary!

Regards,
Stefi


„Koen†ezt írta: