Formula displaying max ranges?

Y

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]
 
Sort by date Sort by votes
Marcelo Branco said:
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.
Click to expand...

Thank you, you've been very helpful!

I've gotten a NEW task that I don't quite understand. Is there a way to find the times with the shortest time between them and then be able to calculate the time inbetween said times. I would normally not have trouble doing this (when the times are in different cells) However, in this case the times all appear in the same cell. Any help is appreciated!

[TABLE="width: 480"]
<colgroup><col width="64" style="width: 48pt;" span="10"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A1
[/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"]J1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 6"]10:08-10:33, 13:21-13:46, 16:16-16:40, 18:17-18:41, 23:14-5:00
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]25[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 4"]10:32-11:02, 15:59-16:26, 22:07-6:05
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 8"]8:47-9:16, 10:11-10:36, 14:05-14:34, 15:32-16:09, 17:31-18:04, 19:23-19:53, 23:37-5:35
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]25[/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]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 10"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
You must log in or register to reply here.

Similar threads

T
  • Question Question
Selecting MAX values of 3D array via INDEX/MATCH
Replies
3
Views
248
travellerva
T
P
  • Solved
Find Max of Year
2
Replies
12
Views
451
bobsan42
bobsan42
M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
155
dreid1011
dreid1011
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
71
daveyc18
D
montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
100
montecarlo2012
montecarlo2012

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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