# How to calculate value based on cell row in Excel?

I want to do some calculation in Excel.

A       B
1      0       1
2      1       0
3      0       4
4      0       3
5      0       2
6      0       1
7      1       0
8      0       2
9      0       1
10     1       0

The cell in column B is the row number of cell A if it is 1 - current cell row number. How to set up this formula if there are many 1 in column A? (After 1 in column A the formula should reset I believe)

• You mean in B2=2 if A2=1 or 0 if A2=0? – yass Jul 1 '17 at 21:26
• I mean, until the next 1 in column A, the formula should be the row number which has value of 1 in column A - current row number – Vahn Jul 1 '17 at 21:39
• A2 has 1, so B1 = row(A2)-row(B1) until row(B2) which is row(A2) = 1. The formula then reset. The next 1 in column A is on A7, then row(A7) - row(B3) so on until row(A7) - row(B7) which result is 0. then the formula reset again until find the next 1 in column A. Is this possible? – Vahn Jul 1 '17 at 21:42
• I updated the formula to correspond your comment kindly try it – yass Jul 1 '17 at 22:18

If you want the row number for A=1 in B1 use:

=IF(MIN(IF(A1:\$A\$10=1,ROW(A1:\$A\$10),999999))>ROW(\$A\$10),ROW(),MIN(IF(A1:\$A\$10=1,ROW(A1:\$A\$10),999999)))-ROW()

Array Formula press Ctrl+Shift+Enter at the same time instead of just Enter

Change A10 to correspond the last row of your Data and keep \$ for fixed references and drag it down.
After copying and pasting the formula double click in it and press Ctrl+Shift+Enter

A   B
0   1 =IF(MIN(IF(A1:\$A\$10=1,ROW(A1:\$A\$10),999999))>ROW(\$A\$10),ROW(),MIN(IF(A1:\$A\$10=1,ROW(A1:\$A\$10),999999)))-ROW()
1   0
0   4
0   3
0   2
0   1
1   0
0   0
0   0
0   0
• after implementing this formula in my current data, all the result is 0. The value in column B is should be != 0 until the row in column A which value is 1 – Vahn Jul 1 '17 at 22:32
• by the way what is 999999 ?? – Vahn Jul 1 '17 at 22:34
• please array formula press Ctrl+Shift+Enter and replace \$A\$10 with the last references, 999999 just a number > row(A10) last row in your Data – yass Jul 1 '17 at 22:39
• oops, I forget to press ctrl + shift +enter . Thank you it's working. – Vahn Jul 1 '17 at 22:43