scatter/category/line (?)

[boris]

I have the following excerpt:

Geo Year %
NE 1999 25
NE 2000 30
NE 1999 22
NW 1998 15
NW 1999 20

Cannot figure out what parameters, types, etc, I am
looking for to accomplish one (or both) of the following:

First graph
- X Axis Year
- Y Axis %
- color coded series by Geo
Basically, something that just shows me the dots that
represent where the geographies were by year. I will then
remove the line segments and have just the data points.
Pretty sure this is just a straight line graph, except as
you'll notice, the year/geo combination is not unique.
Each of these represents a txn, and therefore can occur
multiple times in the same geography, in the same year,
with different results. I need all results plotted, to
show where the clusters fall.

Second (and more important) graph
- X Axis Geo (category?)
- Y Axis %
- no year represented
Need it to just show each Geo in its own vertical silo,
compared to the others. Want to see how the %s group in a
geo and how they look compared to others.


Help is so greatly appreciated!

B

 

[Jon Peltier]

Boris -

This web page has an example that should help:

http://peltiertech.com/Excel/Charts/ConditionalChart1.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[boris]

Jon, thanks. that was helpful. I have one other problem
that needed solving, but this does not help. What I
wanted to do was have a cluster of data points that were
vertically plotted above each of, say, five categories.
So the data will indicate both the category (non-
numerical) and the Y-value. So each entry is a Y-Value, a
Category, and a second indicator of Category (maybe a
year, whereas first category is Geography). I'd like to
see the Year as the X axis, but have the geography figure
in as a format to the data point plot. So if I have two
years and two geographies, I'd have Year 1 and Year 2 on
X, and each of the Geography A values would be plotted
above both years in squares, while Geography 2 would be in
triangles. As example. Any help?

 

[Jon Peltier]

Boris -

Will you have only one point from each geography category for each year?
Then each series references a geography category, and this is how the
data should be arranged:

Geog A Geog B Geog C etc.
2000 data .... ....
2001 .... goes ....
2002 .... .... here
etc.

Make a line chart, with series in columns, then double click each line
series, change to the marker and color you want on the Patterns tab, and
check None for the line.

Given that your years are the categories, you could use the conditional
charting approach I suggested, where the year stays in its column, the
regions serve as the headers for the conditional series, and you make an
XY Scatter chart:

Geo Year % NE NW
NE 1999 25 25 #N/A
NE 2000 30 30 #N/A
NE 1999 22 22 #N/A
NW 1998 15 #N/A 15
NW 1999 20 #N/A 20

See the web page to help with the formulas under the regional headers.

Select the year column (including the header "Year"), then hold CTRL
while selecting the conditional columns (including the header row with
"NE", "NW", etc.). Make an XY Scatter chart, not a Line chart as above.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______