Array formula that works columnwise?

[Guest]

I have an nxm rectangular range of cells, for which I want to determine the
largest columnwise data span ("range" in statistical terms). Clearly, I can
do this using a row of m helper cells each containing
=MAX(column)-MIN(column)
with the desired result then given as =MAX(helper_row).

Can this be done in a single cell without the helper row?

Jerry

 

[Guest]

Maybe I don't understand your question properly but will the following work
for you?

=COLUMNS(A1:F10)

Where A1:F10 is the range that makes up your nxm.

Hope that helps.

Bill Horton

 

[Guest]

I think that you would need a macro function to do it in one cell.

 

[Guest]

if you have fewer than 30 columns you could use
=max(Max(A:A)-Min(A:A),MAx(B:B)-Min(B:B),...,max(Z:Z)-Min(Z:Z))

if you have more than 30 you could group them
=max((Max(A:A)-Min(A:A,...,Max(Z:Z)-Min(Z:Z)),(Max(AA:AA)-Min(AA:AA,...,Max(AZ:AZ)-Min(AZ:AZ)),(Max(BA:BA)-Min(BA:BA,...,Max(BZ:BZ)-Min(BZ:BZ)))

 

[T. Valko]

Hmmm.....

I wasn't sure what you meant but after seeing bj's replies..........

Range = A15

Array entered:

=MAX(SUBTOTAL(4,OFFSET(A1:A5,,COLUMN(A15)-1))-SUBTOTAL(5,OFFSET(A1:A5,,COLUMN(A15)-1)))

Biff

 

[Guest]

Very slick!

Thanks,
Jerry

Hmmm.....

I wasn't sure what you meant but after seeing bj's replies..........

Range = A15

Array entered:

=MAX(SUBTOTAL(4,OFFSET(A1:A5,,COLUMN(A15)-1))-SUBTOTAL(5,OFFSET(A1:A5,,COLUMN(A15)-1)))

Biff

 

[Guest]

One more level of complexity: I need to ignore error values (#N/A's for
charting purposes) if present. The usual array approach of
IF(ISNUMBER(range),range) cannot be used with OFFSET.

Jerry

 

[Guest]

Workable in principle, However there is an additional wrinkle

The data is in 42 rows by 27 columns, but may contain ignorable errors (#N/A
for charting purposes). By the time I use IF(ISNUMBER(B4:45),B4:B45) instead
of simply B4:B45 ..., the formula is too long to fit in a pre-2007 cell.

Jerry

 

[T. Valko]

I can't come up with anything that ignores errors. I think this is a job for
Harlan!

If this can be done it's going to be a really expensive formula. You may be
better off using the helper row.

Biff

 

[Guest]

I have not found a solution yet, but I have found another inconsistency in
the processing of array formulas.

If you array enter

=MAX(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))-MIN(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))

in a 1x4 range of cells, you get the four columnwise max-min's, but

=COUNT(MAX(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))-MIN(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11))))

only returns 1, and

=MAX(MAX(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))-MIN(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11))))

returns the first value, instead of the largest of the 4 values that were
returned by the original array formula.

Jerry

 

[Lori]

If the data starts in row3 then maybe try array-entered:

=MAX(DMAX(A17,COLUMN(A17),A12)-DMIN(A17,COLUMN(A17),A12))

where the top 2 rows are either just blank or to ignore errors fill
across in the top rows e.g.:

a1 a2 a3
<>#n/a <>#n/a <>#n/a

 

[Harlan Grove]

I can't come up with anything that ignores errors. I think this
is a job for Harlan!

With the entire range of interest named D, try the array formula

=MAX(MOD(LARGE(IF(ISNUMBER(D),D)+10000*(COLUMN(D)-1),
1+(COLUMNS(D)-COLUMN(D))*ROWS(D)),10000)
-MOD(SMALL(IF(ISNUMBER(D),D,10000)+10000*(COLUMN(D)-1),
1+(COLUMN(D)-1)*ROWS(D)),10000))

This relies on adding column number times some big value (10000 above)
to the values in D, so it may run into trouble due to limited
precision. If you want it to adapt to the values in D, replace it with
(MAX(IF(ISNUMBER(D),D))+1).

 

[Harlan Grove]

I have not found a solution yet, but I have found another
inconsistency in the processing of array formulas.

If you array enter

=MAX(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))
-MIN(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))

in a 1x4 range of cells, you get the four columnwise max-min's, but

=COUNT(MAX(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))
-MIN(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11))))

only returns 1, and

These results arise from the INDEX calls, which return arrays of
arrays. Excel copes with this return type the same way it does with
arrays of range references (e.g., INDEX(A15,0,{1,2})) - by using
implicit indexing when entered into multiple cell ranges.

=MAX(MAX(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11)))
-MIN(INDEX(IF(ISNUMBER(A15),A15),,COLUMN(A11))))

returns the first value, instead of the largest of the 4 values that
were returned by the original array formula.

Enter your last formula into a 1x4 cell range and you'll get the same
result as your first formula above. Why? Because Excel applies the
outer MAX function to each, er, subarray returned by its argument, and
each of those is a degenerate array with a single value.

I know what I believe, but do you believe anyone on the Excel
development team could explain these semantics? They've certainly
never bothered to provide any documentation about them.

 

[Guest]

Almost! "<>#N/A" would never have occurred to me, based on the way errors
propagate everywhere else. Thanks for teaching me something.

It doesn't quite do what I need, because #N/A in any data column eliminates
the entire row from every column, which is not appropriate in this
application.

Jerry

 

[Guest]

:
....

These results arise from the INDEX calls, which return arrays of
arrays. Excel copes with this return type the same way it does with
arrays of range references (e.g., INDEX(A15,0,{1,2})) - by using
implicit indexing when entered into multiple cell ranges.
....

Enter your last formula into a 1x4 cell range and you'll get the same
result as your first formula above. Why? Because Excel applies the
outer MAX function to each, er, subarray returned by its argument, and
each of those is a degenerate array with a single value.

"Curiouser and curiouser!" Apparently cell ranges are required to elicit
this behavior. I tried embedding an INDEX() call within the outer MAX() to
obtain the value from a different column, but that only gives #REF!

I know what I believe, but do you believe anyone on the Excel
development team could explain these semantics? They've certainly
never bothered to provide any documentation about them.

I won't hold my breath.

Jerry

 

[Guest]

That seems to work!

Thanks very much,
Jerry

With the entire range of interest named D, try the array formula

=MAX(MOD(LARGE(IF(ISNUMBER(D),D)+10000*(COLUMN(D)-1),
1+(COLUMNS(D)-COLUMN(D))*ROWS(D)),10000)
-MOD(SMALL(IF(ISNUMBER(D),D,10000)+10000*(COLUMN(D)-1),
1+(COLUMN(D)-1)*ROWS(D)),10000))

This relies on adding column number times some big value (10000 above)
to the values in D, so it may run into trouble due to limited
precision. If you want it to adapt to the values in D, replace it with
(MAX(IF(ISNUMBER(D),D))+1).

 

[Lori]

Thanks for the feedback, i didn't think of entire rows being
eliminated. However a tweak to the formula above might do the
trick...with "D" as the extended data range (A17) and the same
criteria, try array-entered:

=MAX(DMAX(D,COLUMN(D),OFFSET(D,,COLUMN(D)-1,2,1))-
DMIN(D,COLUMN(D),OFFSET(D,,COLUMN(D)-1,2,1)))

A slightly simpler approach would be to enter the same column name
above each column e.g. "A" across the first row and use the "<>#N/A"
criteria for "A" in say E1:E2 then try

=MAX(DMAX(OFFSET(A16,,COLUMN(A16)-1,,1),1,E1:E2)-
DMIN(OFFSET(A16,,COLUMN(A16)-1,,1),1,E1:E2))

which appears to give the same results as Harlan's formula.