16

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.

  • 5
    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
  • 1
    That wont work because I'll still have to do a manual count :S – baswijdenesdotcom Apr 7 '17 at 6:58
  • 26
    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
  • 1
    @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
  • 2
    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
40

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

enter image description here

  • 12
    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
  • 67
    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
  • 74
    Excel internationalises function names? Oh god :( – pjc50 Apr 7 '17 at 12:06
  • 26
    @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 Neely Apr 7 '17 at 12:30
  • 4
    @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
57

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.

  • 17
    This is probably the easiest and most intuitive method which is also fairly general. – jpaugh Apr 7 '17 at 18:43
  • 3
    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
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.

9

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.