Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Charting % of Instances
I need a chart that shows a number of times that someone's been through our
system. I have a list of names in column B. Some are there once, some are repeated once, some twice, some three times. I'm looking for a way of pie charting the amount of times someone's been through. For example the pie might be 50% of the people have only been through once, 25% have been through twice, and the other 25% have been through 3 times; these results gathered by counting and comparing the names in the B Column. I'm not sure how to do it. I'm thinking =COUNTIF into a blank column which I can then output to a pivot table which I can get a pie chart from but this may be WAY overthinking/funcitioning it. Any help? Thanks in advance! --Dax -- I would give my left hand to be ambidextrous! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Charting % of Instances
Start with your original idea of a countif column next to data (I'll assume
data is in column a =COUNTIF(A:A,A1) Now, we'll setup a simple table for your pie chart In D1: G1, put values of 1, 2, 3, 4 respectively. D2 formula: =COUNTIF($B:$B,D1)/D1 Copy all the way across You now have a count for each amount of repetition. Note that if you want, could change last column formula to =COUNTIF($B:$B,"="&G1)/G1 to allow for counting people who visit more than 4 times. You could try going with your Pivotchart idea, but I think this way is simpler (and much smaller in file size!) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dax Arroway" wrote: I need a chart that shows a number of times that someone's been through our system. I have a list of names in column B. Some are there once, some are repeated once, some twice, some three times. I'm looking for a way of pie charting the amount of times someone's been through. For example the pie might be 50% of the people have only been through once, 25% have been through twice, and the other 25% have been through 3 times; these results gathered by counting and comparing the names in the B Column. I'm not sure how to do it. I'm thinking =COUNTIF into a blank column which I can then output to a pivot table which I can get a pie chart from but this may be WAY overthinking/funcitioning it. Any help? Thanks in advance! --Dax -- I would give my left hand to be ambidextrous! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Charting % of Instances
Thanks for helping Luke. Sorry to be an idiot but I'm getting a DIV/0! error
so let me walk through what I did and see if I'm making a mistake somewhere. My names are in Column C and my next available column is Column W, so in W1 I entered, =COUNTIF(C:C,C2). I then copied this formula down column W. Then I put 1, 2, 3, 4, etc in columns X, Y, Z, etc. I then entered, =COUNTIF($W:$W,X1)/X1 into X2 and it gives me the #DIV/0! error. Am I doing something wrong? -- I would give my left hand to be ambidextrous! "Luke M" wrote: Start with your original idea of a countif column next to data (I'll assume data is in column a =COUNTIF(A:A,A1) Now, we'll setup a simple table for your pie chart In D1: G1, put values of 1, 2, 3, 4 respectively. D2 formula: =COUNTIF($B:$B,D1)/D1 Copy all the way across You now have a count for each amount of repetition. Note that if you want, could change last column formula to =COUNTIF($B:$B,"="&G1)/G1 to allow for counting people who visit more than 4 times. You could try going with your Pivotchart idea, but I think this way is simpler (and much smaller in file size!) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dax Arroway" wrote: I need a chart that shows a number of times that someone's been through our system. I have a list of names in column B. Some are there once, some are repeated once, some twice, some three times. I'm looking for a way of pie charting the amount of times someone's been through. For example the pie might be 50% of the people have only been through once, 25% have been through twice, and the other 25% have been through 3 times; these results gathered by counting and comparing the names in the B Column. I'm not sure how to do it. I'm thinking =COUNTIF into a blank column which I can then output to a pivot table which I can get a pie chart from but this may be WAY overthinking/funcitioning it. Any help? Thanks in advance! --Dax -- I would give my left hand to be ambidextrous! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Charting % of Instances
Sorry, I'm an idiot. I put the 1, 2, 3, etc in row 2! Works great now!
Thanks so much! -- I would give my left hand to be ambidextrous! "Dax Arroway" wrote: Thanks for helping Luke. Sorry to be an idiot but I'm getting a DIV/0! error so let me walk through what I did and see if I'm making a mistake somewhere. My names are in Column C and my next available column is Column W, so in W1 I entered, =COUNTIF(C:C,C2). I then copied this formula down column W. Then I put 1, 2, 3, 4, etc in columns X, Y, Z, etc. I then entered, =COUNTIF($W:$W,X1)/X1 into X2 and it gives me the #DIV/0! error. Am I doing something wrong? -- I would give my left hand to be ambidextrous! "Luke M" wrote: Start with your original idea of a countif column next to data (I'll assume data is in column a =COUNTIF(A:A,A1) Now, we'll setup a simple table for your pie chart In D1: G1, put values of 1, 2, 3, 4 respectively. D2 formula: =COUNTIF($B:$B,D1)/D1 Copy all the way across You now have a count for each amount of repetition. Note that if you want, could change last column formula to =COUNTIF($B:$B,"="&G1)/G1 to allow for counting people who visit more than 4 times. You could try going with your Pivotchart idea, but I think this way is simpler (and much smaller in file size!) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dax Arroway" wrote: I need a chart that shows a number of times that someone's been through our system. I have a list of names in column B. Some are there once, some are repeated once, some twice, some three times. I'm looking for a way of pie charting the amount of times someone's been through. For example the pie might be 50% of the people have only been through once, 25% have been through twice, and the other 25% have been through 3 times; these results gathered by counting and comparing the names in the B Column. I'm not sure how to do it. I'm thinking =COUNTIF into a blank column which I can then output to a pivot table which I can get a pie chart from but this may be WAY overthinking/funcitioning it. Any help? Thanks in advance! --Dax -- I would give my left hand to be ambidextrous! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 Instances | Excel Discussion (Misc queries) | |||
linking certain instances | Excel Discussion (Misc queries) | |||
Create list of unique instances from list of multiple Instances | Excel Worksheet Functions | |||
Excel Instances | New Users to Excel | |||
Custom charting - Stacked charting with a line | Charts and Charting in Excel |