I have a system which based on what a user spent within a day they get bonus cash back. In essence for each $500 spent they get a $25 in a bonus wallet. If the amount is simple for example $1000 then its easy and we give $50. The problem arises if the user spent on visit A $400 whereas they get nothing. but on visit B they spent $600 which between those 2 visits they spent $1000 and considering that they did not get a bonus on visit A they should get $50 (2*25) on visit B. Keep in mind that the user can spend what ever they earn on their bonus wallet whenever they want within the day. I have attached my screenshot where I would like to find a way to programmatically do this calculation regarding the bonus. Any help is much appreciated.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Bonus logic question
- Thread starter drbro
- Start date
I think you would need macro code to do what you are asking. That, or at least a 'helper' additional cell to keep track of $ spent thus far that don't qualify for the 'bonus'.
On a side note, if those transactions are made on a card, it is probably stolen, who spends that much money in one day?
You also didn't mention if the customer uses their 'bonus' money, is that considered $ spent? Kind of like if you get paid for a holiday and those (free) hours put you into overtime, most companies will not pay you for the overtime that those (free) hours caused.
You also have some miscalculations in your picture. For example first transaction is @ 10:00 AM, second transaction is listed @ 12:00 AM <-- that should be PM if what you have are the transactions for one day. The next incidence I saw was the 'Total bonus in bonus wallet before' column is not being adjusted when the customer spends money from that wallet. The 'Total bonus in bonus wallet after' seems correct though.
On a side note, if those transactions are made on a card, it is probably stolen, who spends that much money in one day?
You also didn't mention if the customer uses their 'bonus' money, is that considered $ spent? Kind of like if you get paid for a holiday and those (free) hours put you into overtime, most companies will not pay you for the overtime that those (free) hours caused.
You also have some miscalculations in your picture. For example first transaction is @ 10:00 AM, second transaction is listed @ 12:00 AM <-- that should be PM if what you have are the transactions for one day. The next incidence I saw was the 'Total bonus in bonus wallet before' column is not being adjusted when the customer spends money from that wallet. The 'Total bonus in bonus wallet after' seems correct though.
Upvote
0
This should solve everything I mentioned, leaving just the 'bonus' column to be calculated via formula or macro:
| drbro1.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | time | Transaction Amount | Total $ Spent Today | bonus earned | Total bonus in wallet before | Bonus spent | Total bonus remaining | ||
| 2 | 10:00 AM | 500 | 500 | 25 | 25 | 0 | 50 | ||
| 3 | 12:00 PM | 500 | 1000 | 25 | 50 | 0 | 75 | ||
| 4 | 1:00 PM | 1000 | 2000 | 50 | 75 | 100 | 25 | ||
| 5 | 2:00 PM | 500 | 2500 | 25 | 25 | 0 | 50 | ||
| 6 | 5:00 PM | 2000 | 4500 | 100 | 50 | 0 | 150 | ||
| 7 | 6:00 PM | 400 | 4900 | 0 | 150 | 0 | 150 | ||
| 8 | 7:00 PM | 600 | 5500 | 50 | 150 | 175 | 25 | ||
| 9 | 8:00 PM | 1000 | 6500 | 50 | 25 | 0 | 75 | ||
| 10 | 9:00 PM | 1200 | 7700 | 50 | 75 | 0 | 125 | ||
| 11 | 10:00 PM | 800 | 8500 | 50 | 125 | 125 | 50 | ||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | = B2 |
| C3:C11 | C3 | = C2 + B3 |
| E3:E11 | E3 | = G2 |
| G2:G11 | G2 | = D2 + E2 - F2 |
Upvote
0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 90,637
- Office Version
- 365
- Platform
- Windows
Hi & welcome to MrExcel.
Is this what you want?
Is this what you want?
| +Fluff 1.xlsm | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | time | Money Spent | Total money spent | Bonus | Total bonus in wallet before | Bonus spent | Total bonus remaining | ||
| 2 | 10:00:00 | 500 | 500 | 25 | 25 | 0 | 25 | ||
| 3 | 12:00:00 | 500 | 1000 | 25 | 50 | 0 | 50 | ||
| 4 | 13:00:00 | 1000 | 2000 | 50 | 100 | 100 | 0 | ||
| 5 | 14:00:00 | 500 | 2500 | 25 | 125 | 0 | 25 | ||
| 6 | 17:00:00 | 2000 | 4500 | 100 | 225 | 0 | 125 | ||
| 7 | 18:00:00 | 400 | 4900 | 0 | 225 | 0 | 125 | ||
| 8 | 19:00:00 | 600 | 5500 | 50 | 275 | 175 | 0 | ||
| 9 | 20:00:00 | 1000 | 6500 | 50 | 325 | 0 | 50 | ||
| 10 | 21:00:00 | 1200 | 7700 | 50 | 375 | 0 | 100 | ||
| 11 | 22:00:00 | 800 | 8500 | 50 | 425 | 125 | 25 | ||
| 12 | |||||||||
Main | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C11,E2:E11 | C2 | =SUM(B$2:B2) |
| D2:D11 | D2 | =INT(C2/500)*25-N(SUM(D$1:D1)) |
| G2:G11 | G2 | =E2-SUM(F$2:F2) |
Upvote
0
Solution
Hi, apologies as I was a bit sick. I am more interested in the bonus calculation. That is column D and especially looking at column B and row 7 and 8. The issue here is that every $500 we give $25 but if on one session the user spends 400 they get nothing but on the next session they spent 600 which is total 1000 - we want to give them 2x$25. We want an intelligent way to calculate this. Right now is quite simplistic as it only looks on the amount per session.
Upvote
0
Similar threads
- Solved
