Scattergams in Excel?

  • Thread starter Thread starter Peter Frank
  • Start date Start date
P

Peter Frank

Hello,

Is there a way to plot scattergrams in Excel? What I mean by
scattergrams (or are they called dot plots?) are plots which similar
to box-whiskers-plots but instead just plot the values and maybe the
median and/or mean as a line. My data are several columns of values
which have been compared using ANOVA. But I would also like to inspect
the data visually side by side with scattergrams.
Can this be done somehow? Scatterplots don't work because Excel
expects XY data pairs and I only have multiple Y data instead. Or is
there an Excel add-in capable of doing this?

Peter
 
Peter -

What you could do is list your Y values in column B, with a dummy X
value in column A. If the Y values don't overlap, you can use a constant
X, like 1. If the Ys overlap, you can build formulas that will put small
horizontal offsets into the X values. This page shows one way to offset
your X values (actually, it's arrayed horizontally, so it offsets the
Ys, but you get the idea):

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

I couldn't find my favorite offsetting formula, which is buried deep in
the file structure of my hard drive, but I just cobbled this together.
With 20 Y values in B2:B21, put this formula into A2, and fill it down
to A21:

=(COUNTIF(B$2:B2,B2)-COUNTIF(B$2:B$21,B2)/2-0.5)*0.1

This offsets each point for the same Y by 0.1 on the X axis, and centers
them on X=0. Change the 0.1 to move the points closer or further apart,
and add a nominal X value if you're comparing several columns of points,
say, group A at X=1, group B at X=2, etc.

- Jon
 
Have you tried using a line chart. Just format the data series
and change the line type to none.

Dan E
 
Back
Top