Help : Formula for different condition

[KH - Pat WONG]

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!

[#2]

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).

[#3]

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.