Excel '97 Chart Problems

[RWF7437]

I have many problems getting Excel '97 to plot data correctly; esppecially when the data is "random" rather than neatly ordered in even increments.

Does anyone know if later versions of Excel correct some of these problems and add functions like, interpolate, extrapolate, intersect, etc. ?

 

[melone]

Are you plotting it as an "XY Scatter Plot"?

 

[RWF7437]

Yes melone.

I've tried many chart types and the x-y scatter plot is the only one which SOMETIMES works.

 

[pmover]

while i've plotted much data using excel 97 (now use 2000), i've not had difficulties as you've stated.

a couple of suggestions and offer.

1) for x-y charts, be certain that there are equal x-data and y-data.
2) are the data being plotted on primary axis or secondary axis?

i'll be glad to assist you in your endeavors (no charge) upon request. however, the findings and results should be posted for others to learn from.
please advise.
-pmover

 

[ivanlocke]

What exactly are the problems you are having?
I can't imagine excel is acutually putting the dots in the wrong spots and you say you are using a scatter plot.

Just guessing, but I imagine you have some data with a lot of scatter(unknown,y) and a small range(known,x) and are trying to fit a line to it, and excel is giving you a line that you find wrong. Its probably that you don't have enough data points. Either get data points farther out from your range (you'll need less data points) or get more data points in the same range (will need many more data points).

Lets say you were plotting dice roll out comes.
You have die 1, 2, and 100.
Lets say you roll die 1 and 2 each 2 times.
die 1 gives you 6 and 5 with die 2 giving you 1 and 3
from this die 3 should have a roll averaging -1.5
of course we know the trendline should be horizontal at 3.5

To get a much more accurate graph only one or two rolls with the 100 die would give almost a horizontal line, or you are going to have to roll die 1 and 2 enough times that they both give an accurate median of their values (Not going to do the stats, but I could imagine needing 10 or more rolls per point)

 

[RWF7437]

Thanks to all who've responded.

I do have two Excel charts I'm working with now. One is a hydrograph for a 24 hour storm. The other is a plot of flood probabilities vs time.

If anyone wanted to send me an email address I'd be happy to forward them to you for any help you can offer. If I could attach them to this post, I would, but this website doesn't offer us that option, unfortunately.

Russ Faust
rwf7437@attbi.com

 

[IRstuff]

I'll take a stab.

arbiter007@hotmail.com

TTFN

 

[pmover]

send file to: geolinc3@hotmail.com

-pmover

 

[electricpete]

I haven't seen your files.

Here's one thought. If you select the version of xy-plot that includes lines connecting points, then points are connected in the order of the listed points. That's fine if your points are in order of increasing x. But if your x values jump around your connecting lines will look weird. In that case either turn off the connecting lines or sort the data.

 

[GregLocock]

No, he'd got the first couple of rows of x data as text rather than numbers, probably by typing a space in ahead of the number. I can't tell how it got there, but replacing them with real number s sorts it out. Excel was displaying a line chart as it though they were labels, not x values

Cheers

Greg Locock

 

[RWF7437]

Thanks to all who replied. Thanks GregLOcock especially for finding that error. Learned a lot from the other comments too so I'll be on the lookout for these kinds of errors in the future.

Russ

 

[pmover]

an aside or technique to think about:

in encountering "unknown" problems or situations with excel, i frequently will copy the data and paste (formulas or values; never formatting) the data into a new workbook and then start over or conduct the investigation into the problem. this activity resolved many of the problems i've encountered.

bullseye greg!
-pmover