Copy a row with a max value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In an exercise log, I have some data arranged in the following columns: Date, Location, Duration, Distance, Speed.
At the bottom of the data, I'd like to automatically reproduce the row with the max speed, for example.

I found the max speed with: =MAX(E1:E30)
Then I tried to use the following for the date on which the max speed occurred: =ADDRESS(ROW(MAX(E1:E30)),5) and also INDIRECT(that address), but it didn't work.

Please help
 
Just adjust the ranges:-

It will match (look up) the Max val in E1:E30, then the MATCH function returns
the row in the range that it appears on, relative to the starting row of the
range (eg, it appears on the 7th row of the range in question), then passes the
7 in this example to the INDEX function, which will run down 7 rows into
whatever range you have specified and return the value in that cell.

=INDEX($D$1:$D$30,MATCH(MAX($E$1:$E$30),$E$1:$E$30,0))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Duncan Feldane said:
In an exercise log, I have some data arranged in the following columns: Date,
Click to expand...
Location, Duration, Distance, Speed.
At the bottom of the data, I'd like to automatically reproduce the row with the max speed, for example.

I found the max speed with: =MAX(E1:E30)
Then I tried to use the following for the date on which the max speed
Click to expand...
occurred: =ADDRESS(ROW(MAX(E1:E30)),5) and also INDIRECT(that address), but it
didn't work.
 
Back
Top