What's the formula to calculate the value of a cell based on the row number

Question

I'm new to Google Sheets.

I'm looking for a way to make this happen:

I have file in this format:

number    name

If number starts from 100, for example, how can I make each cell in number increase for each nth row by 20?

In other words, in the second row number will be (20*2)+100, and in the third row it will be (20*3)+100, and so on.

So the results will look something like:

number    name
100       John Smith
120       Jane Doe
140       George Washington

It's rather unclear what you're after here. What have you already tried? Can you edit your question to include some sample data and what the expected outcome is? Include the code you would use to do this in Python; that may help someone craft an answer. — ale, Aug 22 '18 at 11:13
It seems English isn't your first language. I've made an attempt to clean up your question. Please edit it if something isn't right. I took out the part about doing something in Python, as that's programming, and that's off-topic here. You'd do well to take the tour and also look over the advice at How to Ask. — ale, Aug 22 '18 at 12:04
thanks for helping me you just made something wrong the value of number cell start from 100 to 120 to 140 etc so every row increase by 20 and i edited that — ayman ayman, Aug 23 '18 at 13:50

ale · Accepted Answer · 2018-08-22 12:08:20Z

1

It seems like you want the number to increment by 20 on each row. Assuming "number" is in column A, couldn't you just do this?

      A       B
1  number   name
2    100
3  =A2+20
4  =A3+20
5  =A4+20

That results in:

      A       B
1  number   name
2    100
3    120
4    140
5    160

answered Aug 22 '18 at 12:08

ale

43.2k25 gold badges117 silver badges260 bronze badges

thanks for respond but i have 1000 row and the idea of doing that for manually terrified me and it must be a formula for doing that in program like google spreadsheet – ayman ayman Aug 23 '18 at 13:46
You can simply autofill the column. I autofill a thousand rows all the time. – ale Aug 23 '18 at 17:26

add a comment |

Rubén · Accepted Answer · 2018-08-23 20:42:28Z

ROW() is the Google Sheets built-in function that returns the row number of the cell that holds the formula.

To use a single formula to return the row number for 1000 rows we coulde use something like the following

=ArrayFormula(ROW(A2:A1001)-1)

The -1 makes the automatic row numbering to start on 1 instead of 2

To apply the above to the OP case we could use the following:

=ArrayFormula(100 + (ROW(A2:A1001)-1)*20))

2 Answers 2