How to calculate distance between values

[vladi305]

I have a sheet with 0 and 1s. The 1 represent an event. I want to calculate the distance between 1 values. The sheet is large with over 100 columns and around 1400 rows so I want to be able to either do it automatically or by dragging the formula. Everytime there's a 1 I want to mark it with the distance between the one found and the previous one. I need this data to later find how often the event happen

 

[vladi305]

But the formula I suggested does exactly what your image shows.
If it is not working then you will need to post some data, or share a workbook that shows the problem.

I will try it later today to adjust it and see if I can get it to work. I really hope it does otherwise it will take a month or two to complete.

 

[Fluff]

It does not need adjusting, just put it in C3 & fill down. The fact that the dates are in descending order is irrelevant

 

[vladi305]

I've continously worked on finding this formula or the way to count the values

I'm trying to count from bottom to top the distance between 1 values. The 1 value represent an event on a particular date
the formula below when the event is found it extract the numeric value of the cell address and the difference of the previous value with the next one is the distance
so in cell A1 I store manually the value from the previous value of what I will use as the starting point. The idea will be to drag up the cursor and get the count
The question is how to set the value of the previous cell in A1 dynamically
As you notice the result on F17 is correct, the distance from F27 to F17 is 10 but from F17 to F3 is 14 NOT 24

To get the correct value the cell A1 would need to be updated when the formula reach the F3 cell

=LET(
lastVar, $A$1,
nextVar, TEXTJOIN("",TRUE,IFERROR((MID(CELL("address",INDEX($F17,MATCH(1,$E17,1))),ROW(INDIRECT("1:"&LEN(CELL("address",INDEX($F17,MATCH(1,$E17,1)))))),1)*1),"")),
IF(E17=1, lastVar - nextVar, ""))

​

• 

 

[Fluff]

Why are you trying to make life more difficult for yourself?
You seem to be fixated about dragging a formula up, when you can use the formula I posted & drag down.
If the formula I supplied does not give the correct answers then you need to clearly explain
a) why it does not work.
b) why you have to drag a formula up, which goes against best practises.

 

[vladi305]

Why are you trying to make life more difficult for yourself?
You seem to be fixated about dragging a formula up, when you can use the formula I posted & drag down.
If the formula I supplied does not give the correct answers then you need to clearly explain
a) why it does not work.
b) why you have to drag a formula up, which goes against best practises.

Well, your formula is working. I tried now on the test sheet and it works. Let me put it to work on the real large sheet

 

[vladi305]

Yes your formula works perfect sir. Please accept my apologies. Something I was doing wrong that the formula didn't work before but it does the job

 

[Fluff]

Glad to help & thanks for the feedback.