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

 

[zacuk]

For anyone who might be interested in it, the final version of the macro can be found at: https://www.mrexcel.com/forum/excel...elp-me-correct-some-minor-issues-macro-2.html

Thanks.