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

A

Alex_Werner

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?
 
J

John Michl

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
 
A

Alex_Werner

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
 
B

Bob Phillips

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
 
Top