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

I just tried this formula

=column(max(L5:iv5))

and it doesn't even work

"NDBC" wrote:

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

perhaps
=INDEX(L5:IV5,,MATCH(MAX(L5:IV5),L5:IV5,0)-1)

NDBC;436736 Wrote:
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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121229

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
--
Gary''s Student - gsnu200860


"NDBC" wrote:

I just tried this formula

=column(max(L5:iv5))

and it doesn't even work

"NDBC" wrote:

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

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.


"p45cal" wrote:


perhaps
=INDEX(L5:IV5,,MATCH(MAX(L5:IV5),L5:IV5,0)-1)

NDBC;436736 Wrote:
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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121229

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" wrote:

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.


"p45cal" wrote:


perhaps
=INDEX(L5:IV5,,MATCH(MAX(L5:IV5),L5:IV5,0)-1)

NDBC;436736 Wrote:
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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121229

If the lap times are always less than 1 day and laps numbers are always
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 to
format the cell to show minutes and seconds.

NDBC;436995 Wrote: [color=blue]
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" wrote:
[color=blue]
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.


"p45cal" wrote:

[snipped]


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=121229