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

  • 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 Sep 29 '18 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 Sep 30 '18 at 15:23

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].


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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