SLOPE function malfunctioning

[Arthur]

Dear All - I hope someone can offer advice.

I am using Windows vista and Microsoft Office 2003 – professional edition –
I bought the computer in Dublin, Ireland, with Vista installed in June 2008
and the Office software before that.

I am having a serious problem with excel. For my research, which I am
supposed to be analysing now, I must use the SLOPE function, with which the
“known ys†are plotted against “known ys†and a line of best fit gives a
slope.

My excel is giving a different answer to that of my colleagues (theirs is
right, mine wrong). Also, when the area to be considered for y points is
expanded, even if there are no data points present, the answer changes.

Please advise me on what I can do. It is very frustrating and stopping me
from properly doing my work at the moment. Unfortunately internet is very
slow (working in Africa) and irregular/unreliable so downloading huge files
and service packs is difficult (but usually eventually possible).

I return to Ireland in early June 2010.

[I tried to contact a Microsoft Ireland support, but was told it would cost
72 euros to email them! - is that for real?]

Thanks

Arthur

 

[Bernie Deitrick]

Arthur,

You can calculate the slope using worksheet functions: for x values in A2 to
A10, and y values in B2 to B10 (and with no blank cells!) the slope of the
fit line is

=SUMPRODUCT((B2:B10-AVERAGE(B2:B10))*(A2:A10-AVERAGE(A2:A10)))/(SUMPRODUCT((A2:A10-AVERAGE(A2:A10))^2))

HTH,
Bernie
MS Excel MVP

 

[Mike Middleton]

Arthur -

Check to verify that the x and y data on your worksheet are truly numerical
values, not text masquerading as numbers.

- Mike
http://www.MikeMiddleton.com

 

[Mike Tordoff]

Try using the LINEST function rather than SLOPE. Perhaps your colleagues
are assuming the best fit is linear.

 

[Bruce Sinclair]

Arthur -

Check to verify that the x and y data on your worksheet are truly numerical
values, not text masquerading as numbers.

... and check also that you have your xs and ys around the right way.
IIRC, this is not always as obvious as you might think (ie I've done this
before ).
Suggest switching them and seeing if you then get the 'right' answer.

HTH

 

[Bernie Deitrick]

Mike,

LINEST is an array entered function that returns the SLOPE and the INTERCEPT
(and some other statistics if you so choose - see help), but both LINEST and
SLOPE are linear fits using the least squares method, and return the same
results.

HTH,
Bernie
MS Excel MVP

Try using the LINEST function rather than SLOPE. Perhaps your colleagues
are assuming the best fit is linear.

Dear All - I hope someone can offer advice.

I am using Windows vista and Microsoft Office 2003 - professional
edition -
I bought the computer in Dublin, Ireland, with Vista installed in June
2008
and the Office software before that.

I am having a serious problem with excel. For my research, which I am
supposed to be analysing now, I must use the SLOPE function, with which
the
"known ys" are plotted against "known ys" and a line of best fit gives a
slope.

My excel is giving a different answer to that of my colleagues (theirs is
right, mine wrong). Also, when the area to be considered for y points is
expanded, even if there are no data points present, the answer changes.

Please advise me on what I can do. It is very frustrating and stopping
me
from properly doing my work at the moment. Unfortunately internet is
very
slow (working in Africa) and irregular/unreliable so downloading huge
files
and service packs is difficult (but usually eventually possible).

I return to Ireland in early June 2010.

[I tried to contact a Microsoft Ireland support, but was told it would
cost
72 euros to email them! - is that for real?]

Thanks

Arthur

 

[Harlan Grove]

You can calculate the slope using worksheet functions: for x values in A2 to
A10, and y values in B2 to B10 (and with no blank cells!) the slope of the
fit line is

=SUMPRODUCT((B2:B10-AVERAGE(B2:B10))*(A2:A10-AVERAGE(A2:A10)))/(SUMPRODUCT((A2:A10-AVERAGE(A2:A10))^2))

....

If you're looking for alternatives to the SLOPE function, less typing
to use

=COVAR(x,y)/VARP(x)

at least since Excel 2003, which made the VAR* functions more robust.
While the - operators in your formula would convert their left
operands to numeric values, the AVERAGE functions would skip numbers-
as-text in their argument ranges, so the results could still be wrong.
If that's the problem, your formula could be changed to

=SUMPRODUCT((y+AVERAGE(-y))*(x+AVERAGE(-x)))/SUMPRODUCT((x+AVERAGE(-
x))^2)

while mine could be fixed as an array formula as

=COVAR(-x,-y)/VARP(-x)