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

Z

zacuk

Board Regular
Joined
Dec 22, 2016
Messages
60
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
 
Sort by date Sort by votes

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG03Jan58
[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.Offset(, CDif).Areas
    [COLOR="Navy"]If[/COLOR] Dn(Dn.Count) - Dn(1) > oMax [COLOR="Navy"]Then[/COLOR]
        oMax = Dn(Dn.Count) - Dn(1)
        [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) = "Group Number Search  :-": Ray(1, 2) = MyMax & " -0.5"
Ray(2, 1) = "Max Time Address := ": Ray(2, 2) = R.Address
Ray(3, 1) = "Max Time Values = " & R(R.Count) & " - " & R(1) & " = ": Ray(3, 2) = R(R.Count) - R(1)
Ray(4, 1) = "Max Number of Groups": Ray(4, 2) = nRng.Areas.Count
[COLOR="Navy"]With[/COLOR] Range("C1").Resize(4, 2)
    .Value = Ray '[COLOR="Green"][B]Change Location to suit[/B][/COLOR]
    .NumberFormat = "@"
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hi Mick,

That's great. It works perfectly. Really appreciate your help.

Thanks a lot.

Zac

PS: I think I should start learning how to create macros. I can write really complex formulas, but not macros yet!
 
Upvote 0
Hi Mick,

The Ray(4, 1) works well, EXCEPT if the very last data point (in column V) is also within the (MyMax - MyMax-0.5) range, in which case it counts 1 more than the actual number of groups.

It's a minor thing and I can live with it, but would appreciate if you can make the macro perfect, please.

Thanks a lot.
 
Upvote 0
I've had a play with my limited data but I can't duplicate your extra count !!!!
Perhaps you could send an example of the list with the extra count .
 
Upvote 0
Hi Mick,

Please copy this data into an excel file. Thanks

Column C
[TABLE="width: 69"]
<colgroup><col></colgroup><tbody>[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]2.0[/TD]
[/TR]
[TR]
[TD]4.0[/TD]
[/TR]
[TR]
[TD]6.0[/TD]
[/TR]
[TR]
[TD]8.0[/TD]
[/TR]
[TR]
[TD]10.0[/TD]
[/TR]
[TR]
[TD]12.0[/TD]
[/TR]
[TR]
[TD]14.0[/TD]
[/TR]
[TR]
[TD]16.0[/TD]
[/TR]
[TR]
[TD]18.0[/TD]
[/TR]
[TR]
[TD]20.0[/TD]
[/TR]
[TR]
[TD]22.0[/TD]
[/TR]
[TR]
[TD]24.0[/TD]
[/TR]
[TR]
[TD]26.0[/TD]
[/TR]
[TR]
[TD]28.0[/TD]
[/TR]
[TR]
[TD]30.0[/TD]
[/TR]
[TR]
[TD]32.0[/TD]
[/TR]
[TR]
[TD]34.0[/TD]
[/TR]
[TR]
[TD]36.0[/TD]
[/TR]
[TR]
[TD]38.0[/TD]
[/TR]
[TR]
[TD]40.0[/TD]
[/TR]
[TR]
[TD]42.0[/TD]
[/TR]
[TR]
[TD]44.0[/TD]
[/TR]
[TR]
[TD]46.0[/TD]
[/TR]
[TR]
[TD]48.0[/TD]
[/TR]
[TR]
[TD]50.0[/TD]
[/TR]
[TR]
[TD]52.0[/TD]
[/TR]
[TR]
[TD]54.0[/TD]
[/TR]
[TR]
[TD]56.0[/TD]
[/TR]
[TR]
[TD]58.0[/TD]
[/TR]
[TR]
[TD]60.0[/TD]
[/TR]
[TR]
[TD]62.0[/TD]
[/TR]
[TR]
[TD]64.0[/TD]
[/TR]
[TR]
[TD]66.0[/TD]
[/TR]
[TR]
[TD]68.0

Column V[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 202"]
<colgroup><col></colgroup><tbody>[TR]
[TD]1.27[/TD]
[/TR]
[TR]
[TD]1.42[/TD]
[/TR]
[TR]
[TD]1.42[/TD]
[/TR]
[TR]
[TD]1.42[/TD]
[/TR]
[TR]
[TD]1.43[/TD]
[/TR]
[TR]
[TD]1.74[/TD]
[/TR]
[TR]
[TD]2.06[/TD]
[/TR]
[TR]
[TD]3.28[/TD]
[/TR]
[TR]
[TD]4.07[/TD]
[/TR]
[TR]
[TD]4.91[/TD]
[/TR]
[TR]
[TD]5.64[/TD]
[/TR]
[TR]
[TD]5.64[/TD]
[/TR]
[TR]
[TD]5.95[/TD]
[/TR]
[TR]
[TD]6.21[/TD]
[/TR]
[TR]
[TD]6.18[/TD]
[/TR]
[TR]
[TD]6.36[/TD]
[/TR]
[TR]
[TD]6.49[/TD]
[/TR]
[TR]
[TD]6.73[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]2.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]10.00[/TD]
[/TR]
</tbody>[/TABLE]

Report

[TABLE="width: 289"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Group Number Search :-[/TD]
[TD]10 -0.5[/TD]
[/TR]
[TR]
[TD]Max Time Address := [/TD]
[TD]$C$33:$C$35[/TD]
[/TR]
[TR]
[TD]Max Time Values = 60 - 56= [/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Max Number of Groups[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

Report (if I replace the last 10 in col V with 2):

[TABLE="width: 289"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Group Number Search :-[/TD]
[TD]10 -0.5[/TD]
[/TR]
[TR]
[TD]Max Time Address := [/TD]
[TD]$C$33:$C$35[/TD]
[/TR]
[TR]
[TD]Max Time Values = 60 - 56= [/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Max Number of Groups[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
I still can't find your problem the code runs OK with me !!
Add the line below as shown, to get count and addresses of groups.
Code: 
Next Dn

[B][COLOR=#ff0000]MsgBox "Group Addresses :- " & nRng.Address & vbLf & "Group Count :-" & nRng.Areas.Count[/COLOR][/B]

For Each Dn In nRng.Offset(, CDif).Areas

The only difference is my Column "C" address in results is C31 To C33, could just be where your data starts in column.
 
Last edited:
Upvote 0
Hi Mick,

Thanks for the "count and addresses of groups" code.

Turns out that there was a row further down the table to which the 'final data' point is copied into, hence another count if the last data point is also a max! hahaha :)

Maybe we need to specify the rows to look at so that there is no interference from any other rows. I am ok without this feature unless you have time to work further on this please.

Thanks once again.
 
Upvote 0
You could perhaps change this:-
Code: 
[COLOR=#000080]Set[/COLOR] Rng = Range(Range("V2"), Range("V" & Rows.Count).End(xlUp))

To This:-
Code: 
[COLOR=#000080]Set[/COLOR] Rng = Range("V2").resize(Rws) 
'Rws being the number of rows you want to look at !!
 
Upvote 0
Hi again Mick,

A couple of issues have arisen with the use of the macro:

(A) I realize that the macro rounds up the max value. So, it'd round up both 9.6 and 10.4 to 10. This, on its own is an issue, because it displays the Max as 10, not as 10.4

The above leads to a second issue:

(B) If the data in column V goes up from 9.6 to 10.4, the macro is unable to count the cells which contain numbers above 10.0 (because, it assumes the Max is 10, after rounding up).

(C) Running the macro, some times return an error message (Run Time Error 91: Object Variable or With Block Variable Not Set). NOTE: I think, this error is generated when only one cell contains maximum! (maybe the macro cannot work out the range, in this case).

Hope you'll be abel to help.

Thanks a lot.

Regards,

Zac
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Excel Formula for Dynamic Timetable with Variable Time Increments based on certain hours of the day
Replies
1
Views
635
Muhammad_Usman
Muhammad_Usman
B
  • Question Question
Check Adjacent Cell (real time)
Replies
5
Views
89
brychu
B
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
698
AOB
AOB
megera716
  • Solved
Calculate maximum of 3 amounts for daily pay but I have the same date on multiple rows
Replies
4
Views
329
megera716
megera716
L
  • Question Question
Creating macro for nested calculations within calculations
Replies
2
Views
126
LizN
L

Forum statistics

Threads
1,223,918
Messages
6,175,365
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top