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
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityWhat 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.
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
Jun 26 '18 at 1:36