Find the times of day max number occurred

[joh1135]

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
	A	B	C	D	E	F
1	Time	A	B	C	D	E
2	6:00	4	2	6	2	1
3	6:30	6	1	-	2	-
4	7:00	8	3	5	0	3
5	7:30	8	4	-	-	-
6	8:00	7	6	7	2	9
7	8:30	3	2	-	-	6
8	9:00	4	2	3	0	6
9	9:30	3	2	-	-	6
10	10:00	3	4	2	0	5
11	10:30	2	2	-	-	13
12	11:00	3	1	2	0	16
13	11:30	6	4	-	-	4
14	12:00	8	2	2	0	0
15	12:30	6	2	2	-	-
16	13:00	8	3	2	4	0
17	13:30	1	6	3	-	2
18	14:00	4	3	2	21	0
19	14:30	9	3	2	14	16
20	15:00	8	3	2	0	12
21	15:30	0	3	2	13	13
22	16:00	0	1	4	11	12
23	16:30	0	4	2	14	12
24	17:00	0	4	4	3	16
25	17:30	8	5	7	-	13
26	18:00	2	4	1	0	13
27	18:30	0	1	2	-	10
28	19:00	1	0	2	0	12
29	19:30	-	1	1	-	-
30	20:00	0	0	1	0	0
31	20:30	-	-	-	-	-
32	21:00	0	0	1	0	1
33	21:30	-	-	-	-	-
34	22:00	-	0	-	-	-
35	22:30	-	-	-	-	-
36	23:00	-	-	-	-	-
37	23:30	-	-	-	-	-
38	Avg	3.9	2.5	2.8	4.1	7.4
39
40
41	MAX	9	6	7	21	16
42	WHEN	14:30	8:0013:30	8:0017:00	14:00	11:0014:3017:00
Sheet1

 

[joh1135]

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

 

[jindon]

If the values you got are correct, it should work...

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]

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]

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]

Dave, delete your mail address as I got it already.

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

 

[joh1135]

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