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.

 

[Cubist]

How about this?

Book3
	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.83
9	1.55	0.82
10	0	0.82
11	0.83	0.82
Sheet6

Cell Formulas
Range	Formula
B1:B11	B1	=MIN(FILTER($A$1:$A$11,($A$1:$A$11<>A1)*($A$1:$A$11>0)))

 

[RobOrBob]

That'll do it nicely!

Thanks!!!

 

[Peter_SSs]

What about doing them all at once with this in the top cell only, no need to copy down? (Also MINIFS generally more efficient than FILTER)

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.83
9	1.55	0.82
10	0	0.82
11	0.83	0.82
MINIFS

Cell Formulas
Range	Formula
B1:B11	B1	=LET(r,A1:A11,MINIFS(r,r,">0",r,"<>"&r))
Dynamic array formulas.

 

[RobOrBob]

Hi Peter

Thanks for the reply.

That also works but has the same issue as Cubist's solution in that it excludes all values that match the current row values.
A value may be duplicated (including the minimum value). So, if the current row value is the minimum and that same value occurs on another row then the other rows value should return as the minimum and not also excluded.

But both solutions have certainly given me a boost!

 

[Cubist]

You didn't indicate this requirement in the prior posts.

 

[RobOrBob]

Adding a column with a unique identifier for each row (arrSels) seems to have resolved it (need to fully test it)
=LET(r,arrVals,s,arrSels,MINIFS(r,r,">0",s,"<>"&s))

Thanks for your input.

 

[RobOrBob]

You didn't indicate this requirement in the prior posts.

It was under the notes in the initial post.

 

[Cubist]

How about this?

Book3
	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.83
9	1.55	0.82
10	0	0.82
11	0.83	0.82
Sheet11

Cell Formulas
Range	Formula
B1:B11	B1	=LET(bool,NOT(ROW()=ROW($A$1:$A$11)),f,FILTER($A$1:$A$11,bool*($A$1:$A$11>0)),MIN(f))

 

[RobOrBob]

How about this?

Book3
	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.83
9	1.55	0.82
10	0	0.82
11	0.83	0.82
Sheet11

Cell Formulas
Range	Formula
B1:B11	B1	=LET(bool,NOT(ROW()=ROW($A$1:$A$11)),f,FILTER($A$1:$A$11,bool*($A$1:$A$11>0)),MIN(f))

Yes, that works perfectly.

Thanks for you help.