[Year 12 IT Apps] Exam question B7

[Mark KELLY]

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
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20111115/27a9b4f7/attachment.html