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

 

[Fluff]

Hi & welcome to MrExcel.
How about imC3 dragged down

=IF(B3=0,"",MATCH(1,B4:B1000,0))

 

[vladi305]

Hi & welcome to MrExcel.
How about imC3 dragged down

=IF(B3=0,"",MATCH(1,B4:B1000,0))

Did you try it because it didn't work on my end

 

[Fluff]

In what way didn't it work?

 

[vladi305]

Did you try it because it didn't work on my end

I'm draggin up on this case to count from C21 to C3 and it didn't find the 1 value, it's marking just 0s

 

[Fluff]

You need to put the formula in C3 & drag down, as I said.

 

[vladi305]

You need to put the formula in C3 & drag down, as I said.

Same thing it's showing just 0 values. Those values at C10 and C22 were entered manually calculating the number of times it took the event to occur. If I drag down it will show different values.

 

[Fluff]

In that case please post a sample of the data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[vladi305]

In that case please post a sample of the data that shows the problem.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks but that excel file is protected it doesn't let me open it

 

[Fluff]

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