Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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 "Jerry W. Lewis" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
I think that you would need a macro function to do it in one cell.
"Jerry W. Lewis" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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))) "bj" wrote: I think that you would need a macro function to do it in one cell. "Jerry W. Lewis" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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,,COLUMN(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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
Very slick!
Thanks, 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,,COLUMN(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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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,,COLUMN(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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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 "bj" wrote: 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))) "bj" wrote: I think that you would need a macro function to do it in one cell. "Jerry W. Lewis" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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,,COLUMN(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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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:D1) )) 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(ISNUMBER(A1:D5),A1:D5),,COLUMN(A1:D1) ))) only returns 1, and =MAX(MAX(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN(A 1:D1)))-MIN(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN(A1:D1) ))) 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,,COLUMN(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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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:D1 ))) 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:D1 )))) 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:D1 )))) 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 - |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
"T. Valko" wrote...
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). |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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:D 1))) -MIN(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN(A1:D1) )) in a 1x4 range of cells, you get the four columnwise max-min's, but =COUNT(MAX(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUM N(A1:D1))) -MIN(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN(A1:D1) ))) 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(A1:D5,0,{1,2})) - by using implicit indexing when entered into multiple cell ranges. =MAX(MAX(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN( A1:D1))) -MIN(INDEX(IF(ISNUMBER(A1:D5),A1:D5),,COLUMN(A1:D1) ))) 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
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 - |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
"Harlan Grove" wrote:
.... 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(A1:D5,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 |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array formula that works columnwise?
That seems to work!
Thanks very much, Jerry "Harlan Grove" wrote: "T. Valko" wrote... 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). |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula works but formula shows in cell??? | Excel Worksheet Functions | |||
How to sort the data entered in different columns (columnwise) | Excel Worksheet Functions | |||
Excel same Formula in different columns-calculate columnwise? | Excel Discussion (Misc queries) | |||
IF formula works one way and not the other | Excel Worksheet Functions | |||
How do I convert works file to excel without works software? | Excel Discussion (Misc queries) |