array formula does not work

[Sergio]

Hi,

I have a range of numbers, say, b1:f100.
I want to create an array formula, that delivers an array of maximums for
each row.
This I can easily do by, for example,

{max(offset(b1, row(1:100)-1, 0,1,5))}

If I put this array on a sheet, then I have correct max for each row.

But if I want to select, for example, min of those max I change the
formula to

{min(max(offset(b1, row(1:100)-1, 0,1,5)))}

and it does not work! It reterns the number of rows. Can anyone explain why
and what I have to do to make it work?
Many thank. Sergei

 

[Domenic]

Try...

=MIN(SUBTOTAL(4,OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1)))

....confirmed with CONTROL+SHIFT+ENTER. If the data can contain blank
rows, try the following instead...

=MIN(IF(SUBTOTAL(4,OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1)),SUBTOTAL(4,O
FFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1))))

Hope this helps!

 

[Sergio]

Many thanks! It worked.

Try...

=MIN(SUBTOTAL(4,OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1)))

...confirmed with CONTROL+SHIFT+ENTER. If the data can contain blank
rows, try the following instead...

=MIN(IF(SUBTOTAL(4,OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1)),SUBTOTAL(4,O
FFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1))))

Hope this helps!

 

[Domenic]

You're very welcome! Although, with regards to the second formula I
offered in case of blank rows, if the maximum for any row is in fact 0
and the minimum from the array of maximums turns out to be 0, the
formula will return an incorrect result. For this reason, try the
following instead...

H1, copied down:

=IF(COUNT(B1:F1),MAX(B1:F1),"")

H101:

=MIN(H1:H100)

Hope this helps!

 

[Sergio]

Hi,
This was my original solution. But I needed everything to reside in one
cell.
And I still do not understand why
{min(max(offset(b1, row(1:100)-1, 0,1,5)))}
returns the number of rows...
Best.

 

[Domenic]

If the data doesn't contain negative numbers, try...

=MIN(IF(COUNTIF(OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1),">=0"),SUBTOTAL(
4,OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1))))

Otherwise, try...

=MIN(IF(COUNTIF(OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1),{"<0",">=0"}),SU
BTOTAL(4,OFFSET(B1:F100,ROW(B1:F100)-ROW(B1),0,1))))

Both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!