Same Category Names Combined before Graphing

nirvehex · #1 09-07-2008, 10:50 PM

Hi,

I'm working with the stock market, trying to create a column graph.

I am trying to get my graph to display industry vs. percent growth. However, the way my excel spreadsheet is setup I have the same industry written down more than once.

For example, my chart looks like this:

Stock - Industry - Percent Change
CYTX - Biotechnology - +2.02%
ESLR - Electrical Components - -16.64%
MNTA - Biotechnology - -6.64%

and so on...

When I go to plot industry vs. percent change in a column bar graph, the same industry will show up twice. Instead I want it to group industries together and also average the two percent changes together.

Is there an easy way to do this and do I a present my problem clearly?

Thanks

Mark

se1429 · #2 09-08-2008, 05:04 AM

Hi,

I think the easiest way is to use a pivot table and summarize your data by industry.
In your case your data will be summarized using the sum function as a default, but you can easily change that to an average.

In case you are not familiar with pivot table:

1. In the data menu click Pivot Table and then follow the wizard.
2. When you get the pivot table field list:
a. Drop the "industry" filed in the area where its says "Drop row fields here".
b. Drop the percentage filed in the area where its says "Drop data items here".
C. Double click on "sum of ..." and change that to "average".
3. You can then plot a chart using the pivot table instead of your detailed data.

Note: your data must have columns headings.

I hope this helps.

Regards.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
New to VBA and Excel, need help splitting a name field and dumping extra middle names	Jonnymorris	Excel Programming	6	09-05-2008 09:44 AM
Changing / Masking Category Names on a PivotChart	Rob_Clark	Excel Programming	0	06-09-2008 11:25 AM
how do I search a word or pdf for a list of names in a CSV file?	Martin Crane	Excel Programming	1	01-30-2008 05:16 PM
Hiding Category Names For Zero Values	TPARK	Excel Charting	1	06-28-2007 06:03 PM
Clearing / Deleting Range Names	tekman	Excel Programming	3	06-27-2007 02:54 PM

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)