Trying to combine Lookup and MIN functions...

[phoenixstar]

Trying to combine two functions. I have 62 columns of data, and am
trying to create another column that does the following:

Column 1 has set values (in this case temperatures for set volumes of
liquid). Columns 2-62 represent the volume of liquid available at each
temperature gradiant. Each column has a certain amount of liquid
subtracted from the lowest temperature gradiant until there is no more
liquid available at that temperature gradiant (value is less than 0).
At this point, the next column begins subtracting the volume of liquid
from the next temperature gradiant.

I am looking to create a column that returns the lowest (minimum value)
temperature gradiant available before the subtraction occurs. Thus, the
first value will return the Minimum Value from column 1 in the row where
the minimum value from column 2 is greater than 0. The 2nd value will
return the Minimum Value from column 1 in the row where the minimum
value from column 3 is greater than 0... and so on...

I can't seem to figure this little logic quandry out, and was hoping to
get some help here. Thank you very much for your time.

 

[Frank Kabel]

Hi
One way: try the following array formula (entered with
CTRL+SHIFT+ENTER) in the first cell:
=INDEX($A$1:$A$100,MAX(IF(OFFSET($A$1:$A$100,0,ROW(1:1))>0,ROW(OFFSET($
A$1:$A$100,0,ROW(1:1))))))
and copy down

or try (if the other columns are really sorted descending):
=INDEX($A$1:$A$100,MATCH(0.0000000001,OFFSET($A$1:$A$100,0,ROW(1:1)),-1
))
and copy down

 

[Myrna Larson]

Not quite sure I understand your problem, but will something like this work:

=MIN(IF(A1:A100>0,A1:A100))

This is an array formula: enter it with CTRL+SHIFT+ENTER, rather than just
ENTER.