array formula does not work
 

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

array formula does not work
 

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!

In article ekom.at,
"Sergio" wrote:

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

array formula does not work
 

Many thanks! It worked.

"Domenic" wrote in message
...
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!

In article ekom.at,
"Sergio" wrote:

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

array formula does not work
 

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!

In article ekom.at,
"Sergio" wrote:

Many thanks! It worked.

"Domenic" wrote in message
...
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!

In article ekom.at,
"Sergio" wrote:

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

array formula does not work
 

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" wrote in message
...
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!

In article ekom.at,
"Sergio" wrote:

Many thanks! It worked.

"Domenic" wrote in message
...
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!

In article ekom.at,
"Sergio" wrote:

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

array formula does not work
 

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!

In article lekom.at,
"Sergio" wrote:

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.