# Can Excel produce a strange but logical sequence of numbers?

I've got this pattern in Excel:

``````14
16
17
19
21
22
24
26
27
29
31
32
34
36
37
39
41
42
44
46
47
49
51
52
54
56
57
59
61
62
``````

The logic to it is +2, +2, +1, rinse and repeat. But when I try to extend the pattern, Excel will start simply counting.

How can I continue this pattern? My list continues until around 700.

• You can define your own formula with that logic, which refers to the cell above. E. g. something like `=A1+2` in cell `B1` and so on which will lead to all odd numbers. – IQV Apr 7 '17 at 6:54
• That wont work because I'll still have to do a manual count :S – baswijdenesdotcom Apr 7 '17 at 6:58
• What do you mean with "manual count"? What is the logic of your counting? When you can't define your logic, how should a program can do this for you? – IQV Apr 7 '17 at 7:01
• @IQV: To be honest, on an IQ test this wouldn't be one of the harder questions. Spotting the +2,+2,+1 pattern is easy. Translating that to Excel is a bit harder. – MSalters Apr 7 '17 at 15:07
• If you ever find yourself wondering "Can Excel do that?", the answer is "Yes, it can." That's because Excel is Turing Complete, which means it's capable of performing any calculation a computer can do. – RubberDuck Apr 9 '17 at 15:09

Excel only recognizes arithmetic patterns.

In your case, you can define a formula and fill it to the needed rows.

`=A1 + IF(MOD(ROW(), 3) = 0, 1, 2)`

Change A1 to the cell where your sequence starts.

In this screen shot below, A1 has the value 14. In A2 I entered this formula

``````=A1 + IF(MOD(ROW(),3)=0,1,2)
``````

And copied it down

• I suspect it's a language issue. There will be either MOD or ROW equivilant in your native language. What language is your PC set up for? May help piuha.fi/excel-function-name-translation/… `MOD` is possibly `REST` and `ROW` is possibly `RIJ` @baswijdenesdotcom – Dave Apr 7 '17 at 8:03
• There is a setting in Excel which will make your life of internet support a million times easier: "Use english function names" – Falco Apr 7 '17 at 10:49
• Excel internationalises function names? Oh god :( – pjc50 Apr 7 '17 at 12:06
• @pjc50 As a programmer I too find it horrifying, but programmers aren't Excel's target market. End users who if not semi-English are unlikely to have memorized a list of English language key words are. For them writing a formula from scratch localized keywords are a much more user friendly option. OTOH I have to wonder how hard supporting localized and English key words would've been. On the gripping hand localization probably dates back to pre-internet times; and my suggestion would be all but guaranteed to break someones spreadsheets somehow if done now. – Dan Is Fiddling By Firelight Apr 7 '17 at 12:30
• @DanNeely I agree that localized function names in Excel might be more suitable for non-programmers, but at least they should convert the names to English internally. I've seen many problems when opening Excel sheets in another locale with different function names and comma/dot as a radix point stackoverflow.com/q/19518644/995714 stackoverflow.com/q/13247771/995714 – phuclv Apr 9 '17 at 2:18

Excel supports arithmetic patterns with step size >1 Fill in the first 3 values, and then use a pattern from there down

1. 14
2. 16
3. 17
4. `=A1+5`

and then copy that down. I.e. A5 becomes `=A2+5` which is indeed 21.

• This is probably the easiest and most intuitive method which is also fairly general. – jpaugh Apr 7 '17 at 18:43
• sometimes I see a question with multiple answers from the same person. most commonly this is a new user not experienced with the site, without permission to comment. whatever the case may be, 1 (or usually both) of the answers are not particularly good. this is the first time I have seen a double answer where BOTH ANSWERS ARE GOOD AND DECENTLY UPVOTED. mind: blown. rats off to ya, @MSalters – chiliNUT Apr 9 '17 at 1:13

Assuming you start at row 1, it's `=ROUND((ROW()*5+37)/3,0)`

For every 3 rows, you add 5, hence 5/3. The 37 is to get the starting value right (row 1 => value 14). `ROUND` to two digits to see how it works.

I really like the intuition behind the already posted answers but it seems everyone is overlooking the simplest solution built into excel.

• Just type 14 in cell `A1`

• in `A2` type `=A1 + 2`

• in `A3` type `=A2 + 1`

• in `A4` type `=A3 + 2`

• select cells A2, A3 and A4 and drag the fill handle in the bottom right corner to the desired point. Excel will indeed repeat the formulas as typed.

Edit: Just to add, although the OP's question is arithmetic in nature and can be tackled with elegant mathematical solutions, if someone comes across this with any other non-mathematical formulas they want to repeat in this fashion, any conditional math would just clutter and slow down the sheet.