minimum of non-zero non-adjacent cells in MS Excel

Question

I know that for finding the minimum of non-zero cells in Excel, I can use

min(if(A1:A10>0,A1:10))

But what if I would like to find the minimum of non-zero & non-adjacent cells?

Answer 1

If you have a collection of non-adjacent cells like A2,C2,F2,H2 and J2 containing positive numbers or zeroes then this formula gives you the smallest non-zero value

=SMALL((A2,C2,F2,H2,J2),INDEX(FREQUENCY((A2,C2,F2,H2,J2),0),1)+1)

You can also name your collection of cells, e.g. List and then use that list in the same seup, i.e.

=SMALL(List,INDEX(FREQUENCY(List,0),1)+1)

Answer 2

Here is how one finds the number closest to zero when some of the non-contiguous values are positive and some are negative. For example,a1,b5 and f15 contain the values -1,2 and -5. The formula should return the value -1.

Tom Ogilvy at https://www.atlaspm.com/toms-tutorials-for-excel/ answered the question:

For the specific question you asked, this would return -1

=INDEX(CHOOSE({1,2,3},A1,B5,F15),MATCH(MIN(ABS(CHOOSE({1,2,3},A1,B5,F15))),ABS(CHOOSE({1,2,3},A1,B5,F15)),0))

entered with Ctrl+Shift+Enter. You can adjust the CHOOSE({1,2,3},A1,B5,F15) (which replaces the C7:C20 in the original formula) to include more cells. For example if I wanted to add Z20 and AA4 I could use CHOOSE({1,2,3,4,5},A1,B5,F15,Z20,AA4). Each cell would need to be listed individually. so you could not have CHOOSE({1,2},A1,B5:B20)