Jan 12 2023 11:23 AM - edited Jan 12 2023 11:26 AM
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
Jan 12 2023 01:25 PM
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.