0
$\begingroup$

I have been trying to manually calculate the slope of a line for large numbers. All the examples I have found demonstrate that manual calculation and SLOPE function in Excel give the same number. However, when I attempt this with large numbers I get different slope values.

For example, if I use the example demonstrated HERE with large numbers (see values below) for X and Y, I get different slope values. Why are the slopes different when I use large numbers but are the same when I use small numbers? And which is giving the correct slope: manual calculation or SLOPE function?

Using the values below, manual calculation gives 0.00004069 but SLOPE function gives 0.00010000.

X Values

  • 1504356680
  • 1504356690
  • 1504356700
  • 1504356710
  • 1504356720
  • 1504356730
  • 1504356740
  • 1504356750
  • 1504356760
  • 1504356770

Y Values

  • 150435.66639
  • 150435.66739
  • 150435.66839
  • 150435.66939
  • 150435.67039
  • 150435.67139
  • 150435.67239
  • 150435.67339
  • 150435.67439
  • 150435.67539
$\endgroup$
3
  • 1
    $\begingroup$ How are you calculating manually? The slope function is correct (we see the y-value increases $10^{-3}$ for each point while the x inreases by $10$ so the slope is plainly $10^{-4}$) but it's a bit hard to help you if you don't say exactly how you computed it. My guess is possibly catastrophic cancellation with premature rounding or calculator truncation. Please show the exact formula you used plus indicate any point where you wrote down any intermediate result (and what you wrote then) to use as an input to later calculation. (Were you using the formula in the question you linked to?) $\endgroup$
    – Glen_b
    Sep 15, 2017 at 4:24
  • $\begingroup$ Interestingly R fails completely on the raw data, giving NA for the slope (!!!). Works just fine if you mean-correct first or just drop some of the leading digits. $\endgroup$
    – Glen_b
    Sep 15, 2017 at 4:29
  • $\begingroup$ @Glen_b Thank you for the feedback! I am using the formula posted HERE on another question. I downloaded the Excel format here file and pasted my X and Y values into the spreadsheet. $\endgroup$
    – MKK
    Sep 15, 2017 at 13:14

2 Answers 2

2
$\begingroup$

Now that we see what formula you used, what's going on is catastrophic cancellation.

While that formula (which was widely used in the era of hand calculation* because it's a bit faster) is algebraically correct, both numerator and denominator involve subtracting very large that differ only after many figures, and if calculations are performed to a finite number of decimal places (as in a computer or a calculator) then it can fail badly.

There's some discussion in the https://en.wikipedia.org/wiki/Loss_of_significance

Also see the discussion of the variance (as used in the denominator of your formula) here:

https://en.wikipedia.org/wiki/Algorithms_for_calculating_variance#Na.C3.AFve_algorithm

Consider that when you square values like 1504356700 + $\delta_i$ (where $\delta_i$ is a value like say 10 or -20) you get 2263089080834890000 + 2(1504356700)$\delta_i$ + $\delta_i^2$. The $\delta_i^2$ term will be making a difference in the 17th figure. You then sum a large number of such terms. It's easy for detail in the $\delta_i^2$ terms to be swamped and for accuracy in the later figures of those terms to be lost.

* When people did hand-calculation they typically worked with corrected data where the numbers would be shifted by a convenient constant (dropping all the leading digits that were the same would easily be sufficient to avoid the problem, as well as speeding the calculation enormously).

If calculating a mean, the correction would be "put back in" to the final answer but for standard deviations, correlation, regression slopes and so on, such shifting would have no impact.

$\endgroup$
0
1
$\begingroup$

This answer doesn't address the underlying reason why the manual calculation in the Excel file does not calculate the correct slope. This answer shows an approach to get the correct slope value using mean corrected values (thank you to Glen_b)

Using the example data in the initial post, the manual calculation and the SLOPE function did not give identical values. If the X and Y data is each mean corrected (subtract mean value from each value), then manual calculation and the SLOPE function give identical values.

So, in the above data examples X and Y values become:

Average X = 1504356725

Average Y = 150435.67089

Subtracting the average X from each X value yields:

  • -45
  • -35
  • -25
  • -15
  • -5
  • 5
  • 15
  • 25
  • 35
  • 45

Subtracting the average Y from each Y value yields:

  • -0.00450
  • -0.00350
  • -0.00250
  • -0.00150
  • -0.00050
  • 0.00050
  • 0.00150
  • 0.00250
  • 0.00350
  • 0.00450

Using the adjusted X and Y values yields 0.0001 for both the manual calculation and SLOPE function for calculating slope.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.