1

I'm currently working on an evaluation excel sheet for forceplate data (showing vertical force development in jumps over time) and stumbled upon a problem that I couldn't manage to fix for the past days. Basically there are two main columns over ~ 4000 rows and 1 extra cell:

Column A shows time [in ms]

Column B shows vertical force measured at the time point in Column A

C1 is the already calculated peak force value before takeoff

I am now trying to define the timepoint of takeoff in an extra cell using INDEX and MATCH functions (FYI: the time of takeoff is when the vertical force value is close to 0 for the first time [range of lookup must be starting from the peak force value though!!], but never exactly 0 due to force plate drift in measurement)

My idea was this:

=INDEX(A2:A4000;MATCH(0;INDEX(B2:B4000;MATCH(C1;B2:B4000;0)):B4000;-1))

so the range

INDEX(B2:B4000;MATCH(C1;B2:B4000;0)):B4000

should define a range of force values starting at the peak force value (C1).

Unfortunately Excel will show me a timepoint where the force value is far away from 0. I've tried the same formula within an easier (but for my purpose faulty) range (B2:B4000) and it worked perfectly, so I guess the problem I'm dealing with lies somewhere within the range defined with the INDEX function. I'd be glad if someone could help me out with this!

Ram
  • 3,092
  • 10
  • 40
  • 56
Schmendlow
  • 21
  • 3
  • 4

1 Answers1

0

You are certainly on the right track. It seems you've correctly adjusted the range in the nested INDEX function but that MATCH function will retunr the position within the adjusted B2:B4000. You need to adjust A2:A4000 in the same way so that the position returned by MATCH will be correct.

=INDEX(INDEX(A2:A4000; MATCH(C1; B2:B4000; 0)):A4000; MATCH(0; INDEX(B2:B4000; MATCH(C1; B2:B4000; 0)):B4000; -1))

I don't have sample data to test that on but I believe it is correct.