# How to calculate distance between values



## vladi305 (Thursday at 2:43 PM)

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 (Thursday at 2:51 PM)

Hi & welcome to MrExcel.
How about imC3 dragged down

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


----------



## vladi305 (Thursday at 2:57 PM)

Fluff said:


> Hi & welcome to MrExcel.
> How about imC3 dragged down
> 
> ```
> ...


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


----------



## Fluff (Thursday at 3:02 PM)

In what way didn't it work?


----------



## vladi305 (Thursday at 3:08 PM)

vladi305 said:


> 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 (Thursday at 3:11 PM)

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


----------



## vladi305 (Thursday at 3:18 PM)

Fluff said:


> 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 (Thursday at 3:19 PM)

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 (Thursday at 3:30 PM)

Fluff said:


> 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 (Thursday at 3:32 PM)

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


----------



## vladi305 (Thursday at 2:43 PM)

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 (Thursday at 3:36 PM)

Fluff said:


> 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 (Thursday at 3:38 PM)

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


----------

