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?<br><br>--<br><br>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.<br>
<br>The best selling item each month over a 6 month period - in one report? That's not easy! <br><br>How is the data stored? Is it a raw list of transactions with each row containing item ID, sale date and sale amount? <br>
<br>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...<br><br>In an empty column you extract the month from the date with =MONTH(Saledate) to get a month number (1=January, 2=February). <br>
Create a filter on the 'month' column and select the month you're interested in.<br>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.<br>
Use the MAX( ) function to find the highest sales value for all items that month.<br>Use the INDEX( ) function to extract the item number corresponding to that maximum sales value.<br>Copy the value, item and month to another part of the spreadsheet and create a chart of the items' sales figures.<br>
<br>That's pretty unreasonable. <br><br>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.<br>
<br>Am I overthinking it? <br>Have I missed the bleedin' obvious?<br><br>My head hurts.<br clear="all"><br><br>-- <br>Mark Kelly<br>Manager of ICT, Reporting, IT Learning Area<br>McKinnon Secondary College<br>McKinnon Rd McKinnon 3204, Victoria, Australia<br>
Direct line / Voicemail: +613 8520 9085, Fax +613 9578 9253<br><a href="mailto:kel@mckinnonsc.vic.edu.au" target="_blank">kel@mckinnonsc.vic.edu.au</a><br>VCE IT Lecture Notes: <a href="http://vceit.com" target="_blank">http://vceit.com</a><br>
Moderator: <a href="http://www.edulists.com.au/" target="_blank">IT Applications Edulist</a><br><br><font>Want a good time? Call 0112358. Ask for Mr </font>Fibonacci.<br>