# 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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Again?
Paste the code onto Module
Use in cell
B42
=MaxIfs(\$A\$2:B37,Column())
and copy to the right
Code:
``````Function MaxIfs(rng As Range, ref As Long)
Dim r As Range, ff As String, myMax
myMax = WorksheetFunction.Max(rng.Columns(ref))
Set r = rng.Columns(ref).Find(myMax,,,xlWhole)
Do
MaxIfs = MaxIfs & r.Offset(,-ref+1).Text & Chr(32)
Set r = rng.Columns(ref).FindNext(r)
MaxIfs = Trim(MaxIfs)
End Function``````

I get the following error

User-define type not defined

Function MaxIfs(rng As Range, ref As Long)
Dim r As Range, ff As Sting, myMax

Should read as String, previous code edited.

Jindon

That fixed that line - now I get a debug error at

Expected Sub, Function or Property

Thanks again for you help.....

Ah, typo

jindon -

Thanks for the help - that is what I thought it was and after changing it - I no longer get an error.

But, I am now getting #VALUE! in the cells that have the formula

=MaxIfs(\$A\$2:B37,COLUMN()) in them

any ideas????

Appreciate your time and help with this....

Hi
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``````

Same results -

I put msgbox Maxifs after the line

MaxIfs = MaxIfs & r.Offset(, -ref + 1).Text & Chr(32)

and got the following results 12:00, 17:00,06:30,06:30,05:00,11:00,17:00,14:00
06:00,14:30,07:30,08:00,07:30,08:00,14:30

I put the msgbox after the following and the same results:

and the same thing before the end of the function

Thanks

Dave

Strange...

So the formula is entered in cell B42 and copy to the right, correct?

1,221,412
Messages
6,159,735
Members
451,588
Latest member
tsg178

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