Solved

excel spreadsheet, graph

Posted on 2010-08-28
8
553 Views
Last Modified: 2012-05-10
Hi

I have a monthly sales and cancellation summary spreadsheet, please see attached work sheet august(2).. I would like to summarise the number and EUR of Saels per caregory and total and also compare it to cancellations visually and in a table. Additionally I also would need to have a summary per week, I guess in a different table and graph.

Nothing I have tried has got me close to a solution. Any advise would be very much appreciated.

Many thanks

Frederic
Finance-Break-down-Analysis-pppp.xlsx
0
Comment
Question by:Frederic74
  • 4
  • 2
  • 2
8 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 33549413
Frederic74,This would probably be easiest if you reorganize your data:1) Instead of having separate worksheets for each month, have a single worksheet.  Excel 207 and later give you one million rows, so use them :)2) Instead of having separate regions for sales and cancellations, put them all in the same range, with a column indicating sale or cancellationOnce you do this, getting the summaries/graphs for your data is a simple matter of creating a PivotTable/PivotChart.Patrick
0
 

Author Closing Comment

by:Frederic74
ID: 33549593
Thank you

I have  followed your advise and have put all in one table.

I guess a pivot table would mean I do not need my summarising formulas on the top my spreadsheet?

I'm new to pivot tables do you know a good example introducing it to me ideally similar to my spreadsheet?
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33549613
Attaced example shows how you could visually show number & sales + cancellation.
As the numbers are much lower, they need to be plotted on a secondary axis.

Regarding your weekly report, I think Patrick has a very valid suggestion overall.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Expert Comment

by:calacuccia
ID: 33549617
Forgot to attach the workbook
Xl0000011.xls
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33549621
Pivot Table won't help you for the chart though :-)

BTW: your charts are much too small to get a nice visualisation.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33549637
>>Pivot Table won't help you for the chart though :-)I wouldn't say that: if the PT powers a PivotChart, then it should faciltate the charting: one could see new sales vs cancellations, and depending on how the source data is reconstructed, you could use either a calculated field or a calculated item to see the variance of sales over cancellations...
0
 
LVL 17

Expert Comment

by:calacuccia
ID: 33549650
Could be Patrick, but he will need secondary axis for his amount as it has a complete different dimension.
0
 

Author Comment

by:Frederic74
ID: 33549970
I have added the cancellattions to the sales, please see attached.  Does this make it easier?
Finance-Break-down-Analysis-pppp.xlsx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
find a word in a cell 3 39
Increment default InPutBox value 14 26
what program/tool is used to create charts like this? 4 30
Delete row if does not start with 0 43 37
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question