Formula help

[erenkey]

I have a spreadsheet that has dates down the left column and hours of the day across the top row. Is there a formula that i can use that will look into each column(independently of each other) find the max number and tell me which date it is associated with? For example If I am looking at the 1:00 am column and find the max # of transactions is 100, I want it to tell me what date the 100 occured on. Is this possible?

 

[Jonmo1]

=Index(A:A,Match(Max(B:B),B:B,0))

Where A:A are Dates and B:B is the 1:00 AM column.

 

[lotsasmiles]

If you're just looping through each column to find the max values, with each iteration, you can just set the "date" variable by selecting the max value, then offsetting it to get to the first column where the date exists.... Does that make any sense? So if the third column is 1:00 (assuming the dates are in the first), and that's what you're currently looking through, set a range variable to equal the max value (perhaps use the find method?). Once you have that, get the date by doing: maxValCell.offset(0, -2). Hope that helps!!

 

[Aladin Akyurek]

I have a spreadsheet that has dates down the left column and hours of the day across the top row. Is there a formula that i can use that will look into each column(independently of each other) find the max number and tell me which date it is associated with? For example If I am looking at the 1:00 am column and find the max # of transactions is 100, I want it to tell me what date the 100 occured on. Is this possible?

Book7
	A	B	C	D	E	F	G	H
1		10:00	12:00	13:00	!4:00			Time
2	7/25/2007	56	57	96	68			13:00
3	8/7/2007	54	40	62	62			Max
4	8/18/2007	68	99	77	86			96
5	8/23/2007	55	64	82	64			Count Of Max
6	8/29/2007	60	61	96	93			2
7	9/5/2007	65	55	62	98			Date(s) Of Occurrence
8	9/12/2007	44	84	46	65			7/25/2007
9	9/20/2007	56	80	48	83			8/29/2007
10	10/4/2007	49	76	72	80
11	10/11/2007	60	73	82	49
12	10/22/2007	42	75	84	62
13	11/6/2007	72	51	74	50
14	11/19/2007	90	92	62	67
15	11/27/2007	70	66	44	54
16
Sheet1

H2 houses the time to look at...

H4:

=MAX(INDEX(B2:E15,0,MATCH(H2,B1:E1,0)))

H6:

=COUNTIF(INDEX(B2:E15,0,MATCH(H2,B1:E1,0)),H4)

H8:

Control+shift+enter...

=IF(ROWS(H$8:H8)<=H$6,OFFSET($A$2,SMALL(IF(INDEX($B$2:$E$15,0,MATCH(H$2,$B$1:$E$1,0))=H$4,ROW($A$2:$A$15)-ROW($A$2)+1),ROWS(H$8:H8))-1,0),"")

and copy down.

 

[erenkey]

Aladin,
I got the first 2 parts to work but i cannot get the last part to work. Does it amke a difference if I am putting the information below the spreadsheet instead of beside it?

 

[erenkey]

to find the max of each column I just used =Max(c2:c366)

My spreadsheet has one row for each day of the year.

Jonmo,
I tried using youe formula but it gave me a ref error. Is there a way for me to say, Look in this group, find this number, return me the data on the same row from this column?

 

[Aladin Akyurek]

Aladin,
I got the first 2 parts to work but i cannot get the last part to work. Does it amke a difference if I am putting the information below the spreadsheet instead of beside it?

You just need to adjust the formulas, in particular the ROWS() bit. Btw, don't get seduced by INDEX(...,MATCH(MAX(...),...)) for it will not turn up results conform the reality.