0

I have an Excel table like this and want to get RESULT(subtracting row numbers of cells which contain 1). I have thousands of rows so I need a formula.

ROW NMB      Value     RESULT
1            0
2            1           2-1=1
3            0
4            0
5            0
6            1           6-2=4 
7            0
8            0
9            1           9-6=3
  • Do you mean =IF(B2>0,A2-B2,"")? – Máté Juhász Jan 14 at 18:19
  • I think it might be =IF(C4>0,A4-SUM(D$1:D3),"") you are looking for. – cybernetic.nomad Jan 14 at 18:39
  • 1
    Your first result doesn't fit the pattern. You're subtracting the last col A value from a row containing 1. Is the first result an exception? If the 1st 1 was in the 3rd row, what value would you subtract from it? – fixer1234 Jan 14 at 21:52
  • I think the RESULT for the 1st '1' value is '2-0=2'.. right? – p._phidot_ Jan 15 at 3:53
0

By using an extra column.. you can get the 'RESULT'. Assuming your 1st 'Value' data is located at A1, column B as helper column and the 1st 'RESULT' is in C1.

put :

B1  ---->  =IF(A1="","",IF(A1=1,ROW(),0))
C1  ---->  =IF(A1=1,B1,"")

then :

B2  ---->  =IF(A2="","",IF(A2=1,ROW(),OFFSET(B2,-1,0)))
C2  ---->  =IF(A2=1,B2-B1,"")

and drag downwards.

Done. You can hide of move column B elsewhere if needed.

Hope it helps. ( :

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.