# Formula help

#### erenkey

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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Jonmo1

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

##### New Member
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!!

##### MrExcel MVP
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
ABCDEFGH
110:0012:0013:00!4:00Time
27/25/20075657966813:00
38/7/200754406262Max
48/18/20076899778696
58/23/200755648264Count Of Max
68/29/2007606196932
79/5/200765556298Date(s) Of Occurrence
89/12/2007448446657/25/2007
99/20/2007568048838/29/2007
1010/4/200749767280
1110/11/200760738249
1210/22/200742758462
1311/6/200772517450
1411/19/200790926267
1511/27/200770664454
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

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

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

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

Replies
1
Views
129
Replies
2
Views
354
Replies
6
Views
520
Replies
2
Views
130
Replies
1
Views
277

1,190,913
Messages
5,983,531
Members
439,848
Latest member
timmyo

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