0

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
1

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

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.