Identifying the cell each time that a number crosses a threshold

[BenO28]

Hello, I am looking at trying to create a couple of formulas that will automatically identify each time that a person lands or takes off during a hop which I will then create a macro for to run simultaneously. As it stands I have to manually scroll down a sheet which has circa 20,000 rows and it is quite time-consuming. I have attached an image. So far I have managed to use index and match, however this only seems to identify one point as opposed to repeated ones throughout the sheet. What I would like to do is:

• I need to identify the relevant timepoints (Column B) for each time that the force crosses below the 20N force threshold so that I can calculate the frequency of each hop. (one full hop = every time the force falls below the 20N threshold in the force column.)

• In the blank table in the column that is titled vto, automatically populate the data based on the formula =(9.81*(TimeLand-TimeTO))/2) for each hop throughout the sheet (each time the force data

• In the table that is titled Hhop, automatically populate the data based on the formula =vto/9.81 for each hop

Can anybody help? If anyone can and needs further information, please ask! Many thanks! Ben

 

[FatBoyClam]

Can you add another column to the table of Hops?

It would make it very simple if you could add a column with a formula that identifies the row as being a 'hop' and the ordinal of that hop. Then your blank table just looks to that column for each row.