What exactly is Excel's Auto Fill algorithm?
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
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.