Today I want to address a common occurrence in my job. I am often asked to calculate an instant discount based on the total amount of a cash register receipt. For instance, a customer can instantly save $20 for every $100 spent on a cash register receipt.
Now that does not seem so bad. But the receipt already shows the discounted amount. In this example you can see the for cash register receipt 11111, there are 6 items purchased totaling $241.01.
| Receipt # | ITM_NBR | Item Amount Paid At Cash Register | Total Amount Of Receipt |
| 11111 | 1 | 48.09 | 241.01 |
| 11111 | 2 | 14.79 | 241.01 |
| 11111 | 3 | 10.00 | 241.01 |
| 11111 | 4 | 0.78 | 241.01 |
| 11111 | 5 | 9.35 | 241.01 |
| 11111 | 6 | 158.00 | 241.01 |
My manager wants to know what the original amount of the Total Amount Of Receipt would have been before the discount.
On a one off, it’s not too hard to determine and with a bit of patience, I can manually generate the result ($40). However this does not work when I have 100,000 records.
To automate this I need a few key bits of information. First I need to know what the savings is. In this example $20 for every $100 spent. As this is an instant savings, A $100 purchase would ring up as $80 (100 – 20). A $200 purchase would ring up as $160 (200 – 40). So when I look at the above example, this customer got a $40 instant savings 241.01 + 40 = 281.01(original total before the $40 instant discount). There is no sliding scale so if the customer does not hit the next threshold; they only qualify for the lower discount.
The next bit of information needed to automatically calculate this is the maximum Cash Register Receipt total. This sets my high Tier that I will use in my matrix. A quick check of my Total Amount Of Receipt column shows that the highest valued ticket that I have is $1539.99. My low number is .01 (the bare minimum of a purchase).
So what I need to do is build up my matrix of Max purchase price and Low purchase price and how much of a discount should be provided. Remember that these values have the discount already applied…
So let’s start with the bottom and work our way up.
If a customer’s receipt shows $79.99 or less then they have not spent enough to qualify for an instant $20 discount (too bad they didn’t purchase some gum at checkout). If the receipt shows $80 to $179.99 then they have earned the instant $20 savings. Think about that for a minute. If the receipt shows $179.99, when you add the discount of $20, you get 199.99. The promo is save $20 for every $100 spent. They did not spend $200 so they only qualify for the $20 discount (again they should have purchased some gum). If their receipt shows $80, then they got the discount of $20 (80 + 20 = 100).
| High Ticket Price | Low Ticket Price | Discount Amount |
| 259.99 | 180.00 | 40 |
| 179.99 | 100.00 | 20 |
| 99.99 | 80.00 | 20 |
| 79.99 | 0.01 | - |
Now in my matrix, the high tier is always going to be one cent less than the next qualifying Discount amount threshold. No overlap can occur so the low tier on the next discount is always .01 greater than the high tier on the lower threshold.
When I finish my matrix it looks like the following and I place it on a tab labeled Discount…
|
| A | B | C |
| 1 | High Ticket Price | Low Ticket Price | Discount Amount |
| 2 | 1,539.99 | 1,460.00 | 360 |
| 3 | 1,459.99 | 1,380.00 | 340 |
| 4 | 1,379.99 | 1,300.00 | 320 |
| 5 | 1,299.99 | 1,220.00 | 300 |
| 6 | 1,219.99 | 1,140.00 | 280 |
| 7 | 1,139.99 | 1,060.00 | 260 |
| 8 | 1,059.99 | 980.00 | 240 |
| 9 | 979.99 | 900.00 | 220 |
| 10 | 899.99 | 820.00 | 200 |
| 11 | 819.99 | 740.00 | 180 |
| 12 | 739.99 | 660.00 | 160 |
| 13 | 659.99 | 580.00 | 140 |
| 14 | 579.99 | 500.00 | 120 |
| 15 | 499.99 | 420.00 | 100 |
| 16 | 419.99 | 340.00 | 80 |
| 17 | 339.99 | 260.00 | 60 |
| 18 | 259.99 | 180.00 | 40 |
| 19 | 179.99 | 100.00 | 20 |
| 20 | 99.99 | 80.00 | 20 |
| 21 | 79.99 | 0.01 | - |
My receipt information is on a separate tab
| Receipt # | ITM_NBR | Item Amount Paid At Cash Register | Total Amount Of Receipt | Discount Applied |
| 11111 | 1 | 48.09 | 241.01 | 40.00 |
The formula I use in the Discount Applied column is…
=VLOOKUP(INDEX(Discount!A:A,MATCH(D2,Discount!A:A,-1)),Discount!A:C,3,FALSE)
Now you are saying, not the dreaded Index / Match function that causes me so much grief….. Sorry but yes, it’s the perfect tool for this.
Let’s break the formula down piece by piece to help you better understand.
=MATCH(D2,Discount!A:A,-1) would return 18
This formula is taking the Total Amount Of Receipt in cell D2 (241.01) and asking Excel to return the row number that has a value that is just above the value from our matrix on the Discount Tab.
| Row | High Ticket Price | Low Ticket Price | Discount Amount |
| 16 | 419.99 | 340.00 | 80 |
| 17 | 339.99 | 260.00 | 60 |
| 18 | 259.99 | 180.00 | 40 |
| 19 | 179.99 | 100.00 | 20 |
So what does the INDEX portion of this formula do?
=INDEX(Discount!A:A,MATCH(D3,Discount!A:A,-1))
Well it returns the value in Column A of row 18 in my matrix (259.99). I can use that number to do a Vlookup and return the discount amount to apply.
=VLOOKUP(INDEX(Discount!A:A,MATCH(D2,Discount!A:A,-1)),Discount!A:C,3,FALSE)
| Receipt # | ITM_NBR | Item Amount Paid At Cash Register | Total Amount Of Receipt | Discount Applied |
| 11111 | 1 | 48.09 | 241.01 | 40.00 |
| 11111 | 2 | 14.79 | 241.01 | 40.00 |
| 11111 | 3 | 10.00 | 241.01 | 40.00 |
| 11111 | 4 | 0.78 | 241.01 | 40.00 |
| 11111 | 5 | 9.35 | 241.01 | 40.00 |
| 11111 | 6 | 158.00 | 241.01 | 40.00 |
| 22222 | 7 | 123.52 | 313.04 | 60.00 |
| 22222 | 8 | 176.00 | 313.04 | 60.00 |
| 22222 | 9 | 1.58 | 313.04 | 60.00 |
| 22222 | 10 | 11.94 | 313.04 | 60.00 |
| 33333 | 11 | 93.90 | 97.47 | 20.00 |
| 33333 | 12 | 3.57 | 97.47 | 20.00 |
| 44444 | 13 | 28.51 | 108.99 | 20.00 |
| 44444 | 14 | 3.33 | 108.99 | 20.00 |
| 44444 | 15 | 4.17 | 108.99 | 20.00 |
| 44444 | 16 | 61.96 | 108.99 | 20.00 |
| 44444 | 17 | 5.47 | 108.99 | 20.00 |
| 44444 | 18 | 1.24 | 108.99 | 20.00 |
| 44444 | 19 | 3.75 | 108.99 | 20.00 |
| 44444 | 20 | 0.56 | 108.99 | 20.00 |
| 55555 | 21 | 10.56 | 97.95 | 20.00 |
| 55555 | 22 | 11.03 | 97.95 | 20.00 |
| 55555 | 23 | 76.36 | 97.95 | 20.00 |
Well If I know the discount applied to a ticket, then I can calculate the original ticket amount with simple addition. I can also figure out how much discount was applied to each item by dividing the Total Amount of Receipt by Item Amount Paid At Cash Register. Then multiply the discount by this percent.
So with some organization of my data upfront and utilizing three functions, Vlookup, Index and Match I can back into my original ticket price (as well as my original Item price).
How would you calculate the tier discount that I showed today?
