Prev: How to change series plotting order without changing legend or
Next: Adding data labels to bubble charts
From: malycom on 2 Dec 2009 10:10
Hi

I have 1160 rows of client information listing the addresses and country.
The last column is the country and what I would like to do is to show a
graph that calculates how many times a country is listed in the column and
then work out the percentage of it's use.

For example, assuming it was 100 clients and only United Kingdom, USA and
France were listed with UK being listed 41 times, USA listed 36 times and
France listed 23 times, I would like to see a chart that says United Kingdom
41%, USA 36% and France 23%

I don't care what sort of chart can prioduce this but there may be quite a
lot of countries in the actual column.

Can this be done, and if so, how can I do it. I have absolutely no idea.

One final thing, it doesn't have to be a chart - It could just be a list
stating the percentage values.

If anyone can help with this, it would be greatly appreciated.

Thanks in advance

Mal
From: Luke M on 2 Dec 2009 10:37
First, we need to generate a list of unique country names. You can do this
using Data - Filter - Advanced Filter. Select your data, pick a "copy to"
range, and then check "unique values only". (I'll assume you generated a list
starting in D2).

In E2:
=COUNTIF(C:C,D2)
This will give you the count of how many times the country appeared.

In F2:
=D2/SUM(E:E)
Format cell as a percentage. This gives you what percentage it was used.

For presenting the data, I would use the E column data and put it into a bar
chart. This would let you easily display all the country names and their
repsective usage.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"malycom" wrote:

> Hi
>
> I have 1160 rows of client information listing the addresses and country.
> The last column is the country and what I would like to do is to show a
> graph that calculates how many times a country is listed in the column and
> then work out the percentage of it's use.
>
> For example, assuming it was 100 clients and only United Kingdom, USA and
> France were listed with UK being listed 41 times, USA listed 36 times and
> France listed 23 times, I would like to see a chart that says United Kingdom
> 41%, USA 36% and France 23%
>
> I don't care what sort of chart can prioduce this but there may be quite a
> lot of countries in the actual column.
>
> Can this be done, and if so, how can I do it. I have absolutely no idea.
>
> One final thing, it doesn't have to be a chart - It could just be a list
> stating the percentage values.
>
> If anyone can help with this, it would be greatly appreciated.
>
> Thanks in advance
>
> Mal
 | 
Pages: 1
Prev: How to change series plotting order without changing legend or
Next: Adding data labels to bubble charts