# Formula displaying max ranges?

#### yessir

##### Board Regular
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.

 MAX # xxxx 3 yyyy 2 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 3 3 3 2 2 1 1 yyyy 0 1 2 2 1 1 0 0 0 0 0 0 0
<colgroup><col width="64" style="width: 48pt;" span="14"> <tbody> </tbody>

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Special-K99

##### Well-known Member
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

maximum 2
ranging from 5:08-5:10 AND 5:14-5:15

#### yessir

##### Board Regular
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

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)
 MAX # xxxx 3 yyyy 2
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

 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 3 3 3 2 2 1 1 yyyy 0 1 2 2 1 1 0 0 2 2 2 0 0
<colgroup><col width="64" style="width: 48pt;" span="14"> <tbody> </tbody>

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!!

#### Marcelo Branco

##### MrExcel MVP
Highlighting the max values would be an acceptable solution?
Can be easily done with Conditional Formatting.

M.

#### yessir

##### Board Regular

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

M.

 MAX # xxxx 3 yyyy 2 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 3 3 3 2 2 1 1 yyyy 0 1 2 2 1 1 0 0 2 2 2 0 0 Ideal solution would be where it returned times in different cell set (Like below) Time(s) of Max xxxx 5:11-5:13 yyyy 5:07-5:08,5:13-5:15
<colgroup><col width="64" style="width: 48pt;" span="14"> <tbody> </tbody>
Although I do see how conditonal formatting would highlight the times when at max.

#### Marcelo Branco

##### MrExcel MVP

You need VBA - maybe an User Defined Function (UDF)
I'm busy now - in the meantime maybe someone can help.

M.

#### Marcelo Branco

##### MrExcel MVP
A possible solution with a UDF

Alt+F11 to open the VBEditor
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

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ L​ M​ N​ O​ P​ Q​ 1​ MAX #​ Result​ 2​ xxxx​ 3​ 5:11-5:13​ 3​ yyyy​ 5​ 5:07-5:08, 5:13-5:15​ 4​ zzzz​ 7​ 5:05, 5:07-5:09, 5:14-5:15, 5:17​ 5​ 6​ 7​ 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​ 8​ xxxx​ 0​ 1​ 1​ 2​ 2​ 2​ 3​ 3​ 3​ 2​ 2​ 2​ 2​ 9​ yyyy​ 0​ 1​ 2​ 2​ 1​ 1​ 0​ 0​ 2​ 2​ 2​ 0​ 0​ 10​ zzzz​ 3​ 0​ 3​ 3​ 3​ 2​ 2​ 2​ 2​ 3​ 3​ 2​ 3​

<tbody>
</tbody>

=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:

#### yessir

##### Board Regular
A possible solution with a UDF

Alt+F11 to open the VBEditor
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

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ L​ M​ N​ O​ P​ Q​ 1​ MAX #​ Result​ 2​ xxxx​ 3​ 5:11-5:13​ 3​ yyyy​ 5​ 5:07-5:08, 5:13-5:15​ 4​ zzzz​ 7​ 5:05, 5:07-5:09, 5:14-5:15, 5:17​ 5​ 6​ 7​ 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​ 8​ xxxx​ 0​ 1​ 1​ 2​ 2​ 2​ 3​ 3​ 3​ 2​ 2​ 2​ 2​ 9​ yyyy​ 0​ 1​ 2​ 2​ 1​ 1​ 0​ 0​ 2​ 2​ 2​ 0​ 0​ 10​ zzzz​ 3​ 0​ 3​ 3​ 3​ 2​ 2​ 2​ 2​ 3​ 3​ 2​ 3​

<tbody>
</tbody>

=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.

#### Marcelo Branco

##### MrExcel MVP

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.

Replies
0
Views
85
Replies
4
Views
143
Replies
7
Views
151
Replies
4
Views
235
Replies
14
Views
179

1,141,284
Messages
5,705,510
Members
421,399
Latest member
hjweiss00

### 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.

### Which adblocker are you using?

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

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