Forum Discussion
Technical Documentation for Excel 365 Dynamic Arrays
This MS Research presentation summarises some technical aspects of array processing in Excel. To quote some of the key slides (in case the link is removed):
"1. Arrays as intermediate values
=SUM( SQRT (A1:A10) )
• De-reference the range to get a vector (= 1-D array)
• Apply SQRT to each element of the vector
• Add up the results
• SQRT has a “signature”: (Number) ->Number2. Auto-lifting/coercion rule: when computing F(X), and the value of X is not acceptable to F (based on its signature) then
• If X is a reference, de-reference it, and try again
• If X is a string, parse it to a number and try again e.g. SQRT( “4.5” )
• If X is an array, apply F to each element of the array3. Auto-lifting
• Works for multi-argument functions too
(*) : (Number, Number) -> Number
SUM( A1:A10 * C2:C11 )
• (*) only accepts numbers; but is given ranges
• So de-reference the ranges, and then map down the two vectors
4. The Signature MattersINDEX : ({Matrix,Range}, Number) -> {Matrix,Range,Number,String,Boolean}
INDEX( A2:A10, {7, 3} ) returns the 2-vector {A8,A4}
• First argument of INDEX is acceptable
• Second argument is not
• So we lift only over the second"A related thread is here: https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2623809
JKPieterse thank you, this is a nice write up. I am still looking for something that goes more under the hood. Some sections of this paper go in that direction: https://www.microsoft.com/en-us/research/publication/higher-order-spreadsheets-with-spilled-arrays/. Maybe I am looking at this the wrong way and the issue isn't really the spill behavior (final stage of evaluation), but more generally how Excel decides in wich order to evaluate nested inputs, especially when some of the interim results are arrays.
This MS Research presentation summarises some technical aspects of array processing in Excel. To quote some of the key slides (in case the link is removed):
"1. Arrays as intermediate values
=SUM( SQRT (A1:A10) )
• De-reference the range to get a vector (= 1-D array)
• Apply SQRT to each element of the vector
• Add up the results
• SQRT has a “signature”: (Number) ->Number
2. Auto-lifting/coercion rule: when computing F(X), and the value of X is not acceptable to F (based on its signature) then
• If X is a reference, de-reference it, and try again
• If X is a string, parse it to a number and try again e.g. SQRT( “4.5” )
• If X is an array, apply F to each element of the array
3. Auto-lifting
• Works for multi-argument functions too
(*) : (Number, Number) -> Number
SUM( A1:A10 * C2:C11 )
• (*) only accepts numbers; but is given ranges
• So de-reference the ranges, and then map down the two vectors
4. The Signature Matters
INDEX : ({Matrix,Range}, Number) -> {Matrix,Range,Number,String,Boolean}
INDEX( A2:A10, {7, 3} ) returns the 2-vector {A8,A4}
• First argument of INDEX is acceptable
• Second argument is not
• So we lift only over the second"
A related thread is here: https://techcommunity.microsoft.com/t5/excel/array-formulas-array-concepts-summary-desired-and-attempted/m-p/2623809
- Thank you - this is exactly the more abstract type of explanation I am looking for - still digesting Simon's presentation, lot's of fodder for thought - delightful 🙂