Excel Professor

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?