How to predict number sequences i.e. Excel?

Question

In Excel, there is a nice feature where if you enter 1, 2, 3 in three consecutive cells and then select the cells and drag down, it automatically fills in the rest of the cells as 4, 5, 6, 7, etc. It is also able to match more patterns. For example, if I did 2, 4, 6, 8, it would recognize I am adding 2 and then suggest 10, 12, 14, etc.

How does Excel know which numbers? Do they hardcode the different cases (i.e. adding some constant, subtracting some constant, multiplying by some constant) or is there some algorithm that can automatically predict this for you? (This question should be language-agnostic, but if it helps, I want to do this in jQuery)

Thanks!

score 3 · Accepted Answer · answered Jul 24 '11 at 17:16

3

I'm pretty sure it's a simple thing to do. Excel probably checks, when a user enters three numbers, the delta between each consecutive number. If they are the same both times, it will use that delta to predict the next number.

answered Jul 24 '11 at 17:16

Jon Martin

3,252
5
29
45

But IIRC, it also works when you multiply...so if you have 2, 4, 8, it will suggest 16, 32, 64, ... – jasonbogd Jul 24 '11 at 17:20
Nah, just addition and subtraction. – Jon Martin Jul 24 '11 at 17:20
I just tested it and it doesn't. It only works with additions and substractions (unless there are special cases I'm not aware of, in which case it's not multiplication nor division) – Jumbala Jul 24 '11 at 17:21

score 1 · Answer 2 · answered Jul 28 '11 at 07:32

1

If you want to do this more generically than Excel does (only addition or subtraction of constant values), have a look at some interpolation algorithms from numerical methods point of view. This wikibook article is an ok tutorial.

answered Jul 28 '11 at 07:32

Mihai Maruseac

20,967
7
57
109

score 0 · Answer 3 · answered Jul 24 '11 at 17:20

In Excel, it only works with additions and substractions (that I know of, I just tried with multiplications and the results don't follow the same pattern as the originals).

Knowing that, I think it would be easy to calculate the difference between the first two cell, then check the difference between cell 2 and cell 3 to see if it's the same. You can then apply that transformation to the cells you want to predict.

*Example*

Cell1: 2
Cell2: 5
Cell3: 8

if(cell2 - cell1 == cell3 - cell2)
    cell4 = cell3 + (cell2-cell1); //Cell4 will now be 11 since it increments by steps of 3

You could then use a for loop that would copy the value you want to all selected cells (if you want to make the next ten cells follow the pattern, use a for or a while loop that will update the value of those 10 cells)

How to predict number sequences i.e. Excel?

3 Answers3