# Find the times of day max number occurred

#### joh1135

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

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

Hope this helps

Dave

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### jindon

##### MrExcel MVP
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
Do
MaxIfs = MaxIfs & r.Offset(,-ref+1).Text & Chr(32)
MaxIfs = Trim(MaxIfs)
End Function``````

#### jindon

##### MrExcel MVP
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

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

Hope this helps

Dave

Ah....

Should

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

#### joh1135

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

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

#### joh1135

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

1,181,589
Messages
5,930,771
Members
436,759
Latest member
grnelson1797

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