What is wrong on the fomular: Offset(MIN(A1:A10),,-1)?
 

I have a huge spread sheet, in which I need to extract the value from the
cell, which sits right to the lowest value in the whole row. E.g.:
Example row: 2|4|7|3|0.3|10
Expected extract value: 3
I was thinking of using the OFFSET functionality in combination with the MIN
functionality: =OFFSET(MIN(A1:A6),,-1) However, this is not working. Anybody
any idea why not? Or a better solution at all?

=IF(MATCH(MIN(21:21),21:21,0)1,INDEX(21:21,MATCH( MIN(21:21),21:21,0)-1),"")

--
HTH

Bob Phillips

"Alex_Werner" wrote in message
...
I have a huge spread sheet, in which I need to extract the value from the
cell, which sits right to the lowest value in the whole row. E.g.:
Example row: 2|4|7|3|0.3|10
Expected extract value: 3
I was thinking of using the OFFSET functionality in combination with the
MIN
functionality: =OFFSET(MIN(A1:A6),,-1) However, this is not working.
Anybody
any idea why not? Or a better solution at all?

There are a few reasons why the offset formula you posted won't work
not the least of which is that the fist argument in Offset is supposed
to be a reference, not a value. Also, row post indicated that the data
are in a row but the formula includes reference to a column of data not
a row of data.

I don't think offset is the way to go. Try a combination of Match and
Index. Assume data are in A1 through F1, then...

=INDEX(A1:F1,1,MATCH(MIN(A1:F1),A1:F1,0)-1)

should do the trick.

John Michl
www.johnmichl.com

Hi Bob,

you really did an amazing job! It really works! And I tried already a full
day on that... Many thanks! (I changed the area a little bit, that means with
the whole row of 21 I got a circular problem. However, when I changed to
A21:FR21 it works.)
Many thanks again!
Alex

"Bob Phillips" wrote:

=IF(MATCH(MIN(21:21),21:21,0)1,INDEX(21:21,MATCH( MIN(21:21),21:21,0)-1),"")

--
HTH

Bob Phillips

"Alex_Werner" wrote in message
...
I have a huge spread sheet, in which I need to extract the value from the
cell, which sits right to the lowest value in the whole row. E.g.:
Example row: 2|4|7|3|0.3|10
Expected extract value: 3
I was thinking of using the OFFSET functionality in combination with the
MIN
functionality: =OFFSET(MIN(A1:A6),,-1) However, this is not working.
Anybody
any idea why not? Or a better solution at all?

Hi Alex,

Glad it worked.

I assumed the formula would be in another row, we always make assumptions,
invariably wrong <vbg, but you worked it out so that is great.

Bob

"Alex_Werner" wrote in message
...
Hi Bob,

you really did an amazing job! It really works! And I tried already a full
day on that... Many thanks! (I changed the area a little bit, that means
with
the whole row of 21 I got a circular problem. However, when I changed to
A21:FR21 it works.)
Many thanks again!
Alex

"Bob Phillips" wrote:


=IF(MATCH(MIN(21:21),21:21,0)1,INDEX(21:21,MATCH( MIN(21:21),21:21,0)-1),"")

--
HTH

Bob Phillips

"Alex_Werner" wrote in message
...
I have a huge spread sheet, in which I need to extract the value from
the
cell, which sits right to the lowest value in the whole row. E.g.:
Example row: 2|4|7|3|0.3|10
Expected extract value: 3
I was thinking of using the OFFSET functionality in combination with
the
MIN
functionality: =OFFSET(MIN(A1:A6),,-1) However, this is not working.
Anybody
any idea why not? Or a better solution at all?