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]

What do you mean you can't open it, it's an add-in rather than a normal xl file.

I've never used that before, thanks I'll find the way

 

[Fluff]

Did you follow the instructions on the page I linked to?
If so do you see the XL2BB tab on the ribbon?

 

[vladi305]

In what way didn't it work?

Hi, I checked this now and the reason it didn't work is because there are formulas on the cells, the values are the result of logical formulas

 

[Fluff]

Are you saying the formula I suggested now works?

 

[vladi305]

Are you saying the formula I suggested now works?

No because I need it to work with formulas and in addition to that dragging from bottom to top and this only works down. Correct if I'm wrong please.

 

[Fluff]

It doesn't matter if there are formulae in the other cells as long as they return either 0 or 1.
Why would you want to drag from bottom up? Your image clearly shows you want to count from the 1st 1 to then next 1 below it, which is exactly what that formula does.

 

[vladi305]

It doesn't matter if there are formulae in the other cells as long as they return either 0 or 1.
Why would you want to drag from bottom up? Your image clearly shows you want to count from the 1st 1 to then next 1 below it, which is exactly what that formula does.

The dates go from bottom to top and if I want to note how long did it take from Dec 5 to 12 and then from the 12 to 14 that will be 7 and 2. If I count from top ill get 2 and 7

 

[Fluff]

So are you saying the image you posted is wrong? In E6 you have 1 in E7 5 & in E12 34 which suggests you are count to the next row below.

 

[vladi305]

So are you saying the image you posted is wrong? In E6 you have 1 in E7 5 & in E12 34 which suggests you are count to the next row below.

You seem to look things only your way and haven't read my post carefully. The image I posted is correct. The events happen every other day counting up. That particular event you mentioned on E7 happened on December 28 and it falls the 5th event since the last one counting up. Then it happened on the 30th and I noted 1 because it's the next event.

 

[Fluff]

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.