Array formula that works columnwise?
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 (A1:D7) 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(A1:D6,,COLUMN(A1:D6)-1,,1),1,E1:E2)-
DMIN(OFFSET(A1:D6,,COLUMN(A1:D6)-1,,1),1,E1:E2))
which appears to give the same results as Harlan's formula.
On 16 Apr, 16:26, Jerry W. Lewis wrote:
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
"Lori" wrote:
If the data starts in row3 then maybe try array-entered:
=MAX(DMAX(A1:D7,COLUMN(A1:D7),A1:D2)-DMIN(A1:D7,COLUMN(A1:D7),A1:D2))
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
On 16 Apr, 10:44, Jerry W. Lewis wrote:
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(A1:D5),A1:D5),,COLUMN(A1:D1 )))-MIN(INDEX(IF(ISNUMBER**(A1:D5),A1:D5),,COLUMN(A1:D 1)))
in a 1x4 range of cells, you get the four columnwise max-min's, but
=COUNT(MAX(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN (A1:D1)))-MIN(INDEX(IF(IS**NUMBER(A1:D5),A1:D5),,COLUMN(A1:D 1))))
only returns 1, and
=MAX(MAX(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN(A 1:D1)))-MIN(INDEX(IF(ISNU**MBER(A1:D5),A1:D5),,COLUMN(A1:D 1))))
returns the first value, instead of the largest of the 4 values that were
returned by the original array formula.
Jerry
"T. Valko" wrote:
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
"Jerry W. Lewis" wrote in message
...
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
"T. Valko" wrote:
Hmmm.....
I wasn't sure what you meant but after seeing bj's replies...........
Range = A1:D5
Array entered:
=MAX(SUBTOTAL(4,OFFSET(A1:A5,,COLUMN(A1:D5)-1))-SUBTOTAL(5,OFFSET(A1:A5,,CO**LUMN(A1:D5)-1)))
Biff
"Jerry W. Lewis" wrote in message
...
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- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
|