98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot table charts
Pivot table charts
Resolved · Medium Priority · Version 2010
Thu 12 Dec 2013, 17:13Delegate Richard said...
Richard has attended:
Excel Pivot Tables course
Excel Advanced - Formulas & Functions course
Pivot table charts
I am trying to create a line chart from a pivot table which has four columns and 84 rows. Each piece of data in each row represents a certain number of days an item has been in stock. So for instance we may have 28, 30, 35 representing one item sold after 28 days, one sold after 30 days and one after 35 days in stock. Each column represents a particular price band for that item so we should end up with four different lines on the chart. I want the line graph to show number of days in stock on the x axis and the Y axis to show the cumulative % of items at that price band. So using the figures above if we had one item for 28 days, one for 30 days and one for 35 days, I would expect the chart to show a line which is at 33% for 28 days, 66% for 30 days and 100% for 35 days.
It is the cumulative presentation which is causing the problem.
Thanks!
Thu 12 Dec 2013, 17:30
Edited on Thu 12 Dec 2013, 17:30Trainer Gary said...
RE: Pivot table charts
Hi Richard
Thanks for getting in touch. Thinking through your problem if your data is as you've described it it's possible that your data isn't laid out perfectly for a PivotTable.
Rather than try and describe a long-winded process based on an assumption, I've created a book (attached). The Original sheet contains a table on how I *think* you've got the data laid out at present. You will need to convert it to a format as per the Final sheet.
I've then had a go at creating your percentages.
Let me know if I'm near the mark of what you've done. If you want to amend the file to move me closer to your data, feel free to email me the file gary@stl-training.co.uk.
I hope this helps.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
GF Pivot Chart and Convert.xlsxThu 12 Dec 2013, 17:48Delegate Richard said...
RE: Pivot table charts
Thanks Gary,
The layout of the data as you have it is fine however I am trying to show 4 lines on the chart each which ends at 100% as it is the gradient of the curves that we are trying to illustrate.
This if we take the first line using your illustrative data for band "A" we would have 9.78% as the first marker (as you have it) but the second marker would be 22.43% (being 9.78% + 12.65%). The third marker would show 33.31% etc to 100%.
Thanks
Richard
Thu 12 Dec 2013, 23:44
Edited on Thu 12 Dec 2013, 23:44Trainer Gary said...
RE: Pivot table charts
Hi Richard
Thanks for your reply. I've made the change as you'd suggested.
I'm not sure how useful the final chart is but I hope it works with your data!
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
Thu 19 Dec 2013: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Creating a range of monthly payments as textYou could use a formula to create a range of payment ie. payment amount for x% to y% rate with fixed terms and principle. |
