Find Leading and Lagging times from data array.

[scoutzilla]

Greetings,
I’ve struggled with this for far too long, and decided to ask for help.
I need the Leading and Lagging Times (column B) for the data shown in E1:J12. My set point is 265, so for the data snip shown below, the leading time would be 240, and the Lagging time would be 280.
Thanks for any help!

A	B	C	D	E	F	G	H	I	J
1	230.8	5.4	275.1	252.7	246	263.4	249	245.2	253.1
2	235	5.4	275.9	255	248.5	264.7	251.6	247.8	255.2
3	240	5.5	275.8	257.4	251.2	266.2	254.5	250.6	257.5
4	245	5.5	276.1	259.7	253.8	267.5	256.9	253.2	259.5
5	250	5.5	276.1	261.8	256	268.5	258.8	255.5	261.2
6	255	5.5	276	263.7	258.1	269.5	260.5	257.7	262.8
7	260	5.5	276.1	265.2	259.9	270.3	262.4	259.7	264.4
8	265	5.5	276.1	266.6	261.6	271.1	264	261.2	265.6
9	270	5.5	276.2	267.9	263.1	271.7	265.4	262.9	266.8
10	275	5.5	276.2	269.1	264.4	272.4	266.7	264.3	267.9
11	280	5.5	276.2	270.3	265.6	272.8	268.2	265.6	268.9
12	285	5.5	276.3	271.6	266.7	273.4	26936	266.6	269.7

[Jacc]

I (and possibly some others as well) have no idea what leading and lagging time means, do you mind explaining? If you post a workbook with sample data it will attract more attention here on the forum.

[scoutzilla]

My apologies for not being clear enough, The Data shown in columns E-J is from temperature channels. My Temperature Set Point is 265.

For the Leading Channel I’m looking for the first channel to reach (or exceed) 265 this would be Channel G at the time of 240 seconds.
For the Lagging Channel I’m looking for the last channel to reach (or exceed) 265 this would be Channel F at the time of 280 seconds.

PS the last entry in column H is a typo..
Thanks

[Jacc]

This is an array formula, it needs to be entered with Ctrl + Shift + Enter.

=INDEX($F$5:$F$16;MIN(IF($I$5:$N$16>$F$20;ROW($I$5:$N$16)))-ROW($I$5:$N$5)+1)

[scoutzilla]

Nice a big help I made a few adjustments In Cell N2 I enter my Set Point (264.5) I can get my Leading value by:

=INDEX($B$2:$B$13,MIN(IF($E$2:$J$13>$N$2,ROW($E$2:$J$13)))-ROW($E$1:$J$1)+1)

and even the Column Letter for the leading value by:

=INDEX($E$1:$J$1,MIN(IF($E$2:$J$13>$N$2,ROW($E$2:$J$13)))-ROW($E$1:$J$1)+1)

For the Lagging value I get the correct value by:

=INDEX($B$2:$B$13,MAX(IF($E$2:$J$13<$N$2,ROW($E$2:$J$13)))-ROW($E$1:$J$1)+1)

However for the lagging column letter I enter:

=INDEX($E$1:$J$1,MAX(IF($E$2:$J$13<$N$2,ROW($E$2:$J$13)))-ROW($E$2:$J$2)+1)

But get #REF!

While troubleshooting I entered a 1 and 2 as test values in this formula and get the correct column letter but do not understand how it is working:

=INDEX($E$1:$J$1,MAX(IF($E$2:$J$13<$N$2,1,2)))

Cheers

[Jacc]

I don't see how your column letter formulas will work. Mine became quite long, if we are lucky someone else will show up and write something smarter.

=INDEX($I$4:$N$4;MATCH(MAX(INDEX($I$5:$N$16;MIN(IF($I$5:$N$16>$F$20;ROW($I$5:$N$16)))-ROW($I$4:$N$4);0));INDEX($I$5:$N$16;MIN(IF($I$5:$N$16>$F$20;ROW($I$5:$N$16)))-ROW($I$4:$N$4);0);0))

[Jacc]

Dunno why I didn't think of using the Lead Lag numbers in the column letter formula before. It makes the formulas a bit shorter.

[scoutzilla]

Nice examples, let me ponder your work, looks very promising!!
)

[scoutzilla]

I think I can get your solution to work, with 1 small change. The size of my data array is not fixed, so there will be empty cells (columns and rows) to evaluate. Using your formula for Lead, this does not seem to be a problem, however to find Lag, it seems not to work with blank or empty cells.

Ideas?
Cheers!

[Jacc]

I deleted one of the cells in the middle of the range and like you said, Lag failed.

However, this was not due to my formula, rather it was due to the random test number formula. It generates a random number and then adds the content of the cell above to that. If the cell above is empty it means that the accumulated random numbers before that is lost and the process of summing random numbers starts from scratch again. Because it starts from scratch it never reaches the setpoint before it comes to the end of the table and then the formula errors.

Could this be your problem? In my example you can find out by deleting a cell high up in the table. Most likely that column will then be green all the way to the end of the table. If you lower the setpoint the error clears.

Edit: Lag, not lead

[scoutzilla]

Again I was not clear, and again thank you for your efforts.
When I said

The size of my data array is not fixed, so there will be empty cells (columns and rows) to evaluate.

I should have been clearer, that the width and length of my data array will change, meaning I will have blank Columns and Rows (apx 50 Columns, so I took my width out to Column BX to be safe, for the length I go down to Row 300).
When I edit your Lag Formula:

=INDEX($F$5:$F$16,MAX(IF($I$5:$N$16<=$F$20,ROW($I$5:$N$16)))-ROW($I$5:$N$5)+2)

(picking up an empty Column and Row) to:

=INDEX($F$5:$F$17,MAX(IF($I$5:$O$17<=$F$20,ROW($I$5:$O$17)))-ROW($I$5:$O$5)+2)

I get #REF!
Can't seem to get your Lag formula to ignore blank cells..

Cheers and Happy Friday!!

[Jacc]

If you post a workbook with more extensive data showing the problem it will be much easier for me to fix it.

[scoutzilla]

Here you go!

[Jacc]

Tried the Excel Table feature here. However, since the data is decreasing in the lower part of the table the formulas won't work anyway.
Had to make UDF's. Still Table Feature. Seems to work well.

Option Explicit
Dim c As Range
Dim m As Integer
Dim n As Integer
Dim clag() As Variant

Function LeadRow(SetPoint As Range, DataTable As Range, TimeColumn As Range) As Variant
    For Each c In DataTable.Cells
        If c.Value > SetPoint Then
            LeadRow = Intersect(c.EntireRow, TimeColumn).Value
            Exit Function
        End If
    Next c
End Function

Function LeadColumn(SetPoint As Range, DataTable As Range, HeaderRow As Range) As Variant
    For Each c In DataTable.Cells
        If c.Value > SetPoint Then
            LeadColumn = Intersect(c.EntireColumn, HeaderRow).Value
            Exit Function
        End If
    Next c
End Function

Function LagRow(SetPoint As Range, DataTable As Range, TimeColumn As Range) As Variant
ReDim clag(1 To DataTable.Columns.Count)
With DataTable
    For m = 1 To .Rows.Count
        For n = 1 To .Columns.Count
            If .Cells(m, n).Value > SetPoint Then
                clag(n) = 1
                If WorksheetFunction.Sum(clag) >= .Columns.Count Then
                    LagRow = Intersect(.Cells(m, n).EntireRow, TimeColumn).Value
                    Exit Function
                End If
            End If
        Next n
    Next m
End With
End Function

Function Lagcolumn(SetPoint As Range, DataTable As Range, HeaderRow As Range) As Variant
ReDim clag(1 To DataTable.Columns.Count)
With DataTable
    For m = 1 To .Rows.Count
        For n = 1 To .Columns.Count
            If .Cells(m, n).Value > SetPoint Then
                clag(n) = 1
                If WorksheetFunction.Sum(clag) >= .Columns.Count Then
                    Lagcolumn = Intersect(.Cells(m, n).EntireColumn, HeaderRow).Value
                    Exit Function
                End If
            End If
        Next n
    Next m
End With
End Function

[scoutzilla]

Wow! Nice work!
I tried to figure out how to edit the UDF to output the Time Elapsed values vs the HH:MM:SS values, the range in the Name Manager was grayed out! So I have a few Questions.

How do I make a change so the Output is Time Elapsed values vs HH:MM:SS? or can you make that change?

Can you also add in a duplicate UDF so the worksheet shows Lead and Lag for both Set Points? I'm going to use these values to Plot some limits based on Lead and Lag for both set points.

Well after a bit I was able to work around the above, My new Problem is when I have fewer Columns of data I get lag times of 0. A real big challenge, for me at least, is having my workbook set up for different array sizes.

Nice to be making progress, thanks to you!!

[Jacc]

Wow! Nice work!
I tried to figure out how to edit the UDF to output the Time Elapsed values vs the HH:MM:SS values, the range in the Name Manager was grayed out! So I have a few Questions.

How do I make a change so the Output is Time Elapsed values vs HH:MM:SS? or can you make that change?

Can you also add in a duplicate UDF so the worksheet shows Lead and Lag for both Set Points? I'm going to use these values to Plot some limits based on Lead and Lag for both set points.

Nice to be making progress, thanks to you!!

You are welcome.
If you want to copy the worksheet it's no problem. The UDF is just like any other function, you can just drag to fill down and get all the setpoints you want.

I expanded the blue and white table by selecting a cell within it, then clicking on DESIGN under TABLE TOOLS at the top middle of your screen. Then to the very left you find the Resize Table command.

Instructions to change to Time Elapsed is in the workbook.

[scoutzilla]

I will take a look at this one thanks a bunch!!

Argh! so close, When I have fewer columns of data I get 0's for Lag row and Lag col.

Most of my data will only have ~ 12 columns.

Cheers!

[scoutzilla]

Just wondering if it is possible to get your work sheet tweaked a bit to allow for different sets of data. The number of columns in my data change from time to time. I can't seem to extract Lag data when/if with different data sets.

Thanks!

[Jacc]

I'm not sure what you mean.
My lead/lag functions are just like any other Excel function, you just type = and the name of the function and then select the ranges you want for input. There is no help function so you have to look at the ones implemented to see which range goes where.

[scoutzilla]

Your LeadRow function works perfectly, with different array sizes.
Here is an example, I resized my data, with blank cells From Column O all the way out to the right, and Blank Cells from Row 96 down.
The results of:

=LeadRow(A6,$M$8:$BZ$300,$J$8:$J$300)

Are correct. The function seems to ignore blank cells.

For the LagRow function using the same data as above, the formula:

=LagRow(A6,$M$8:$N$300,$J$8:$J$300)

Is correct.

However when I change the formula to include blank columns as:

=LagRow(A6,$M$8:$BZ$300,$J$8:$J$300)

I get the value of 0, If the function finds blank columns I get a Zero.

And again thanks for your help!

[Jacc]

Oh, I see. Same thing we discussed before then.

I updated both Lag functions, just one more row of code was needed. You have to do the testing, I'm too lazy for that.
They got new names too, they now end with 2.

[scoutzilla]

Awesome work! Very Cool!! Thank you Jacc!!!

[Jacc]

'''''''''''''