A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
SCAN function by column on a dynamically varying number of columns
I have need to run an accumulator function on several columns of data in Excel. Columns have been assembled from disparate data sources into a single 2D array, but number of columns in the array will vary.
I can perform the required (phase unwrap) operation on a single column, as follows:
=SCAN(
0,
INDEX(phase!$A$2#,1,1):INDEX(phase!$A$2#,ROWS(phase!$A$2#),1),
LAMBDA(a,b,
IF(
ISNUMBER(INDIRECT(ADDRESS(ROW(b)-1,COLUMN(b),,,"phase"))),
IF(
b-INDIRECT(ADDRESS(ROW(b)-1,COLUMN(b),,,"phase"))>180,
a+b-INDIRECT(ADDRESS(ROW(b)-1,COLUMN(b),,,"phase"))-360,
a+b-INDIRECT(ADDRESS(ROW(b)-1,COLUMN(b),,,"phase"))
),
b
)
)
)
And I can perform this on subsequent columns by simply changing the column index in line 3:
=SCAN(
0,
INDEX(phase!$A$2#,1,99):INDEX(phase!$A$2#,ROWS(phase!$A$2#),99),
LAMBDA(a,b,...
The actual phase-unwrap function contained within the LAMBDA(a,b,...) isn't critical.
Simply allowing SCAN() to operate on the entire 2D array seems to allow no means for keeping columns separated, it will run any function within LAMBDA() across crolumns, whereas I need to only perform the arithmetic within each column, never across columns.
I have tried nesting this SCAN() inside of a MAKEARRAY(), first using MAKEARRAY(1,columns(phase!$A$2#),...), and then MAKEARRAY(rows(phase!$A$2#),columns(phase!$A$2#),...), but both failed. I know why in both cases, but don't know how to fix it.
I have also tried nesting BYCOL(), which obviously failed for similar "single value" reason as MAKEARRAY(1,columns(phase!$A$2#),...).
Desired output is a 2D array or group of column vectors.
Snippet of input array (phase worksheet) is as follows:
Snippet of desired output is as follows:
If you're interested, you can see the nested IF() statements are essentially adding the difference in phase of each subsequent row to the output of the prior row, subtracting 360 degrees whenever the phase winds forward by the same, as detected by a phase difference > 180 between two rows. Again... doesn't really matter for the problem of arranging the array of SCAN() functions.
Thanks!