Lowest value in range of cells excluding this row

[RobOrBob]

Hi all,

I have a range of cells that contain negative and positive values plus 0.

I am trying to return for each row the minimum value in the range including only those > 0 and excluding the current row.

I have managed a solution by returning the minimum value before the current row and that after the current row. But its horrible

lastRow (aka G1): 11 =MAX((A:A<>"")*(ROW(A:A)))
Col A = values
Col B = lowest non-zero value before this row
Col C = lowest non-zero value after this row
Col D = lowest non-zero value of col C and Col D

arrVals: =Sheet1!$A$1:INDEX(Sheet1!$A$1:$A$50,lastRow)
Cell B1: =LET(minbefore,INDEX(arrVals,1):INDEX(arrVals,ROW()-1),IF(ROW()=1,0,MINIFS(minbefore,minbefore,">0")))
Cell C1: =LET(minafter,INDEX(arrVals,ROW()+1):INDEX(arrVals,lastRow),IF(ROW()=lastRow,0,MINIFS(minafter,minafter,">0")))
Cell D1: =MINIFS(B1:C1,B1:C1,">0")

Any help appreciated!

Note:
The cells cannot be converted to a table.
There will be 1 to 50 values.
A value may be duplicated (including the minimum value).
It must use a formula and not VBA.

 

[Peter_SSs]

Here's a bit shorter one that I think also does what you want.

BTW, given that post #2 didn't actually do what you wanted, you might consider re-marking the solution post to one of the several others that seem to do so.

24 03 23.xlsm
	A	B
1	1.55	0.82
2	0.88	0.82
3	2.55	0.82
4	0	0.82
5	0	0.82
6	-1.8	0.82
7	-1.55	0.82
8	0.82	0.82
9	1.55	0.82
10	0.82	0.82
11	0.83	0.82
Sheet6

Cell Formulas
Range	Formula
B1:B11	B1	=LET(r,A$1:A$11,MIN(IF((r>0)*(ROW(r)<>ROW()),r)))