MS Excel question

[kinetic (Steve)]

Hi ,

I wonder if anyone familiar with Excel could help me because I can't
find anything in Tutorials or helpfiles that explains what I want to do.
I want a column of numbers to all be adjusted with a 'bias' so that
when displayed with the chart wizard, they all reference above and below
a flat line which is zero.
I know this is simply using the function feature but I can't tweak it
to do what I want. I'm >|---|< this close to working it out myself but
I'm doing my head in!

To draw a mental picture, imagine the numbers when drawn as a chart
show a sine wave but the sine wave goes 'uphill' or 'downhill'
I want to adjust all of the numbers so the sinewave just goes 'flatline'
left to right.

For the periodic error experts...it is a PE file but tracking the sky at
the wrong rate...too slow is uphill, too fast is downhill.

thanks in advance,
Steve

[sheeny (Al)]

G'Day Steve,

There's probably lots of ways to do this...

My suggestion is to use regression in the data analysis add-in. Amongst all the other stuff this produces, the "intercept" coefficient (b) and the "X variable 1" coefficient (m) should give you the centre trend line:

y = mx + b



To normalise your data to oscillate around a horizontal line, calculate y from the the above equation in a column beside your raw data, and produce your normalised data by subtracting this from your raw data.



Clear as mud?



Al.

[kinetic (Steve)]

Originally Posted by sheeny

My suggestion is to use regression in the data analysis add-in. Amongst all the other stuff this produces, the "intercept" coefficient (b) and the "X variable 1" coefficient (m) should give you the centre trend line:

y = mx + b

To normalise your data to oscillate around a horizontal line, calculate y from the the above equation in a column beside your raw data, and produce your normalised data by subtracting this from your raw data.

Clear as mud?

Al.

Errr..I think so...
So I should regress to a child like state and get Carl (Renormalised) to do
it for me?.....

Yep , seriously, I get you...a thousand thankyous Al !

Steve

[mithrandir (Andrew)]

I did this with OpenOffice, but I guess you can slum it with Excel. I generated some data with a linear and sinusodal component and a bit of randomness. The example is saved in Excel XP format.

Suppose you have 120 Xs in col A and 120 Ys in col B starting in row 3

In E3 put =SLOPE(B3:B122;A3:A122)
In E4 put =INTERCEPT(B3:B122;A3:A122)
In C3 put =A3*$E$3+$E$4
In D3 put =B3-C3

Select C3-D3 and drag the black square at the bottom right corner down to row 122. The formula copies.

Select A3-D122, the "Chart" tool, "X-Y scatter" "Points and Lines", ensure column A is the X values and columns B-D are the Y values.

Fiddle with the format to your heart's content.

[kinetic (Steve)]

Worked a treat!
thanks Andrew and Alan,

Steve

[kinetic (Steve)]

Originally Posted by mithrandir

I did this with OpenOffice, but I guess you can slum it with Excel. I generated some data with a linear and sinusodal component and a bit of randomness. The example is saved in Excel XP format.

Suppose you have 120 Xs in col A and 120 Ys in col B starting in row 3

In E3 put =SLOPE(B3:B122;A3:A122)
In E4 put =INTERCEPT(B3:B122;A3:A122)
In C3 put =A3*$E$3+$E$4
In D3 put =B3-C3

Select C3-D3 and drag the black square at the bottom right corner down to row 122. The formula copies.

Select A3-D122, the "Chart" tool, "X-Y scatter" "Points and Lines", ensure column A is the X values and columns B-D are the Y values.

Fiddle with the format to your heart's content.

Just a slight correction to Andrew's formula above:
In E3 put =SLOPE(B3:B122,A3:A122)
In E4 put =INTERCEPT(B3:B122,A3:A122)

Commas as separators ^

[mithrandir (Andrew)]

Originally Posted by kinetic

Just a slight correction to Andrew's formula above:
In E3 put =SLOPE(B3:B122,A3:A122)
In E4 put =INTERCEPT(B3:B122,A3:A122)

Commas as separators ^

That's a lack of standards thing.

Excel uses ",". OpenOffice uses ";".