Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Assigning scores to a date range
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 |
#2
|
|||
|
|||
Assigning scores to a date range
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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
Date Range and calculation | Excel Worksheet Functions | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
Count cells based on date range in another column | New Users to Excel | |||
adding occurrences for date range | Excel Discussion (Misc queries) |