Find time associated with half-height

[jtillema]

Hi everyone,

First post here, so be gentle. I'm analyzing scientific data of a pulse, which has an asymmetric shape. The data I'm trying to analyze is time vs. signal. I've learned by searching this forum how to find the time associated with max signal. Now I'd like to find the time at 1/2 and 1/4 the signal. I've tried adapting the formula that I learned here to do that, but I can't get it to work correctly. Need some help.

Here's the formula to find time at peak height.

=INDEX(D1:D200,MATCH(MAX(E1:E200),E1:E200,0))

I tried to modify it to find 1/2 by doing

=INDEX(D1:D200,MATCH(0.5*MAX(E1:E200),E1:E200,0))

but I get weird values or an error. The 0 at the end makes it exact, which causes a problem, so I tried a 1 and -1, but that didn't help in all cases. Since the data is a spike, there will be 2 times associated with half the max, and I only need the one on the downswing. I tried changing the range to something like

=INDEX(D100:D200,MATCH(0.5*MAX(E1:E200),E100:E200,0))

but again, I'll get either an error or the wrong answer.

Need some help.

[shg]

Welcome to the forum.

Post a workbook and explain in context?

[jtillema]

Actually, for some reason it's working now. But now I have a new issue. Since I'm only interested in the value associated with decay, the range must be variable. When I find the address of the cell containing the max using

=MATCH(MAX(N1:N200),N1:N200,0)

I get the correct line number. In this case, it's line 93. When I manually enter 93 into my function, I get the correct answer.

=INDEX(M93:M201,MATCH(0.5*MAX(N1:N201),N93:N201,-1))

When I try to specify a variable range with

=INDEX(M93:M200,MATCH(0.5*MAX(N1:N200),"M"&K9&":M200",-1))

I get a value error.

I checked the calculation steps and found

INDEX($M:$93:$M$200,MATCH(0.63665,"M93:M200",-1))

For some reason, there are quotes around the cell range. Everything else looks right to me, but how do I get rid of the quotes?

[daddylonglegs]

You can use INDEX again to get the cell reference you want, e.g.

=INDEX(N1:N201,K9)

on it's own that just gives the value in N93 but used within your formula it will give a cell reference, i.e.

=INDEX(INDEX(M1:M201,K9):M201,MATCH(0.5*MAX(N1:N201),INDEX(N1:N201,K9):N201,-1))

[jtillema]

Thanks. That works well except for the case where there is no 1/2 height. e.g. The curve rises but does not fall to half its max height. In this case I actually get a number, when I shouldn't. The number happens to be the value in cell M201.

[daddylonglegs]

That's just how MATCH works, it matches with the smallest value that's greater than or equal to the lookup value (with -1 as 3rd argument), so if 0.5*MAX(N1:N201) is smaller than everything in the match range it'll match with the smallest, i.e. N201

You could use an IF function to avoid that, e.g.

=IF(0.5*MAX(N1:N201)< N201,"No 1/2 height",INDEX(INDEX(M1:M201,K9):M201,MATCH(0.5*MAX(N1:N201),INDEX(N1:N201,K9):N201,-1)))

[jtillema]

OK, but when I use

=INDEX(M93:M201,MATCH(0.5*MAX(N1:N201),N93:N201,-1))

I get #N/A for the cases when there is no half height.