• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pie of Pie chart - showing top n values

C

carlo

New Member
Dear,

I am a beginer in excel charting and found your pie of pie very interresting - Cfr article Group Smaller Slices in Pie Charts to Improve Readability

.

In the illustrated example, values up to a certain % are group in the "other" slice.


I would like the opposite I mean I want to show the top n most important values in n slices and group the rest in the last slice.

By top n I mean : n slices regardless of the value it-self.


Thnaks in advance.

Best regards,

Carlo
 
As pie charts are a poor way to convey data when # of series is greater than 3, why not create a bar chart? Depending on what your source data looks like, you could probably use the LARGE and MATCH functions to get the values and names of top N categories, and then use a formula similar to this:

=SUM(MyData)-SUM(OtherSeriesPulledSoFar)

To get a value for "other".
 
Hi Lule,

Thanks for your very fast response and your sugestion in using bart chart instead of Pie chart; it's a requirement and I can't change that. As there are too many items, the idea is to show a number (n) of items (the number can vary but with a max of 5) to get n + 1 slices.


the source data looks like this and the number of lines can vary (dynamic):

item value

q 6

d 22

h 1.5

f 14

s 9


information is not sorted on alphabetical order neither on value.


Let's take the example I want to show the TOP 3 items and group the reste => 4 slices.

How would you identify the TOP 3 and associated value and consolidate the rest in "Others" ?


Thanks

Carlo
 
First, let's setup a helper column to help distinguish between any possible duplicate values. Assuming source data starts on row 2, helper formula is:

=B2+0.00000001*COUNTIF(B:B,B2)

What this does is add a insignificant amount to value, but makes each value unique enough that we won't have duplicates. This is important for our next formula.


To get top 3 values, we use this formula in F2:

=LARGE(C:C,ROW(A1))

Copy down 3 cells

To get the names associated with those values, place this in E2:

=INDEX(A:A,MATCH(F2,C:C,0))

Finally, to create out "Other" category, in F5:

=SUM(C:C)-SUM(F2:F5)

Make sure there's a label in F1, and then select cells E1:F5 and create your pie chart.
 
Glad it's working Carlo.

One correction to an earlier formula:

=B2+0.00000001*COUNTIF(B$2:B2,B2)

Needed to use a absolute + relative reference, so that values that are identical will be different. My earlier formula would have changed them all the same way.


Sorry about that.
 
I notice it but did not really care about the formula but more interrested in the TOP n approach.

thanks for your follow-up.


Carlo
 
You must log in or register to reply here.
Back
Top