#1
|
|||
|
|||
Generate chart from list of text values
I have a list of text values (state abbreviations) and I need to generate a big pie chart that takes the number of occurrences of a state abbr, and slot that into a portion of the pie chart if that makes sense.
For example, if I had these values in a column: CA CA CA NY NY TX So then my pie chart would 1. figure out how many different values there are (three) and 2. build a pie chart that had 1/2 CA, 2/3 NY and 1/6 TX. Is it possible to do this? (My actual spreadsheet has over 1000 rows) |
#2
|
||||
|
||||
Hi knownunknown,
Suppose your set of abbreviations to be graphed is in column A. If you create a list of state abbreviations (eg in column E) you could put a formula like: =COUNTIF(A:A,E1) in, say, F1 and copy down as far as needed. Column F will then have the required stats for charting.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
That makes sense, so then, Col F would then have a number corresponding to each state? But you're saying I would have to define the state values myself in col E, there is no way for excel to just automatically figure out which values there are? Listing 50 states isn't too bad, but would be awesome to know how to get around it if possible, just for future reference.
|
#4
|
||||
|
||||
Hi knownunknown,
Yes, the state list would be in column E and the stats for each state would be in column F. To build the states list would probably require a macro (but maybe someone else knows of a formula).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
You can do this using a pivotchart.
If you make your column a table, the table will automatically expand if more data is added to it - you just have to refresh the pivot table. The great thing about this option is that the pivot table will automatically work out a unique list of states and their counts for you. Example attached. |
#6
|
|||
|
|||
Thanks a bunch, very helpful guys!
|
#7
|
|||
|
|||
I am in a similar situation, trying to figure out a way of A. Grouping text values, that you demonstrate very well but also B. take into account a second column and add the sums?
So for example if: Mike 100 Sue 250 Bob 150 Mike 50 Sue 300 How could I make a chart with all instances of the same person grouped in one and get only the totals? |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Generate tree structure, tool? | Troddel | PowerPoint | 0 | 10-13-2010 11:41 PM |
text in 1 Org chart box will not print | mhinnes | Office | 0 | 08-05-2010 12:11 AM |
Get list of text of a style | mavera2 | Word | 0 | 03-19-2010 01:59 AM |
Fields controlled by List box values | farfromapro | Word | 0 | 02-11-2009 02:19 PM |
Generate Folder | streng | Excel | 0 | 11-11-2008 11:20 AM |