Classifying the second largest value if a certain condition is not met

[juansalix]

Hello,

I haven't been able to find a similar topic to this one, so I hope people will find my question interesting. I've uploaded a sample of my data attached to this message.

I've a list of events (cells B to K) with their duration times for a given interval number (cell A). Total time is accounted in cell L. Cell M displays the event having the longest duration for a given interval number. Cell N is the proportion of duration of the longest event relative to the total duration of the interval (80 seconds except for interval #1 which lasts 41 seconds).

I would like to transform my data in the following manner:

1) When "Head-up" is the ONE and ONLY event present in a given interval (example: interval #1 -row 2-), I'd like to have Head-up as my "Event" in column M with its according "Proportion" in cell N (which should be always 1 in this case).

2) When "Head-up" has the longest duration time but is accompanied by other events in a given interval (example: interval #2 -row 3-), I'd like to have the SECOND event with the largest duration time as my "Event" in column M with its according "Proportion" in cell N.

3) When "Head-up" is present in a given interval but there's another event with the longest duration time (example: interval #11 -row12-, "Moving" has the longest duration time), I'd like to have that event that is not "Head-up" with the longest duration time as my "Event" in column M with its according "Proportion" in cell N.

In other words, I'll assume "Head-up" to be my "reference event", and will take the next event with the longest duration time as my "Event" in column M. If none other event other than "Head-up" was recorded in a given interval, then "Head-up" becomes the "Event" in column M.

Hope that was clear enough. Please ask any questions if something needs to be clarified. I think I could use the "if" function for this as I already did to create columns M and N. However, I don't seem able to cover all three conditions as explained earlier.

Hope somebody can put me on the right track! Any input is appreciated.

Cheers

[Bo_Ry]

Please try at
M2
=INDEX(B$1:K$1,MATCH(MAX(B2:K2),B2:K2,))
N2
=MAX(B2:K2)/SUM(B2:K2)

Drag M2:N2 down

[juansalix]

Please try at
M2
=INDEX(B$1:K$1,MATCH(MAX(B2:K2),B2:K2,))
N2
=MAX(B2:K2)/SUM(B2:K2)

Drag M2:N2 down

Thank you for your input.

This seems to work for cells M11 to M17. However, cell M9 should have "Vigilance" in it, cell M10 should have "Moving" etc.. As mentionned in 2) on my original post:

"
2) When "Head-up" has the longest duration time but is accompanied by other events in a given interval (example: interval #2 -row 3-), I'd like to have the SECOND event with the largest duration time as my "Event" in column M with its according "Proportion" in cell N.
"

Any ideas on how to fix this?

[Bo_Ry]

Is this right?

M2
=INDEX(B$1:K$1,MATCH(LARGE(B2:K2,1+(MAX(B2:K2)=C2)-(SUM(B2:K2)=C2)),B2:K2,))

[juansalix]

Is this right?

M2
=INDEX(B$1:K$1,MATCH(LARGE(B2:K2,1+(MAX(B2:K2)=C2)-(SUM(B2:K2)=C2)),B2:K2,))

Perfect that worked! How about N now? I need the proportion of time of the event displayed on the column M relative to the total time (80 seconds except row 2).. Any ideas?

[Bo_Ry]

N2
=index(b2:k2,match(m2,$b$1:$k$1,))/sum(b2:k2)