Use MicrosoftÂ® Excel Differently to Gain a Productive Edge
Issue - Sep 2008
“Your boss sends you an Excel file, containing the sales transactions of several products sold in the last quarter. In the file, column A shows the names of products sold by your company. In column B, it shows the customers who bought the product in that quarter. In column C, it lists down the week the customer bought a particular product. ”
Using the data given, you are required to tabulate the sales for each week by product as shown:
For someone who knows the basic of Excel, the only to get this done is to eyeball all the records and manually sum up the figures and enter them into the table.
If there is only one product, then a simple sumif formula which we teach in part I of our course would solve the problems in a few seconds. But with a listing of more than 10 products, this would take you much longer, considering that sorting is required and the numbers to sum up have to be are accurately identified without human error. But what if the listing has more than 50 products and span over a number of weeks? Just think about the time you would have to spend on tabulating the numbers.
But if you just know of an extra formula to join cells together, absolute and relative referencing, plus the sumif formula you already know, solving such a problem would probably take only slight over a minute. All we have to do is to have a new column to join the product code and week number together. Then by using sumif and the concatenate formula, we would be able to set up a formula that can be used for the entire table. This formula requires no eyeballing on the spreadsheet and yet produces the numbers or results with 100% accuracy.
If you want to save time by doing your Excel tasks right, sign up for our Sales Performance Analytics with Excel course now!
If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.
If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and
"Excel Secrets for Highly Effective Marketers"
Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.