Compute potential Sales of a retail outlet


The objective is to assist a Store Manager with computing potential sales across different products and colours.  To start with let’s assume two datasets: 1. Customer-Colour dataset – a two column table which lists down the colour preference of each customer; and 2. Colour-Product-Price dataset – a three column table which lists down the multiple […]

Read More →

Ignore errors while adding non contiguous cells of a range


In range E5:E13, assume the entries are 1 2 #DIV/0! 4 5 6 #N/A 8 9 In cell I9, one may want to sum up the figures from cells E5,E7,E11 and E13. If one supplies the individual cell references to the SUM function, then the result will be a #DIV/0! error because the SUM function […]

Read More →

Show granular as well as total figures on the Summary sheet


Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions.  Each Region has its own worksheet.  There are three headings on each sheet – Particulars, 2009 and 2010.  While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]

Read More →

Summarise data from different cells of multiple worksheets


In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week.  Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can […]

Read More →

Valuing Closing Stock using FIFO method of Accounting


Assume a stock purchase sheet which details the daily stock purchase of various Raw material at various per unit prices.  On another sheet, the closing stock of each Raw material type is entered.  In issuing Raw Material, one may want to follow the First In First Out (FIFO) method accounting which assumes the Raw material issued […]

Read More →

Summarise data from multiple sheets with multiple conditions


Assume there are three worksheets, one each for 2008, 2009 and 2010.  On each sheet there are two columns for Account code and Account description.  Thereafter there are 12 columns (one for each month).  Values inside the matrix represent amount spent on a specific item in a specific month. On the P&L worksheet, a user will provide the […]

Read More →

Summarise data from multiple sheets with one condition


Assume there are ten worksheet tabs – one for each retail store.  Each worksheet tab has sale data of different brands across 12 months – brands are arranged in rows and months are arranged in columns.  The structure of all worksheets tabs is the same.  Furthermore, each store is managed in any of the two retail […]

Read More →