0
$\begingroup$

I have a scatter plot in Excel (upper part of the screenshot) of time-series data. In-between the values that I plot (to the left), are some missings. I fit a (linear) line to those values and display the according model. I realized that Excel doesn't just leave out the missing values. Leaving those out would lead to the model in the bottom. As you can tell by comparing the model equation (and also the fitted line) it is very different.

Now, I wonder what Excel does to account for the missing values so to still be able to fit the line. There must be some kind of interpolation going on. But which method is used? There are no options to chose from and I can't seem to find it documented anywhere.
Also, does the method change when I change the fitting to "logarithmic"?

Excel fitting data

/e: @user12075: Thanks! You are completely right. Now, I'm wondering if this behavior makes sense? For me at least it was confusing.
Here's the proof to your answer:

enter image description here

Improve this question
$\endgroup$
2
  • 2
    $\begingroup$ Look at the interpolating equation in the upper right corner of the plot. It is fit using ordinary least squares (OLS). The Excel help page explains this. $\endgroup$
    – whuber ♦
    Commented Sep 29, 2018 at 15:51
  • $\begingroup$ Re the edit: older versions of Excel used to interpret blanks (or even non-numeric values) as zeros. Starting around 15 years ago (as I recall) it started behaving a little better by just ignoring them. $\endgroup$
    – whuber ♦
    Commented Sep 30, 2018 at 15:23

1 Answer 1

Reset to default
5
$\begingroup$

Actually Excel does leave out the missing values. No interpolation is involved.

The reason your plots look different is only because the default values of you x-axis changed. In figure 1, x=[1,2,3,4,5,6,17,18,19,20,21]; in figure 2, x=[1,2,3,4,5,6,7,8,9,10,11].

Improve this answer
$\endgroup$
1
  • $\begingroup$ If this is the case, why are the trend line equations different in the top two graphs? $\endgroup$
    – johnDanger
    Commented Nov 28, 2020 at 18:33

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.