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…

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?