Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2011, 03:58 PM
knownunknown knownunknown is offline Generate chart from list of text values Windows 7 64bit Generate chart from list of text values Office 2010 32bit
Novice
Generate chart from list of text values
 
Join Date: Jan 2011
Location: London
Posts: 4
knownunknown is on a distinguished road
Default 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)
Reply With Quote
  #2  
Old 01-21-2011, 01:18 AM
macropod's Avatar
macropod macropod is offline Generate chart from list of text values Windows 7 32bit Generate chart from list of text values Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 01-21-2011, 06:08 AM
knownunknown knownunknown is offline Generate chart from list of text values Windows 7 64bit Generate chart from list of text values Office 2010 32bit
Novice
Generate chart from list of text values
 
Join Date: Jan 2011
Location: London
Posts: 4
knownunknown is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 01-21-2011, 01:45 PM
macropod's Avatar
macropod macropod is offline Generate chart from list of text values Windows 7 32bit Generate chart from list of text values Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #5  
Old 01-21-2011, 03:29 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Generate chart from list of text values Windows 7 32bit Generate chart from list of text values Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Attached Files
File Type: zip Book1.zip (9.9 KB, 32 views)
Reply With Quote
  #6  
Old 01-22-2011, 11:02 AM
knownunknown knownunknown is offline Generate chart from list of text values Windows 7 64bit Generate chart from list of text values Office 2010 32bit
Novice
Generate chart from list of text values
 
Join Date: Jan 2011
Location: London
Posts: 4
knownunknown is on a distinguished road
Default

Thanks a bunch, very helpful guys!
Reply With Quote
  #7  
Old 04-24-2013, 01:56 AM
c0rt0 c0rt0 is offline Generate chart from list of text values Mac OS X Generate chart from list of text values Office for Mac 2011
Novice
 
Join Date: Apr 2013
Posts: 1
c0rt0 is on a distinguished road
Default

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?
Reply With Quote
Reply

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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft