If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I work out percentages of a particular column?
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 |
#2
|
|||
|
|||
How can I work out percentages of a particular column?
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 |
Thread Tools | |
Display Modes | |
|
|