finding the cell adjacent to the max value in a row with formula

N

NDBC

I think I can find the column of the max value in a row using

=column(max(L5:iv5))

is there a way to select the value in the cell immediately to the left of
this cell, so column-1.

Thanks
 
G

Gary''s Student

say L5 thru IV5 contains:

1 2 99 1 1 ................

so we want column M (column #13)

=COLUMN(L5)+MATCH(MAX(L5:IV5),L5:IV5,0)-2 displays 13
 
N

NDBC

Whilst your formula works perfectly for the question I asked (Gary I think
yours gives me the column number not the value of the cell contents) I just
realised it doesn't always solve my problem. Thank you for your responses
though. It keeps astounding me how little of excel I actually use.

I will explain my problem better. What I have is a range in L5 going right
consisting of lap time, place in race, next lap time, place in race etc. So
like this

0:32:23 3 1:00:12 2 1:32:14 1

What I am trying to determine using a formula in k5 is the last time in the
row, which will always be the max time and always 2nd column from the right
hand end. The number of laps completed can vary between races though so i
can't just refer to a set cell.

I'm sorry I wasted your time before but is there a way using formula I can
come up with the time value from the second last cell. In this example 1:32:14

Thanks again.
 
N

NDBC

I've worked it out. I have a lap counter and i use the number of laps to
determine column number in conjunction with index(). thanks


NDBC said:
Whilst your formula works perfectly for the question I asked (Gary I think
yours gives me the column number not the value of the cell contents) I just
realised it doesn't always solve my problem. Thank you for your responses
though. It keeps astounding me how little of excel I actually use.

I will explain my problem better. What I have is a range in L5 going right
consisting of lap time, place in race, next lap time, place in race etc. So
like this

0:32:23 3 1:00:12 2 1:32:14 1

What I am trying to determine using a formula in k5 is the last time in the
row, which will always be the max time and always 2nd column from the right
hand end. The number of laps completed can vary between races though so i
can't just refer to a set cell.

I'm sorry I wasted your time before but is there a way using formula I can
come up with the time value from the second last cell. In this example 1:32:14

Thanks again.
 
P

p45cal

If the lap times are always less than 1 day and laps numbers are alway
1 or more then
=MAX(IF(L5:IV5<1,L5:IV5,0))
*array-entered*, will show the max time in that range. Don't forget t
format the cell to show minutes and seconds.

NDBC;436995 said:
I've worked it out. I have a lap counter and i use the number of lap
to
determine column number in conjunction with index(). thanks


NDBC said:
Whilst your formula works perfectly for the question I asked (Gary think
yours gives me the column number not the value of the cell contents I just
realised it doesn't always solve my problem. Thank you for you responses
though. It keeps astounding me how little of excel I actually use.

I will explain my problem better. What I have is a range in L5 goin right
consisting of lap time, place in race, next lap time, place in rac etc. So
like this

0:32:23 3 1:00:12 2 1:32:14 1

What I am trying to determine using a formula in k5 is the last tim in the
row, which will always be the max time and always 2nd column from th right
hand end. The number of laps completed can vary between races thoug so i
can't just refer to a set cell.

I'm sorry I wasted your time before but is there a way using formul I can
come up with the time value from the second last cell. In thi example 1:32:14

Thanks again.


:
[snipped
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top