calculate lowest value in range (above 0) and reference a related cell.

[klyncher]

Hi

I need to calculate the lowest value in a range, above 0, from the bottom row of the following table (row 27, columns Q to T).

4 13 26 52
2000 2000 1200 0
-£500.00 -£500.00 £300.00 £1,500.00
£0.00 £0.00 £300.00 £1,500.00

In the above example the formula would select the column headed 26 (but it could be any column), then the cell containing the formula would show 26.

I have tried the following formula

Please Login or Register  to view this content.

but the only result it gives is "FALSE" or "#VALUE!"

Any help greatly recieved.

[benishiryo]

welcome to the forum, klyncher. did you press CTRL + SHIFT + ENTER? just to be sure, i hope you're not entering the curly brackets manually. i seem to be able to get 1200 for that.

anyways, you could use this non-array formula. you mention above zero but you're using <> (not equals to) 0. so depending on what you need, maybe:
=SMALL(Q27:T27,1+COUNTIF(Q27:T27,0))
or:
=SMALL(Q27:T27,1+COUNTIF(Q27:T27,"<=0"))

Edit: if you pound signs are manually keyed & not formatted in the cells, they are texts. then you would have to use this array formula:
=SMALL(--SUBSTITUTE(Q27:T27,"£",""),1+SUM(--(--SUBSTITUTE(Q27:T27,"£","")<=0)))

[klyncher]

Hi benishiryo,

Thanks for the response. I did press CTRL + SHIFT + ENTER

The non array formulas both work (no error msg), but don't quite give the answer I was after. The result of both formulas was 300, but I was looking for the result to be the heading of "26" which was above 300 in the example contained in my original message.