Find the times of day max number occurred

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156
I need help calculating the times of the day that the maxinum number occurred in each column and have it displayed in row 42

Thanks
Book1
ABCDEF
1TimeABCDE
26:0042621
36:3061-2-
47:0083503
57:3084---
68:0076729
78:3032--6
89:0042306
99:3032--6
1010:0034205
1110:3022--13
1211:00312016
1311:3064--4
1412:0082200
1512:30622--
1613:0083240
1713:30163-2
1814:00432210
1914:309321416
2015:00832012
2115:300321313
2216:000141112
2316:300421412
2417:00044316
2517:30857-13
2618:00241013
2718:30012-10
2819:00102012
2919:30-11--
3020:0000100
3120:30-----
3221:0000101
3321:30-----
3422:00-0---
3522:30-----
3623:00-----
3723:30-----
38Avg3.92.52.84.17.4
39
40
41MAX9672116
42WHEN14:308:0013:308:0017:0014:0011:0014:3017:00
Sheet1
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156
Jindon

MyMax gives back the results

9 12 11 24 12 16 27 21 32 20 39 7 25 6 9

The data I have is in columns C thru W and rows 2 thru 49

I adjusted your formula to

=MaxIfs($A$2:C49,"COLUMN()")

Hope this helps

Dave
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
If the values you got are correct, it should work...
Code:
Function MaxIfs(rng As Range, ref As Long)
Dim r As Range, ff As String, myMax
myMax = WorksheetFunction.Max(Range(rng.Columns(ref).Address))
Set r = Range(rng.Columns(ref).Address).Find(myMax,,xlValues,xlWhole)
ff = r.Address
Do
     MaxIfs = MaxIfs & r.Offset(,-ref+1).Text & Chr(32)
     Set r = Range(rng.Columns(ref).Address).FindNext(r)
Loop Until ff = r.Address
MaxIfs = Trim(MaxIfs)
End Function
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Jindon

MyMax gives back the results

9 12 11 24 12 16 27 21 32 20 39 7 25 6 9

The data I have is in columns C thru W and rows 2 thru 49

I adjusted your formula to

=MaxIfs($A$2:C49,"COLUMN()")

Hope this helps

Dave

Ah....

Should

=MaxIfs($a$2:c49,Column()) without "
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156
Jindon

No good


If you send me your email -- I can send you the workbook if that would help

I will check back in the morning - thanks again for your time and knowledge

Dave
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Dave, delete your mail address as I got it already.

BTW have read my last post? you r forumula was wrong
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156
Jindon

Got it to work with the following

atime = ""
check = False
For Each acell In Range("E2:E49")
If acell.Value = Range("E60").Value Then
acell.Select
atime = atime & " " & ActiveCell.Offset(0, -4).Value
End If
Next acell
Range("E$61").Value = atime

Thanks for your assistance

(y)
 

Forum statistics

Threads
1,143,654
Messages
5,720,098
Members
422,266
Latest member
Mattyw

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