The other day I was setting up a new employee’s computer. I was configuring, VPN, loading bookmarks and macros, and mapping network drives and printers. When I was configuring Excel she looked at me and asked, what’s this program do (referring to Excel).

Well that took me back a bit. In this day of the modern office, I never considered that a new employee would not have any knowledge of spreadsheets.

Now in her defense, they came from a role that did not require computer use (yes these jobs still do exist).

So I broke out my one sentence explanation of what Excel is.

Excel is a program that is used to store, organize, manipulate and analyze data.

I then elaborated briefly….

Excel consists of a grid of cells (an intersection point) formed by columns and rows. For the most part, columns have letters and rows have numbers. A cell can be considered the basic unit for storing data in a worksheet. As there are just of 17 billion cells available for use in a worksheet, each cell has its own name (or reference). So cell C5 would be in column C row 5. Her eyes glazed over…

I continued… Data is store in these cells and Excel allows you to add, subtract, multiply and divide this data. Her eyes lit up a bit, “Like a calculator” she said.

“Exactly” I replied. Excel can be considered a fancy calculator. Most people use Excel to perform mathematical calculations ranging from the simple to complex. Excel also allows you to sort and filter your data for analysis. You can even graph your results.

When I finished setting up her computer, I wished her the best of luck in her now job and recommended that she visit my blog to learn more about Excel.

If you were in my place, how would you have answered her question and what do you think I should have included in my brief description of Excel?

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…

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