# 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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### jindon

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

#### joh1135

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

#### jindon

##### MrExcel MVP
Should read as String, previous code edited.

#### joh1135

##### Board Regular

Jindon

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

Expected Sub, Function or Property

Thanks again for you help.....

Ah, typo

#### joh1135

##### Board Regular

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

#### jindon

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

#### joh1135

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

#### jindon

##### MrExcel MVP
Dave

Strange...

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

1,148,108
Messages
5,744,878
Members
423,907
Latest member
zerocool88

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