Summing graphs without differnt independent variables

S

Sebastian Stormbo

Hey all,

I am having a very inconvinient problem, which seems to potentially ruin
several weeks of hard work -- and it would be immensly appreciated if anyone
could come up with a quicly implementable solution.

OK, the data I am trying to present is power demand for trains on a distance
of rail over time. In the data I have, power demand is measured at every 250
metres for each train. This means taht my data is like this.

m T1 T2 . . .
0 1500 5462 . . .
250 1700 1654 . . .
500 1200 6542 . . .
750 4000 1224 . . .
1000 3100 5555 . . .
..
..
..

Where at each cell (each 250 m) I know the power demand. Additionally, I
know at what time the trains passed each 250-metre point.

Therefore, with graph with a X-Y Scatter Chart, I can graph Power as a
function of time even though time is not an independent variable.

However, I have problems graphing the aggregate power demand as a function
of time.

Is there anyway I can sum alle the graphs in one chart, or anything similar?
Thanks in advance
 
J

Jon Peltier

You want to sum demand at each time? you need to have a value at each time
for each series. This means you need to somehow interpolate all curves to a
standard set of time points, then add these interpolated values.

- Jon
 
S

Sebastian Stormbo

Thanks Jon!

Yeah, this was what I figured. But this will make the results a lot less
exact, because the curve is -- and thus the parts of the curve between each
point are -- not linear.

Is there no way Excel can interpolate directly from the chart, and hence
expand the data to contain a value for every point of time?
 
L

Lurker

Interpolation is a non-trivial area, see
http://en.wikipedia.org/wiki/Interpolate

You ask Excel to interpolate between values, but say linear interpolation is
inappropriate without indicating what form of interpolation might be OK.

I think your choices are:

a) use linear interpolation and hope it's close enough; depends on the form
of the relationship and the spacing of the points; perhaps transform your
data so the relationship is more like linear, try log(Power)?.

b) fit a curvilinear regression model ; if you know the (approximate) form
of the relationship and can fit it, see LINEST, GROWTH, LOGEST, TREND

c) learn a bit more about interpolation; I don't think Excel has many (any?)
means of interpolation built-in in an easy to use form

But I'm not an Excel expert, just a lurking statistician.

HTH

A Lurker
 
J

Jon Peltier

I would not pretend that the smoothed line option of a series formatting
produces accurate values between actual points. You certainly don't want a
software package to make assumptions about your data in any case. You could
try to fit a polynomial (please limit the degree you use, no more than
second order) or another relationship which is physically meaningful to your
model.

- Jon
 
D

Del Cotter

Yeah, this was what I figured. But this will make the results a lot less
exact, because the curve is -- and thus the parts of the curve between each
point are -- not linear.

Is there no way Excel can interpolate directly from the chart, and hence
expand the data to contain a value for every point of time?
Click to expand...

Why should charts be any better at knowing where the curves go than
spreadsheet cells? Computing is computing.
 
Top