[Year 12 IT Apps] Exam question B7

[Mann, Warrick D]

Hi Mark,

I would actually agree with you, this would actually be better suited to a database.

One way could be to use 2 different sheets. 1 summary sheet, and then a summary of that sheet?? Still quite messy.

Warrick

From: itapps-bounces at edulists.com.au [mailto:itapps-bounces at edulists.com.au] On Behalf Of Mark KELLY
Sent: Tuesday, 15 November 2011 1:33 PM
To: Year 12 IT Applications Teachers' Mailing List
Subject: [Year 12 IT Apps] Exam question B7

A new thread for a new question - I was strangling in that massive thread on all sorts of different questions. Let's use different threads for different questions, eh?

--

Anyway - Question B7, the 8 mark beast - the best-selling item for each month over 6 months. It's got me knocking my head on the furniture.

The best selling item each month over a 6 month period - in one report? That's not easy!

How is the data stored? Is it a raw list of transactions with each row containing item ID, sale date and sale amount?

You'd need to somehow group all sales by month, calculate a sales total for each item in that month, then plot that item's sales in a chart. But it's not easy to do...

In an empty column you extract the month from the date with =MONTH(Saledate) to get a month number (1=January, 2=February).
Create a filter on the 'month' column and select the month you're interested in.
Use =SUMIF(items,1,salesamounts) to get the sales total for item 1, a similar formula for item 2 etc. That would give the total sales for each item in each month.
Use the MAX( ) function to find the highest sales value for all items that month.
Use the INDEX( ) function to extract the item number corresponding to that maximum sales value.
Copy the value, item and month to another part of the spreadsheet and create a chart of the items' sales figures.

That's pretty unreasonable.

Perhaps they want a pivot table? Do they want a line graph showing the monthly sales of each item on separate lines? That would still require grouping the monthly sales figures.

Am I overthinking it?
Have I missed the bleedin' obvious?

My head hurts.


--
Mark Kelly
Manager of ICT, Reporting, IT Learning Area
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: +613 8520 9085, Fax +613 9578 9253
kel at mckinnonsc.vic.edu.au<mailto:kel at mckinnonsc.vic.edu.au>
VCE IT Lecture Notes: http://vceit.com
Moderator: IT Applications Edulist<http://www.edulists.com.au/>

Want a good time? Call 0112358. Ask for Mr Fibonacci.

Important - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Early Childhood Development.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20111115/62fa539e/attachment.html