Formula displaying max ranges?

yessir

Board Regular
Joined
Jun 7, 2019
Messages
103
Office Version
  1. 2021
Platform
  1. MacOS
Is there a formula that knows the max number in the range and is able to then display the range of times corrosponding to the Max number within that row. For example, in the example below it would return 5:11-5:13 as those are the times where the value underneath is the highest in the row. And the formula would be able to return the same thing for the row below it as well.

[TABLE="width: 672"]
<colgroup><col width="64" style="width: 48pt;" span="14"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]MAX #[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5:05[/TD]
[TD="bgcolor: transparent, align: right"]5:06[/TD]
[TD="bgcolor: transparent, align: right"]5:07[/TD]
[TD="bgcolor: transparent, align: right"]5:08[/TD]
[TD="bgcolor: transparent, align: right"]5:09[/TD]
[TD="bgcolor: transparent, align: right"]5:10[/TD]
[TD="bgcolor: transparent, align: right"]5:11[/TD]
[TD="bgcolor: transparent, align: right"]5:12[/TD]
[TD="bgcolor: transparent, align: right"]5:13[/TD]
[TD="bgcolor: transparent, align: right"]5:14[/TD]
[TD="bgcolor: transparent, align: right"]5:15[/TD]
[TD="bgcolor: transparent, align: right"]5:16[/TD]
[TD="bgcolor: transparent, align: right"]5:17[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Not saying I'll asnwer this but what happens if the maximum value occurs again in the row?
E.g.

5:05 5:06 5:07 5:08 5:09 5:10 5:11 5:12 5:13 5:14 5:15 5:16 5:17
xxxx 0 1 1 2 2 2 0 0 0 2 2 1 1

So the answer would be
maximum 2
ranging from 5:08-5:10 AND 5:14-5:15
 
Upvote 0
Not saying I'll asnwer this but what happens if the maximum value occurs again in the row?
E.g.

5:05 5:06 5:07 5:08 5:09 5:10 5:11 5:12 5:13 5:14 5:15 5:16 5:17
xxxx 0 1 1 2 2 2 0 0 0 2 2 1 1

So the answer would be
maximum 2
ranging from 5:08-5:10 AND 5:14-5:15


You are absolutly correct!

(using max formula to calculate max for given row)
[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]MAX #
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 672"]
<colgroup><col width="64" style="width: 48pt;" span="14"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:05[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:06[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:07[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:08[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:09[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:10[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:11[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:12[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:13[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:14
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:15
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:16
[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]5:17[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]


In this case xxxx the max would be 3, and the corresponding times would be from 5:11-5:13.
And in the case of yyyy the max would be 2, the times being 5:07-5:08 and 5:13-5:15.

Thank you and any help is very much appreciated!!
 
Upvote 0
Highlighting the max values would be an acceptable solution?
Can be easily done with Conditional Formatting.

M.
 
Upvote 0
Highlighting the max values would be an acceptable solution?
Can be easily done with Conditional Formatting.

M.

[TABLE="width: 672"]
<colgroup><col width="64" style="width: 48pt;" span="14"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]MAX #[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]5:05[/TD]
[TD="bgcolor: transparent, align: right"]5:06[/TD]
[TD="bgcolor: transparent, align: right"]5:07[/TD]
[TD="bgcolor: transparent, align: right"]5:08[/TD]
[TD="bgcolor: transparent, align: right"]5:09[/TD]
[TD="bgcolor: transparent, align: right"]5:10[/TD]
[TD="bgcolor: transparent, align: right"]5:11[/TD]
[TD="bgcolor: transparent, align: right"]5:12[/TD]
[TD="bgcolor: transparent, align: right"]5:13[/TD]
[TD="bgcolor: transparent, align: right"]5:14[/TD]
[TD="bgcolor: transparent, align: right"]5:15[/TD]
[TD="bgcolor: transparent, align: right"]5:16[/TD]
[TD="bgcolor: transparent, align: right"]5:17[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"]Ideal solution would be where it returned times in different cell set (Like below)[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Time(s) of Max[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]xxxx[/TD]
[TD="bgcolor: transparent"]5:11-5:13[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]yyyy[/TD]
[TD="bgcolor: transparent, colspan: 2"]5:07-5:08,5:13-5:15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
Although I do see how conditonal formatting would highlight the times when at max.
 
Upvote 0
Is there a way to go about this without using conditonal formatting?
 
Upvote 0
You need VBA - maybe an User Defined Function (UDF)
I'm busy now - in the meantime maybe someone can help.

M.
 
Upvote 0
A possible solution with a UDF

Alt+F11 to open the VBEditor
Menu
Insert > Module
Paste the code below in the right-panel

Code:
Function MaxTimes(rTimes As Range, rData As Range)
    Dim lCol As Long, strAux As String, bBreak As Boolean
    Dim sFirst As String, sLast As String
    
    For lCol = 1 To rTimes.Columns.Count
        If rData.Cells(1, lCol) = Application.Max(rData) Then
            If sFirst = "" Then
                sFirst = Format(rTimes.Cells(1, lCol), "h:mm")
                sLast = sFirst
            Else
                sLast = Format(rTimes.Cells(1, lCol), "h:mm")
            End If
            bBreak = (lCol = rData.Columns.Count)
        Else
            bBreak = True
        End If
        
        If bBreak And sFirst <> "" Then
            strAux = strAux & ", " & sFirst & IIf(sFirst = sLast, "", "-" & sLast)
            sFirst = ""
            sLast = ""
        End If
    Next lCol
    MaxTimes = Mid(strAux, 3)
End Function

Back to Excel

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[TD="bgcolor: #DCE6F1"]
G
[/TD]
[TD="bgcolor: #DCE6F1"]
H
[/TD]
[TD="bgcolor: #DCE6F1"]
I
[/TD]
[TD="bgcolor: #DCE6F1"]
J
[/TD]
[TD="bgcolor: #DCE6F1"]
K
[/TD]
[TD="bgcolor: #DCE6F1"]
L
[/TD]
[TD="bgcolor: #DCE6F1"]
M
[/TD]
[TD="bgcolor: #DCE6F1"]
N
[/TD]
[TD="bgcolor: #DCE6F1"]
O
[/TD]
[TD="bgcolor: #DCE6F1"]
P
[/TD]
[TD="bgcolor: #DCE6F1"]
Q
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD][/TD]
[TD]
MAX #​
[/TD]
[TD]
Result​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD]
xxxx​
[/TD]
[TD]
3​
[/TD]
[TD]
5:11-5:13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD]
yyyy​
[/TD]
[TD]
5​
[/TD]
[TD]
5:07-5:08, 5:13-5:15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD]
zzzz​
[/TD]
[TD]
7​
[/TD]
[TD]
5:05, 5:07-5:09, 5:14-5:15, 5:17​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
5:05​
[/TD]
[TD]
5:06​
[/TD]
[TD]
5:07​
[/TD]
[TD]
5:08​
[/TD]
[TD]
5:09​
[/TD]
[TD]
5:10​
[/TD]
[TD]
5:11​
[/TD]
[TD]
5:12​
[/TD]
[TD]
5:13​
[/TD]
[TD]
5:14​
[/TD]
[TD]
5:15​
[/TD]
[TD]
5:16​
[/TD]
[TD]
5:17​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
xxxx​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
yyyy​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
2​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
zzzz​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD]
0​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down (adjust the ranges according your data)
=MaxTimes(E$7:Q$7,INDEX(E$8:Q$10,MATCH(A2,D$8:D$10,0),0))

Save the file as .xlsm

Hope this helps

M.
 
Last edited:
Upvote 0
A possible solution with a UDF

Alt+F11 to open the VBEditor
Menu
Insert > Module
Paste the code below in the right-panel

Code:
Function MaxTimes(rTimes As Range, rData As Range)
    Dim lCol As Long, strAux As String, bBreak As Boolean
    Dim sFirst As String, sLast As String
    
    For lCol = 1 To rTimes.Columns.Count
        If rData.Cells(1, lCol) = Application.Max(rData) Then
            If sFirst = "" Then
                sFirst = Format(rTimes.Cells(1, lCol), "h:mm")
                sLast = sFirst
            Else
                sLast = Format(rTimes.Cells(1, lCol), "h:mm")
            End If
            bBreak = (lCol = rData.Columns.Count)
        Else
            bBreak = True
        End If
        
        If bBreak And sFirst <> "" Then
            strAux = strAux & ", " & sFirst & IIf(sFirst = sLast, "", "-" & sLast)
            sFirst = ""
            sLast = ""
        End If
    Next lCol
    MaxTimes = Mid(strAux, 3)
End Function

Back to Excel

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
A
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
B
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
C
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
D
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
E
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
F
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
G
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
H
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
I
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
J
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
K
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
L
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
M
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
N
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
O
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
P
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
Q
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
1
[/TD]
[TD][/TD]
[TD]
MAX #​
[/TD]
[TD]
Result​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
2
[/TD]
[TD]
xxxx​
[/TD]
[TD]
3​
[/TD]
[TD]
5:11-5:13​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
3
[/TD]
[TD]
yyyy​
[/TD]
[TD]
5​
[/TD]
[TD]
5:07-5:08, 5:13-5:15​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
4
[/TD]
[TD]
zzzz​
[/TD]
[TD]
7​
[/TD]
[TD]
5:05, 5:07-5:09, 5:14-5:15, 5:17​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
5:05​
[/TD]
[TD]
5:06​
[/TD]
[TD]
5:07​
[/TD]
[TD]
5:08​
[/TD]
[TD]
5:09​
[/TD]
[TD]
5:10​
[/TD]
[TD]
5:11​
[/TD]
[TD]
5:12​
[/TD]
[TD]
5:13​
[/TD]
[TD]
5:14​
[/TD]
[TD]
5:15​
[/TD]
[TD]
5:16​
[/TD]
[TD]
5:17​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
xxxx​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
yyyy​
[/TD]
[TD]
0​
[/TD]
[TD]
1​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
2​
[/TD]
[TD]
0​
[/TD]
[TD]
0​
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DCE6F1]#DCE6F1[/URL] "]
10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
zzzz​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD]
0​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[TD]
2​
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9D9D9]#D9D9D9[/URL] "]
3​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2 copied down (adjust the ranges according your data)
=MaxTimes(E$7:Q$7,INDEX(E$8:Q$10,MATCH(A2,D$8:D$10,0),0))

Save the file as .xlsm

Hope this helps

M.

It works great. is there any way to exaplain the code? or is that too difficult, thanks.
 
Upvote 0
I added some comments in the code. Hope this helps.

Code:
Function MaxTimes(rTimes As Range, rData As Range)
    Dim lCol As Long, strAux As String, bBreak As Boolean
    Dim sFirst As String, sLast As String
    
    'sFirst and sLast are used to store the first and
    'the last Time, respectively, of the interval whose values = Max
    'bBreak is used to check if the sequence of Max was breaked (cell<>Max)
    
    'Loop through columns of the range rTimes
    For lCol = 1 To rTimes.Columns.Count
        'Checks if cell in this column=Max
        If rData.Cells(1, lCol) = Application.Max(rData) Then
            'If first value doesn1t exist yet, create it: New interval
            If sFirst = "" Then
                sFirst = Format(rTimes.Cells(1, lCol), "h:mm")
                'makes the last value= first value, just in case there is only
                'one value = Max in the interval
                sLast = sFirst
            Else
                'there are more than one value=Max in the interval, so adjusts the last value
                sLast = Format(rTimes.Cells(1, lCol), "h:mm")
            End If
            'check if last column was reached.
            'If so, break=true to force calculation on the if below
            'if not, there is no break since cell=Max: break = False
            bBreak = (lCol = rData.Columns.Count)
        Else
            'a break in sequence of max values occured
            bBreak = True
        End If
        'if break (cell<>Max) checks if sFirst exists
        'if so creates the string
        If bBreak And sFirst <> "" Then
            strAux = strAux & ", " & sFirst & IIf(sFirst = sLast, "", "-" & sLast)
            'reset sFirst and sFast: it's the end of a sequence of Max values
            sFirst = ""
            sLast = ""
        End If
    Next lCol
    MaxTimes = Mid(strAux, 3)
End Function

M.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

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