I have an Excel spreadsheet that is supposed to collect data from a point of sale system export file and then analyze it.
The data has been stripped out of a text report, so each record has a transaction number, customer information, date and item bought. There can be more than one item bought in a transaction. Each transaction has a single cashier and a single salesperson responsible.
Using these individual records, we want to see how individual salespeople are doing on their assisted sales compared to each other and the store as a whole. I would like the spreadsheet to count total transactions (done), total sales (done), store Average Dollar Sale (done) and store Units per Transaction.
Then we need it to count units per salesperson, total sales per salesperson, units per transaction per salesperson and average dollar sale per salesperson. The spreadsheet has a series of array formulas that appeared to work when used on small datasets. When applied to larger data, the first several rows of the array calculations appear to be cumulating several salespeople worth of data. The error is likely in the array formula.
Please correct the formulas so that a finished table can be generated and shared with management and individual salespeople.
It appears that the wrong data was attached. It should have been a workbook not a comma delimited file
## Deliverables
This must be in Excel as it will be shared with people who only have Excel and can manage to paste some raw data into a screen. Please do not suggest a desktop application, database format or anything else.
Excel 2007 and up. Can't be limited to Excel 2010. Sample workbook is attached. It has instructions annoted, and sample data. We used a filter on a duplicate copy of the data to test it. The data is real and represents 1 week of actual sales by actual cashiers and actual salespeople.