Hi all
I'm having no luck working this one out. I'll try to spell this out really clearly, since I don't have a file to upload at the moment. I will come back and add one tomorrow if not easily solved without it (or if I discover that it is required under the forum rules).
I have a spilled range across 5 rows and a dynamic number of columns. The number of rows or columns don't really matter, to be honest - they should be able to be adjusted for the solution to still work. Assume A1 = RANDARRAY(5,3). This will give a spilled range with random numbers of size 5 x 3.
I want to be able to SUM each column in the Spilled range separately, and for the SUM to form a spilled range (1 row high, dynamic columns based on RANDARRAY width). Although I specify SUM, as long as the outcome gives the same result mathematically as a SUM, it doesn't matter what the formula is. Thus a single formula in cell A6 which generates a spilled range in row 6.
The SUM needs to be part of a spilled range so that it will dynamically adjust to the model parameters (e.g. if the RANDARRAY formula changes to (5,4), there will be another column SUMmed).
The SUM can't be a single result for the entire spilled range (e.g. SUM(A1#) ) - it needs to be the SUM of the column that it sits in (separate results for A, B, and C based on the example formula provided).
By embedding within SEQUENCE, I can create a spilled SUM range, but a number of different syntactic attempts have failed to give the correct outcome.
As a workaround, I could generate 5 single row arrays of dynamic column width and simply add them together ( = A1# + A2# ... + A5#). I'm looking for a way that doesn't require separating the spilled ranges and which doesn't require lots of cell references.
Thank you.
I'm having no luck working this one out. I'll try to spell this out really clearly, since I don't have a file to upload at the moment. I will come back and add one tomorrow if not easily solved without it (or if I discover that it is required under the forum rules).
I have a spilled range across 5 rows and a dynamic number of columns. The number of rows or columns don't really matter, to be honest - they should be able to be adjusted for the solution to still work. Assume A1 = RANDARRAY(5,3). This will give a spilled range with random numbers of size 5 x 3.
I want to be able to SUM each column in the Spilled range separately, and for the SUM to form a spilled range (1 row high, dynamic columns based on RANDARRAY width). Although I specify SUM, as long as the outcome gives the same result mathematically as a SUM, it doesn't matter what the formula is. Thus a single formula in cell A6 which generates a spilled range in row 6.
The SUM needs to be part of a spilled range so that it will dynamically adjust to the model parameters (e.g. if the RANDARRAY formula changes to (5,4), there will be another column SUMmed).
The SUM can't be a single result for the entire spilled range (e.g. SUM(A1#) ) - it needs to be the SUM of the column that it sits in (separate results for A, B, and C based on the example formula provided).
By embedding within SEQUENCE, I can create a spilled SUM range, but a number of different syntactic attempts have failed to give the correct outcome.
As a workaround, I could generate 5 single row arrays of dynamic column width and simply add them together ( = A1# + A2# ... + A5#). I'm looking for a way that doesn't require separating the spilled ranges and which doesn't require lots of cell references.
Thank you.
