Calculate the maximum duration for an event that occurs multiple times during a process

[zacuk]

Hi, In my Excel file, column 'V' has data from a process, the values in which peak to around 6.0 from time to time. I would consider all values between 5.5 and 6.0 as the “peak”. Sometime the peak value is maintained for 2 hours before dropping down to below 5.5, but at other times, it can be up for 6 hours or more.

I am interested in finding out the 'Maximum duration' over which the peak value (5.5-6.0) was maintained. So, for example during the entire 60 h period, if the data in column V reached the values 5.5-6.0 ten times, I would like to find out which of these ten incidents covered the longest duration.


I have managed to write a formula, but it works with the highest number only. So, e.g., it would give the duration for which the value stayed it 6.0 and ignores the peaks at 5.9 even if they lasted longer than when the value was at 6.0.

The formula I have made is based on Index/match, using offset function to set the lookup range start at the max value.

I am not much used to <acronym>VBA</acronym>, so a formula would be great (or, a suggestion one what should I do). Otherwise, I'd appreciate if you can write the <acronym>VBA</acronym> code which I paste in the macro box and run it, please.


Thanks

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG23Dec07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("V2"), Range("V" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value >= 5.5 And Dn.Value <= 6 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
    [COLOR="Navy"]If[/COLOR] Dn.Count > oMax [COLOR="Navy"]Then[/COLOR]
        oMax = Dn.Count
        [COLOR="Navy"]Set[/COLOR] R = Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox "Max  Contigious cells of 5.5 to 6 inc = " & oMax & Chr(10) & _
"Address of Same = " & R.Address & Chr(10) & _
"Max Number of Groups, 5.5 to 6 inc =" & nRng.Areas.Count
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zacuk]

Hi Mick,

Thanks for the code.

I copied and pasted it into a new macro, but it gives error upon running it. If I press 'Debug' it highlights: 'For Each Dn In nRng.Areas'

Do you see some error in this line, please?

Secondly, should I replace 'V2' with the first cell of the range in: Set Rng = Range(Range("V2"), Range("V" & Rows.Count).End(xlUp))

The time is in the column A of my worksheet. Is this macro looking at column A, please?

Hope you'll be able to help. Thanks

 

[MickG]

If your times are in column "A", starting "A2", then change the line below (was column "V"), as shown.

[COLOR=#000080]Set[/COLOR] Rng = Range(Range[COLOR=#FF0000]("A2"), [/COLOR]Range("[COLOR=#FF0000]A[/COLOR]" & Rows.Count).End(xlUp))

 

[zacuk]

Thanks a lot Mick.

The good news is, the macro is doing 'something' now

If I use A2 and A in the 'Set Rng' line you mentioned earlier today, the macro returns a message saying it found 1 matching value in column 'A' which has the process time. However, I am interested in the matching values in the 'DATA COLUMN' which is V.

If I use V2 and V in the 'Set Rng' line you mentioned earlier today, the macro returns the correct information for the data in column V (which is great), but I want to find the corresponding duration (calculated based on column A), please.

Would be great if you could help with updating the macro to calculate the time lapsed (in Column A) while the data (in column V) was at the peak (5.5-6). Currently, the macro simply returns the range of the data column. Below is an example of how the date is arranged:

Column A Column V
Time (h) Data
0 0
2.5 2.1
4.3 3.5
5.8 5.8
6.8 6.0
9.0 3.0
12.0 5.7
14.0 5.9
18.5 6.0
20.5 3.1
and so on...

In this case, 12.0 hours to 18.5 hours is the longest duration during which the data stayed between 5.5 and 6, so the output I am interested in is 6.5 hours in this example. Having said that, I like the feature of the macro that it tells how many times there was a situation when the data was between 5.5 and 6 (awesome!).

PS: Is it possible to automate it, so that the macro looks for the MAX value in the column V (instead of 6) and itself creates the data range of MAX-0.5, please? This will help me use the macro on other data where the max value may be more than just 6.

Thanks a lot.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG03Jan16
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] MyMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("V2"), Range("V" & Rows.Count).End(xlUp))
MyMax = Application.Max(Rng)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value >= MyMax - 0.5 And Dn.Value <= MyMax [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
    [COLOR="Navy"]If[/COLOR] Dn.Count > oMax [COLOR="Navy"]Then[/COLOR]
        oMax = Dn.Count
        [COLOR="Navy"]Set[/COLOR] R = Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox "Max sets of Contiguous cells of ""MyMax"" (" & MyMax & "-0.5) = " & oMax & Chr(10) & _
"Column ""A"" Address of Largest set of (" & MyMax & "-0.5) = " & R.Offset(, -21).Address & Chr(10) & _
"Column ""A"" Values = " & R.Offset(, -21)(R.Count) & " - " & R.Offset(, -21)(1) & " = " & R.Offset(, -21)(R.Count) - R.Offset(, -21)(1) & Chr(10) & _
"Max Number of Groups, ""MyMax"" (" & MyMax & " -0.5) = " & nRng.Areas.Count
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zacuk]

PERFECT!

Does exactly what I want to do. Great help.

Is it possible to display the results in cells, please? Currently, it displays a message box which disappears when the Excel file is closed. If all values cannot be displayed, maybe just the duration (calculated from column A) will do.

In some of my worksheets, the Process Time maybe recorded in columns other than A. Will this macro still work if I replace A in the following lines of the macro with 'C' or another column, please?

Dim Rng As Range, Dn As Range, nRng As Range, oMax As Long, R As Range
Dim MyMax As Long




MsgBox "Max sets of Contiguous cells of ""MyMax"" (" & MyMax & "-0.5) = " & oMax & Chr(10) & _
"Column ""A"" Address of Largest set of (" & MyMax & "-0.5) = " & R.Offset(, -21).Address & Chr(10) & _
"Column ""A"" Values = " & R.Offset(, -21)(R.Count) & " - " & R.Offset(, -21)(1) & " = " & R.Offset(, -21)(R.Count) - R.Offset(, -21)(1) & Chr(10) & _
"Max Number of Groups, ""MyMax"" (" & MyMax & " -0.5) = " & nRng.Areas.Count
</pre>

 

[MickG]

Try this:-
NB:- You will now need to enter in "InputBox" the "Time" column as NUMBER (Was Colum "A"=1)
Results start "C1" change address, in code As required.(see Code Remarks)

[COLOR="Navy"]Sub[/COLOR] MG03Jan26
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, nRng [COLOR="Navy"]As[/COLOR] Range, oMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] MyMax [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] CDif [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
col = Application.InputBox(prompt:="Please Enter ""Time"" Column Number ", Title:="Time column", Type:=1)
[COLOR="Navy"]If[/COLOR] col = 0 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
CDif = col - 22
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("V2"), Range("V" & Rows.Count).End(xlUp))
MyMax = Application.Max(Rng)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value >= MyMax - 0.5 And Dn.Value <= MyMax [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Set[/COLOR] nRng = Dn Else [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng.Areas
    [COLOR="Navy"]If[/COLOR] Dn.Count > oMax [COLOR="Navy"]Then[/COLOR]
        oMax = Dn.Count
        [COLOR="Navy"]Set[/COLOR] R = Dn
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
ReDim Ray(1 To 4, 1 To 2)
Ray(1, 1) = "Max Group size (" & MyMax & "-0.5) :-": Ray(1, 2) = oMax
Ray(2, 1) = "Time Column Address := ": Ray(2, 2) = R.Offset(, CDif).Address
Ray(3, 1) = "Time Values": Ray(3, 2) = R.Offset(, CDif)(R.Count) & " - " & R.Offset(, CDif)(1) & _
" = " & R.Offset(, CDif)(R.Count) - R.Offset(, CDif)(1)
Ray(4, 1) = "Max Number of Groups": Ray(4, 2) = nRng.Areas.Count
Range("C1").Resize(4, 2).Value = Ray '[COLOR="Green"][B]Change Location to suit[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zacuk]

Thanks Mick. Got it. Cool stuff.

It works, but the output in this macro is determined by the number of consecutive cells containing the Max-0.5 values. Whereas, the output should be determined based on the 'Longest Duration' (i.e., in the time column). So, for example, if the Max-0.5 value (in column V) was maintained for 4 hours in one group of cells, and for 6 hours in another group of cells, the macro should return the results corresponding to the latter group (with 6 h duration), irrespective of the group sizes.

Sorry to bother you again

 

[MickG]

So, If I create ranges of all the groups in column "V" that are "Max-0.5", then look in the comparative "Time" column ranges, taking the first cell in each group, "Time" ranges from the last cell in each group "Time" range, thereby finding its duration, then from these times, find the largest to give you your result.
Does that sound OK