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
 

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
Joined
Aug 21, 2004
Messages
16,995
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)
ff = r.Address
Do
     MaxIfs = MaxIfs & r.Offset(,-ref+1).Text & Chr(32)
     Set r = rng.Columns(ref).FindNext(r)
Loop Until ff = r.Address
MaxIfs = Trim(MaxIfs)
End Function
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156
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


:(
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156

ADVERTISEMENT

Jindon

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

ff r.Address

Expected Sub, Function or Property



Thanks again for you help.....
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156

ADVERTISEMENT

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

:confused: :confused:
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
How about this one?
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,,,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
 

joh1135

Board Regular
Joined
Mar 11, 2003
Messages
156
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:

Set r = Range(rng.Columns(ref).Address).FindNext(r)

and the same thing before the end of the function

Thanks


:( :(
 

jindon

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

Strange...

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

If so, can you just add msgbox to check myMax please?
 

Forum statistics

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