how to subtract previous cell containing number in excel

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
• 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

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. ( :