Finding the maximum of minimum values

问题:

I would like to calculate the maximum value of the minimum values of each row in a spreadsheet (Google Sheets, specifically) that is greater than 0. I hope that makes sense. My data is:

0    6   7   8   1          
0   12  21  22  21          
0   10      18  24
0    7   9   1  17          
0   16  16  20              

So, I want an ArrayFormula of some sort that will generate:

 1
12
10
 1
16

Of which I could then get the maximum. I've read and experienced that the obvious solution doesn't work, which is:

=max(ArrayFormula(min(if(A:Z>0,A:Z,"")))

The reason being the ArrayFormula(min(... part of it returns the minimum of the whole data set that's greater than 0, which is 1 here. If there's no nice workaround, I'll consider rearranging my data somehow, or calculating minima in separate cells, but I'm really hoping for a 1-step solution.

回答1:

max ArrayFunction doesn't exist, so the only way is workaround. Suppose, you have some numbers in range A1:C5. the resulting range is D1:D5:

All you need is single formula in cell D1:

=QUERY(QUERY({ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(row(A1:C5)&"*"&A1:C5&"/")),"/")),"\d+")),ArrayFormula(REGEXEXTRACT(TRANSPOSE(SPLIT(ArrayFormula(CONCATENATE(row(A1:C5)&"*"&A1:C5&"/")),"/")),"[*](\d+)")*1)},"select Col1, min(Col2) where Col2 <>0 group by Col1 label min(Col2) ''"),"select Col2")

Be careful, it can work slow.

The main part of formula is query:

select Col1, min(Col2) where Col2 <>0 group by Col1...

change it to get different results (max, min, sum, avg, some calculatrions and so on)

If someone want to explore the formula, here's worksheet with step-by step solution.

回答2:

With data in the range A1:E5, in F1 and copied down:

=if(small(A1:E1,1)=0,small(A1:E1,2),small(A1:E1,1))  

or

=ArrayFormula(min(if(A2:E2>0,A2:E2,"")))  

should return the minimum value greater than 0 per row, for which the maximum might be calculated with:

=max(F1:F5) 

回答3:

In addition to get the minimum value per row (allowing an array output in one go), try:

=index(ArrayFormula(transpose(query(transpose(if(A:C>0, A:C,)),"select "&join("),","min(Col"&row(indirect("A1:A"&count(A:A))))&")"))),,2)