50
5
What is the algorithm used by Auto Fill in Excel?
For example, when I enter the numbers 3,4,5,7,8 into a column and then use Auto Fill, I get the following result:
3
4
5
7
8
9.3
10.6
11.9
13.2
14.5
15.8
17.1
18.4
Seçkin Durgay
Posted 2018-06-25T11:33:01.397
Reputation: 543
66
Excel uses its Linear Trend approach when you auto-fill. This employs the least-squares method algorithm.
This is the same algorithm used by the TREND() function as seen demonstrated below:
Enter the following formula in C6 and ctrl-enter/copy-paste/fill-down into the rest of the column:
=TREND($C$1:$C$5,$B$1:$B$5,B6)
Below is a chart showing the trend line that the new values fall on.
The least-squares method creates a "best fit" line for the original data points. The new data points are essentially extracted from this line.
robinCTS
Posted 2018-06-25T11:33:01.397
Reputation: 4 135
12Not that I doubt your answer, but just curious how you know they use the least-squares method. (Or, more broadly, did you do some investigating "under the hood"? Or is it common for such applications to use this method?) – BruceWayne – 2018-06-25T19:55:40.350
7
@BruceWayne yes, it's a common approach in simple linear regression
– 0xFEE1DEAD – 2018-06-25T20:03:50.63010@BruceWayne No investigation under the hood was required ;-) It is mentioned in a few places on the web. Plus, Excel's own documentation states that the TREND() function uses "the method of least squares", which, as you can see, I confirmed produces the same result as auto-filling. So, provided that you can believe Micro$oft's own documentation (and we all now how accurate that is) I would say that that makes it fairly conclusive. Plus what 0xFEE1DEAD said. – robinCTS – 2018-06-26T01:36:53.570
7
Excel documentation confirms that dragging the fill handle will insert values using linear best-fit trend
– phuclv – 2018-06-26T06:36:31.100@phuclv Thanks for the link. However, it doesn't explicitly specify which algorithm is used for the linear best-fit. Just because LSM is the most common algorithm doesn't mean it is the one used in MS Office. – robinCTS – 2018-06-26T06:59:10.967
@AndrewT. Those are the programmatic options. When you do a manual auto-fill, either by dragging from the bottom-right corner of the range, or by using the Fill → Series… → AutoFill menu tool, the Linear Trend approach is used. – robinCTS – 2018-06-26T11:34:09.957
1
Obligatory Joel Spolsky video about excel. Skip to ~8 minutes of an explanation of how Excel references cells, which is useful for defining your own series. https://www.youtube.com/watch?v=0nbkaYsR94c
– Bindelstif – 2018-06-26T21:40:47.633@Bindelstif: I watched the first 12 minutes of the video and bailed out. I didn’t see anything particularly useful, and I estimated that the prospects for finding useful information in the remaining 42 minutes were low. What “useful” information are you referring to? – Scott – 2018-06-30T16:07:38.573
@Scott It's not really "useful", but IIRC, the series bit starts just after the 18 minute mark. I bailed out not long after that. – robinCTS – 2018-06-30T16:53:34.687
Marie Antoinette allegedly said “Let them eat cake” (but she probably didn’t). Joel seems to be inclined to feed us cake crumbs — tiny morsels, spread out over a long period of time. (I guess that the presentation is aimed at people who have essentially zero Excel experience.) Joel is clearly brilliant, in his opinion. I wonder what the presentation would have looked like if he had rehearsed it. :-) – Scott – 2018-06-30T20:03:58.180
2Excel probably checks the average increase and use that for autofill. (In your series you increased by 1 three times and once by 2, average is 1.25, round it to one digit (as I'm not affiliated with MS I've no clue why), that's 1.3, so increase number 1.3 in each row.). – Máté Juhász – 2018-06-25T11:54:25.213
3@MátéJuhász That's a good guess, and what I initially thought. However it turns out to be incorrect. See my answer for more details. – robinCTS – 2018-06-25T12:28:33.457
An observation is that the difference between the last two given values is 1.3, and then the auto-filled values all increase by 1.3. The simplest (but, I have now learned, wrong) interpretation for this set of data would be that it just repeats the last difference. – Thomas Padron-McCarthy – 2018-06-25T17:35:45.910
@ThomasPadron-McCarthy Actually, the last two given values are 7 and 8, so the difference is 1. The 1.3 is the calculated Linear Trend slope, which, as you correctly ascertained, is then progressively added to obtain the auto-filled values. – robinCTS – 2018-06-25T18:03:26.570