Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 7
There are 1 users currently browsing forums.
|
 |
06-30-2009, 01:33 PM
|
|
Registered User
|
|
Join Date: 05 Aug 2006
Posts: 55
|
|
|
Need PIE Slices as percent of entire PIE
Please Register to Remove these Ads
Fellow Forum Members,
Attached is five months of dummy sales data for eight products. My objective is to filter this data for a 2, 3, 4, 5, and 6 week date ranges, and have my pie chart dynamically change for each corresponding week range I filter. I'm seeking for the size of the Pie Slices to automatically change, as well as the Legend and the color of the Pie slices depending on the products that were sold for the filtered weeks.
LASTLY, I need the Pie Slices to show as a single pie slice for products that show up multiple times once I complete a filter. Attached you will find a dummy data excel file I have setup. You will notice that Product A shows up 4 times in the filtered data, and also shows up as 4 individual pie slices in the Pie chart. This is WRONG. How do I make Excel display the Product A Pie slice as a single slice that is proportionate to the entire pie? I do not want to see four pie slices for Product A.
Does this require very complicated VBA coding? I would appreciate any help any one can offer.
|
06-30-2009, 01:43 PM
|
 |
Forum Guru
|
|
Join Date: 06 Dec 2006
Location: Mississauga, CANADA
MS Office Version:2003 & 2007
Posts: 18,517
|
|
|
Re: Need PIE Slices as percent of entire PIE
Your post does not comply with Rule 5 of our Forum RULES. We have Seven question forums: Miscellaneous, General, Programming, Worksheet Functions, Charting, Excel 2007 Help and New Users. Please choose the appropriate forum, and post your question in ONLY one forum.
I will change it to Charting this time for you.
__________________
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above my avatar (picture) in this post.
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
|
07-01-2009, 04:48 AM
|
 |
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,223
|
|
|
Re: Need PIE Slices as percent of entire PIE
No need to any VBA code.
Just create some extra data via formula to determine whether a record is within a 2,3,4,5 or 6 week period.
Then use a pivot table to summarise your data.
If you also display items with no data you will retain the colours for each product.
|
07-01-2009, 10:24 AM
|
|
Registered User
|
|
Join Date: 05 Aug 2006
Posts: 55
|
|
|
Re: Need PIE Slices as percent of entire PIE
Quote:
Originally Posted by Andy Pope
No need to any VBA code.
Just create some extra data via formula to determine whether a record is within a 2,3,4,5 or 6 week period.
Then use a pivot table to summarise your data.
If you also display items with no data you will retain the colours for each product.
|
Andy,
Thanks for the file you bounced back. I think it is a very impressive approach. However, is there an approach that does not rely on pivot tables? To me the ideal solution would be one that requires no pivot tables and any additional data processing. Is there a way to do just using a formula that will control the way the chart outputs its quantity of slices?
|
07-01-2009, 10:53 AM
|
|
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,223
|
|
|
Re: Need PIE Slices as percent of entire PIE
Without using pivot tables you would need to summarize the data your self using formula.
|
07-01-2009, 12:41 PM
|
|
Registered User
|
|
Join Date: 05 Aug 2006
Posts: 55
|
|
|
Re: Need PIE Slices as percent of entire PIE
Quote:
Originally Posted by Andy Pope
Without using pivot tables you would need to summarize the data your self using formula.
|
Andy,
Thanks again for your help. I feel that what I am trying to do with a pie chart is something that I am sure thousands of business people using Excel 2003 have wanted to do at one time or another. It bewilders me why Microsoft has not made it easier to produce a pie chart with pie slices that combine common data into one pie slice and that shows all the pie slices in proportion to the entire pie. To have to do what you have shown me in the two files you have shared is in my opinion not very user friendly.
Therefore, I am now wondering if Excel 2007 has improved Pie Chart capabilities. What I am trying to accomplish is to filter data by a date range, and show the resulting filtered data as percentage pie slices in relation to an entire pie (100% represents the entire pie). At the very least I think Excel 2007 differs in that it supports the ability to easily filter by setting up a data range you specify. I remember seeing in Excel 2007 how one could filter using a date range such as 6-1-2009 to 6-24-2009. Are the Pie Charting cababilities in Excel 2007 more robust? Will it enable me to show the pie slices the way I want to show them? Any info you can provide on Excel 2007 relating to this matter will be greatly appreciated. Thanks
Last edited by binar; 07-01-2009 at 01:11 PM.
|
07-02-2009, 05:45 AM
|
|
Forum Guru
|
|
Join Date: 10 May 2004
Location: Essex, UK
MS Office Version:2003 & 2007 sp2
Posts: 7,223
|
|
|
Re: Need PIE Slices as percent of entire PIE
They have built something, it's called pivot chart but you chose not to use it.
The charting engine itself was completely rewritten but no new chart types were added.
You can use the custom filter in xl2003 to get information between 2 dates.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
