Dynamic Range in formula

[mikejvir]

Hello,

I have two columns of data. The first column is time and is monotonic. The second column has data that is sinusoidal in nature. If I determine the time when there is a zero axis crossing, going from negative to positive, I can calculate the period of the signal between any two of these crossings.

A simple technique is to copy the column and paste values only into another column and then do an ascending sort. The issue I have is I will be doing this for many datasets. So I am attempting to have Excel do this for me. Since I can’t post the spreadsheet, here is a sample of the data.

Col | A | B | C | D | E | F
----------------------------------------------------------
Row | Time | | Voltage | Cross |Index| TimeValue
| | | | | 0 |
4 | -100.008 | | 0.08657 | | 88 | -97.789
5 | -99.983 | | 0.07350 | | 168 | -93.604
6 | -99.958 | | 0.06170 | | 88 | -93.604
7 | -99.933 | | 0.05232 | | 168 | -93.604
8 | -99.908 | | 0.04125 | | 88 | -93.604
9 | -99.883 | | 0.02913 | | 168 | -93.604

91 | -97.833 | | -0.02165 | | |
92 | -97.808 | | -0.00848 | -97.789 | |
93 | -97.783 | | 0.00247 | | |
94 | -97.758 | | 0.01322 | | |

259 | -93.633 | | -0.01396 | | |
260 | -93.608 | | -0.00205 | -93.604 | |
261 | -93.583 | | 0.00985 | | |
262 | -93.558 | | 0.02070 | | |

425 | -89.483 | | -0.02787 | | |
426 | -89.458 | | -0.01849 | | |
427 | -89.433 | | -0.00595 | -89.418 | |
428 | -89.408 | | 0.00426 | | |
429 | -89.383 | | 0.01027 | | |

(I thought I aligned everything with the Courier New text, by formatting in Word first, the setting the font here. Guess it did not work ).
Columns A and C are the data.

Column D is where I determine if I have axis crossing with the following formula

Cell D4 =IF(AND(C4<0,C5>0),A4+(A5-A4)*((0-C4)/(C5-C4)),"")

I also use linear interpolation to find the exact time of the crossing.

I then try to find the row for the next crossing by using the equation, which I found here at MrExcel.

Cell E4 =MATCH(TRUE,INDEX(ISNUMBER(OFFSET(D4,1+E3,0,1000,1)),0),0)

I use 1000 for the row height because I know it will always be within this range. I then put the value in Column F

Cell F4 =OFFSET(D4,E4,0,1,1)

I copied to the next cell

Cell E5 =MATCH(TRUE,INDEX(ISNUMBER(OFFSET(D5,1+E4,0,1000,1)),0),0)

D5 should be D93, the row after the next crossing. I used another offset within the ISNUMBER function for this as

Cell E5 =MATCH(TRUE,INDEX(ISNUMBER(OFFSET(OFFSET($D$4,E4,0,1,1),1,0,1000,1)),0),0)

And I got the data which is great. The issue is the values after that are incorrect. I would expect the data in cell E6 to be 260, not 87.

I have been staring at this for to long to see the simple mistake I am making (Writing this has help resolve some of the problems to this point).

Thanks for the help and sorry for the long winded question.

Michael J. Virostko

 

[Johnny C]

I'm guessing that this is because MATCH is looking down from the current cell. Try
Cell E5 =MATCH(TRUE,INDEX(ISNUMBER(OFFSET(OFFSET($D$4,E4+MAX(E$1:E3),0,1,1),1,0,1000,1)),0),0)

 

[mikejvir]

Hi Johnny,

Thanks for the suggestion, but that did not work.

But you made me realized my simple mistake . I reset the reference cell therefore Column E only as the offset. I solved the problem by using the sum function


Cell E5 =MATCH(TRUE,INDEX(ISNUMBER(OFFSET(OFFSET($D$4,SUM(E$3:E4),0,1,1),1,0,1000,1)),0),0)

Now I get all the correct data.

Thanks again.

Michael J. Virostko