Jun 22 2018
11:07 PM
- last edited on
Jul 31 2018
08:32 AM
by
TechCommunityAP
Jun 22 2018
11:07 PM
- last edited on
Jul 31 2018
08:32 AM
by
TechCommunityAP
Greeting, first time to visit this forum, I would like to know whether excel has certain formula to resolve the following problem
My company has customer loyalty program, and each dollar spend will have 1 point, this member point can convert into cash to use in our retail store.
The converting scale is in progressive rate as attached. 1000pt convert to $20, 2000pt convert to $60, 3000pt convert to $100
So if customer A have 4000 pt, then she will potentially be converted into (3000pt) $100+ (1000pt) $20 , the potential liability for my company is $120.
since we got quite a lot of customer with different pt balance, it's very difficult for me to calculate their potential liability.
Hope someone can help.
THanks!
Jun 24 2018 01:26 AM - edited Jun 24 2018 01:27 AM
See attachement.
In this file, you insert the ID and the spent dollars in column A and B.
In column C D and E it counts how many of the biggest rate is applyable to the points of the customer, then calculates fidelity points in column F.
It calculates the integer solution of total/3000, then subtract that 3000 points (for each) from the original total and calculates the integer newertotal/2000; than do the same with newesttotal/1000. So 4000 is 1 time 3000 (100) and 1 time 1000 (20) [that is obv the same of 2 times 200 (60+60)]; 6000 is 2 times 3000 (200) that is better than 3 times 2000 (180) or 6 times 1000 (120). Fair enough.
I added a G column in which it shows how many points are "left", for the next goal.
You should hide columns C D E (colored columns).
Jun 24 2018 07:14 AM
As variant that could be done with Power Query
- first create a table for the conversion of points with 1000pt gradation and till possibly max amount of points (let say 50000pts). It could be generated by formulas, but that's 5 minutes job to generate such table manually, it's static and never be recalculated;
- query that table as connection;
- query all members with points, group by members to sum points for every of them and lookup conversion table from firts query. How to lookup is in details here https://www.powerquery.training/portfolio/vlookup-true-in-power-query/
- we don't need the list of members with conversion, just sum and return total company liability as one number
And in attached.