Scatter Charts

[Aurora]

I am using Excell 2000

I am trying to create a scatter chart comparing to our pay
in relations to the midpoints of the wage ranges.
I put the data in Excell the % is where the employee is in
their pay range ex: Jan Doe making $100/yr may be at the
70% of her range, Billy Bob who makes $175/yr is the 90%
of his pay range.
A B C D E F G
70% $100
80% $200, $100,
90% $250, $325, $175 etc
100% etc.
My graph has the % on the X axis and the $$ on the Y axis.
I indicated the data range is A1 through D5 and chose
rows. Then I said series 1 is B2, Series 2 is B3 thru C3,
Series 3 is B4 thru D4 etc

But my points are all over the chart and do not stay in
their position. For example. In the 70% area, I ended up
with 3 points when I should have only had 1 point. The
exta points read something like this - Series"90%"
point "70%" (70% $325).

Does anyone know what I am doing wrong or can you direct
me to someplace regarding creating scatter charts.

Please help!!! - Aurora

 

[Jon Peltier]

Aurora -

You need to think about what you're plotting. You cannot get the kind of
% vs $ that you want with the data you are showing.

The data you are showing is a lookup table, which tells me what
percentage an employee is at given their salary and their "class" (A, B,
C, etc). So I put this table into a worksheet, starting in A1:

A B C D
70% $100 $75
80% $200 $100
90% $250 $325 $175
100% $300 $350 $200

I put this table into the sheet, starting in A9:

Empl. Class Salary Percent
Jane A $100 70%
Billy C $175 90%
Fred B $90 70%

The first three columns you populate, the last is a formula which looks
up the percentage from the table above, based on salary and class. In
D10, the formula is:

=INDEX($A$2:$A$5,MATCH(C10,OFFSET($A$2:$A$5,0,MATCH(B10,$B$1:$H$1))))

This gets filled down the list of employees. I direct you to online help
for assistance with INDEX, MATCH, OFFSET.

Now select the salary and percent range and make an XY Scatter chart. If
you want to put the employee names onto the points, download Rob Bovey's
Chart Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com). Both are free and let you select a range to get
data point labels from.

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