Returning values in a Row period

[sajeel]

Working on a time series signal change. Need to find out what the value of the 5 interval (Holding period) is when the value becomes greater than zero. However the values returned each time should be after a signal change of the holding period. In the excel sheet attatched at Time= 1, signal=0, at Time=2, signal=2.99. So this value and 5 from there should be returned.Further down in time next signal is at Time=14, where signal=8.98. So the 5th value is 4.19. Now we only take the next value after that specific holding period. In this case this occurs at Time= 19 signal=6.527. So this value occured after the previous 5 interval holding period

[Steve R]

Everything you are describing in your post is happening on your excel workbook attachment?

Need to find out what the value of the 5 interval (Holding period) is when the value becomes greater than zero.

Donīt you find the "fifth" value with your workbook formula?
=IF(AND(B18=0,B19>0),INDEX(B19:B29,$F$2,1),FALSE)

Can you create a workbook with the output values you want to be calculated?

[DonkeyOte]

sajeel, does the attached help ?

[sajeel]

sajeel, does the attached help ?

This is definitely what I am looking for. Is it possible to also pick up values in the time series if they are negative values. i.e instead of positive values if I could use the same principle with negative values. I seem to try it and my excel sheet keeps falling over. Thanks

[DonkeyOte]

Not entirely sure I follow but using the example file I posted .. to collate values based on a non-zero rule (rather than >0)

H4: =LOOKUP(2,1/($B$6:$B$455<>0),ROW($B$6:$B$455))

H6: =IF(OR($H5=$H$4,$H5=""),"",MIN(IF((B$6:B$455)*(ROW(B$6:B$455)>=($F$2+N($H5))),ROW(B$6:B$455))))
committed with CTRL + SHIFT + ENTER
copied down as required (in sample to H48)

The remaining formulae in I & J can be left as before.

[sajeel]

In the example file you sent me, we were only picking up positive values >0. If in the signal column i.e Column B we had negative values, how would I change the formula's. For instance in the attachment Column B has values:

0
2.99177
0
0
0
0
etc with positive values in the column

If these values were negative, how would I detect them? Thanks

[DonkeyOte]

sajeel, I believe I have already answered this in the prior post.

[sajeel]

Thanks a lot for your help. I really appreciate it!