Rankings

[mitch_auspen]

Hello,

I want to do a few simple (yet elusive) calculations with my excel spreadsheet.

I am creating a spreadsheet with contact info, and I want to be able to use excel to generate a ranking system based on the data so that it automatically sorts my lists into varying segments. More on this later.

So my current spreadsheet consists of data such as the customer, and the amounts they spent each month, broken down into 12 monthly columns.

From there, I want excel to assign a number value ranking each customer based on how recently they purchased. So say a 3 for sales in the last 3 months, a 2 in the last 12, and a 1 if it's been 12 months or more.

I also want excel to work out the average sale amount (total dollars spent on purchases in last three years/total number of purchases in past three years) and also assign a value again depending on if the average is below 500, 500-1000, and above 1000.

At the end, I want Excel to sort this data but it's corresponding number rankings (so 3,1 etc.) so I can segment my lists.

Furthermore, I want all of this to be dynamic, so as I add more data in, the generated rankings and sort update to fit this trend.

Can anyone help me with the formulas for these please?

It would be much appreciated

[manohoo]

All of what you are asking for is doable in Excel, but I would use a database since it's much more suitable to your needs.

Anyway, if you still want to use Excel just post a file with data and I will give you a hand.

[dangelor]

A possible start...

[mitch_auspen]

Thanks dangelor, however what i am asking for is a bit more complex. Say for example with the recency of sales. I want excel to search 12 columns (each a month of the year and check for data in each. Then I want it to generate a number ranking based on the sales recency. So if the sale was in the previous three columns, assign a 3. In the previous 6, (as in 6 months), but not in the last 3, then assign a 2. And if they have had a sale in the previous 12, but not in the previous 3 then assign a 1. That way I can group all of the 1s together and so on. So I can segment my lead lists.

Can anyone help me with data this specific?

[dangelor]

Could you upload an example?

[mitch_auspen]

Dangelor, here is an example attached.

The numbers at the end are based on a few conditions.

Recency is determined by how recently the customer purchased. They are given a 1 if the purachse was in the last 12 months, a 2 if in the last 6 months, and a 3 if the purchase was in the last 3 months.

Frequency is how often they buy. Again, a 1 is awarded if they made one or less sale within 12 months. A 2 is awarded if they made less than 3 sales, and a 3 is awarded if there were more then 3 purchases.

Monetary is determined by the average purchase. So the total sale amount of that customer, averaged across the number of purchases they made. If there average is below $100 I want excel to generate a 1. If it is above $100 but below $500, a 2, and $500 and above a 3.

Overall, i want excel to generate these ranking numbers, and to update itself as the data changes over time. I want to be able to see all of my 333, my 323, 233 etc. because then i can quickly see what is lacking in a customer.

For example: a customer who frequently buys, and recently purchased, but who has a low average sale would be a 331. Then I can market to that customer not to buy more frequently, but to increase their monetary output, through specials, volume discounts etc. That way I can more effectively market.

And given i have a 1000+ customers already to market to, updating this by hand would take many, many long arduous hours

[dangelor]

These formulas seem to work...

Recency
=IF(SUM(L3:N3)>0,3,IF(SUM(I3:N3)>0,2,1))

Frequency
=IF(COUNTIF(C3:N3,0)<=9,3,IF(COUNTIF(C3:N3,0)<=10,2,1))

Monetary
=IF(O3>=500,3,IF(O3>=100,2,1))

[mitch_auspen]

Thanks Dangelor, that is perfect!