Technical Documentation for Excel 365 Dynamic Arrays

I am pretty excited about the new spilling arrays, and I would like to get a better sense of what is going on under the hood. Like how does Excel 365 figure out what shape a result has what can I ...

Test.xlsx12 KB

excel

Formulas and Functions

office 365

lori_m
Sep 22, 2021
ThomasHartl
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

ThomasHartl
Copper Contributor
Sep 22, 2021
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.
- lori_m
  Iron Contributor
  Sep 22, 2021
  ThomasHartl
  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
  - ThomasHartl
    Copper Contributor
    Sep 27, 2021
    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 🙂

Forum Discussion

Technical Documentation for Excel 365 Dynamic Arrays

Resources