Forum Discussion
How to calculate distance between values
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
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.
