Creat grouping count values in Pivot Chart

[timko92]

Here is a picture of what I'm looking at so the question makes more sense:

As you can see, there are too many values for the pie chart to display individually by country. So what I would like to do for the pie chart is to group by values, so 1-2, 2.01-3, 3.01-4, 4.01-5. Then I would like a count of how many counties there are in each group. I have tried several approaches but so far no luck. Any suggestions? Thank you

[RudiS]

What you could try is :
1. right click on the values and choose sort smallest to largest
2. after the sort, select all country names between 1-2 and right click on the selection and choose group
3. repeat step 2 for various value groupings
4. collapse the groups and the chart will look less "busy"

[timko92]

Thank you. That is a good alternative I will try for now. But ideally, and I might be asking for too much here, I would like the series for those ranges I mentioned be on the pie chart if that makes any sense.

[FDibbins]

Tim, please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

I know you were just showing your pradicament, but did you see how small that was anyway?

Having said that, if you want to count within "ranges" take a look at the countifS() function

[timko92]

Gotcha. Would you suggest making use of the countif function by making calculated fields within the pivot table with that function, or to just use separate cells for that and make a graph off of those cells?

[RudiS]

I'm not sure if I understand you, but you can collapse and expand the groups to determine the detail to display on the chart. Collapse down all the groups and expand only one group, etc...

Also note that I'm not sure if you WILL be able to effectively fit ALL that data onto a pie and expect to see legible output. A pie is just not equipped to handle that volume of data.

[timko92]

Hey Rudi,

I think FDibbins answered my question. But I know that a pie chart can't handle all that data, and so what I'm trying to achieve is to have ranges for number values, and have each part of the pie chart be a representation how many nations fall into each range. In other words, what percentage of nations falls in the 1-2 category for example. There would be no specific nations if that makes any sense.

[RudiS]

OK... Experiment with the COUNTIF() function.
If you need further assistance, upload a sample workbook and some dummy data so we can help see that you need.

Cheers

[timko92]

Thanks guys. While I'm here in this thread, so as not to make another one, I have a question regarding defining to count values between values in countif. In another thread someone said to do =COUNTIF(rng,">x")-COUNTIF(rng,">y"), but all this does is subtracts the one count from another.

Never mind, figured it out!

[FDibbins]

Happy to help and thanks for the feedback )

its always a good feeling when you figure it out for yourself

[timko92]

Quick question though. I got the chart looking like how I wanted it to by just making use of separate cells. Out of curiosity I decided to try to use the countifs function to create a field in the pivot table calculated fields section, and when using the same exact formula for one of my ranges, when I click "Ok", it highlights the first cell of the range I specified, and won't do anything else. Would you be able to explain why that's the case?

[FDibbins]

If I want to use a calc for a PT in the manner you want, I always do it in the original data, in a helper, and then use that in the PT