How to calculate distance between values

Occasional Visitor

I have the following cells and I want to calculate the distance between 1 values. The database is big with over 100k values 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

 

 

11		12		13		14		15
								
0		0		0		0		0
0		0		0		0		0
0		0		0		0		0
0		1		0		0		0
0		1		0		0		0
0		0		0		0		0
0		0		0		0		0
1		0		0		0		0
0		0		0		0		0
0		1		0		0		1
0		0		0		0		0
0		0		0		0		0
0		0		0		0		0
0		0		0		1		0
0		0		0		0		0
0		0		0		0		0
0		0		0		0		0
0		0		1		0		0
0		0		0		0		0
1		0		0		0		0

 

 

 

1 Reply

@vladi305 

Maybe with Power Query. In the attached file you can add data in the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh.

 

In the example cell A2 is cell number 1 and cell B2 is cell number 2 and so on.

distances.JPG