Formula help

erenkey

Board Regular
Joined
Dec 9, 2005
Messages
162
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
=Index(A:A,Match(Max(B:B),B:B,0))

Where A:A are Dates and B:B is the 1:00 AM column.
 
Upvote 0
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!!
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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
Back
Top