How can I set the increment value for Google Spreadsheets to auto-increment?

Question

OP Edit: I think I want Sheets to behave in a way it's not intended.

I expected Sheets to recognize the incremented value in my formula the way I believe it recognizes the increment value in a selected set of non-formulaic values.

"If wishes were dollars..."

---

---

I have a series of cells referencing other cells:

 | Row |  a  |  b  | col B shows |
 |-------------------------------|
 |  1  |  1  | =a2 |      2      |
 |  2  |  2  | =a4 |      4      |
 |  3  |  3  | =a6 |      6      |

If I select cells b1,b2 & b3, then drag the autofill corner to fill in b4, b5, & b6, the fill pattern follows the increment of the "source" cells, i.e, "+2", but the autofill seems to start with a value based on the number of cells I've selected. It looks like the app takes the value of the first cell in the series ("=a2", in this case) and increments the autofill value by the number of cells selected in the series (3, here), So rather than auto-filling cell b4 with the next value in the progression ("=a8"), b4 is auto-filled with "=a5".

 | Row |  a   |  b   | col B shows |
 |---------------------------------|
 |  1  |  1   | =a2  |      2      |
 |  2  |  2   | =a4  |      4      |
 |  3  |  3   | =a6  |      6      |
 |  4  |  4   | =a5  |      5      |
 |  5  |  5   | =a7  |      7      |
 |  6  |  6   | =a9  |      9      |
 |  7  |  7   | =a8  |      8      |
 |  8  |  8   | =a10 |      10     |
 |  9  |  9   | =a12 |      12     |
 |  10 |  10  | =a11 |      11     |

If I select 4 cells before dragging to auto-fill, it adds "4" to "a2" and places "=a6" where I would expect it to place "=a10". Select 5 cells, I get "=a7" where I'd expect "=a12".

It looks to me like auto-fill is placing the number of values in the series (the series length) above the series increment values. I can see where this would be "by design", but I want to auto-fill my progression by a set increment, not a set series length.

Hope y'all can help! Please forgive me if I've butchered mathematical terminology

Answer 1

• you could use a formula like:

  =ARRAYFORMULA(FILTER(ROW(INDIRECT("A1:A20")), MOD(ROW(INDIRECT("A1:A20")), 2)=0))

  

  • A1:A20 represents range 1-20
  • 2)=0 represents increment by 2

Answer 2

I think your Q is basically "I want to auto-fill my progression by a set increment" and guess that that increment is 2 and you wish to start at 2, so put 2 in one cell, 4 in the cell immediately below, select the two cells, grab the fill handle and drag down to suit.

Answer 3

Try =INDIRECT("A"&ROW()*2-<starting-row - 1>). This will give the result you're looking for, but the pattern of increment won't be as obvious. I don't know a way to make autofill do what you want.