Finding the maximum of minimum values

Question

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:

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.

Max Makhrov · Accepted Answer · 2016-02-25 11:13:20Z

0

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.

answered Feb 25, 2016 at 11:13

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pnuts · Accepted Answer · 2014-07-23 01:59:55Z

0

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)

answered Jul 23, 2014 at 1:59

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Comments

JPV · Accepted Answer · 2016-02-25 21:12:12Z

0

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)

answered Feb 25, 2016 at 21:12

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Collectives™ on Stack Overflow

Finding the maximum of minimum values

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related