Formula displaying max ranges?

yessir

Board Regular
Joined
Jun 7, 2019
Messages
81
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 #
xxxx3
yyyy2
5:055:065:075:085:095:105:115:125:135:145:155:165:17
xxxx0112223332211
yyyy0122110000000
<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
Joined
Nov 7, 2006
Messages
8,412
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
 

yessir

Board Regular
Joined
Jun 7, 2019
Messages
81
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)
MAX #
xxxx3
yyyy2
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>


5:055:065:075:085:095:105:115:125:135:14
5:15
5:16
5:17
xxxx0112223332211
yyyy012
2110022200
<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
Joined
Aug 23, 2010
Messages
16,856
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Highlighting the max values would be an acceptable solution?
Can be easily done with Conditional Formatting.

M.
 

yessir

Board Regular
Joined
Jun 7, 2019
Messages
81

ADVERTISEMENT

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

M.

MAX #
xxxx3
yyyy2
5:055:065:075:085:095:105:115:125:135:145:155:165:17
xxxx0112223332211
yyyy0122110022200
Ideal solution would be where it returned times in different cell set (Like below)
Time(s) of Max
xxxx5:11-5:13
yyyy5: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.
 

yessir

Board Regular
Joined
Jun 7, 2019
Messages
81
Is there a way to go about this without using conditonal formatting?
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,856
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 23, 2010
Messages
16,856
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

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>


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:

yessir

Board Regular
Joined
Jun 7, 2019
Messages
81
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


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>


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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,856
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
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
Top