Forum Discussion
Technical Question: Is a Range argument passed to SUM as a Reference or an Evaluated Array?
Hi everyone,
I have a technical question regarding the internal mechanics of the SUM function's arguments in Excel.
Specifically, I’ve noticed that in the ODF (OpenFormula) specification, the argument is defined as a NumberSequenceList. However, I am unsure how Excel’s internal engine implements this.
When a formula is written as =SUM(A1:B5), what exactly is passed to the function? I am trying to understand which of the following conceptual models is correct:
Reference Passing: A Reference to the range A1:B5 is passed, and the SUM function iterates through the cells internally.
Pre-evaluation: The range A1:B5 is Evaluated into a sequence (or array) of numbers before it is passed as an argument to the SUM function.
I am authoring a technical manual and want to ensure I describe the relationship between cell references and function arguments accurately. Does Excel follow a similar abstraction to the ODF NumberSequenceList, or is there a different internal logic?
Thank you in advance for your insights!
I suspect you need a member of the development team to know for sure or, otherwise someone like Charles Williams who regularly codes in conjunction with Excel. My expectation is that the range A1:B5 remains as a reference until you take some action to force it to convert. For example
= SUM(0+$E$5:$F$16 $D$8:$G$11)
still performs the array intersection before the summation. OFFSET will also pass overlapping range references around if required to do so.
2 Replies
Thank you for your insightful comments.
I have consulted several technical documents, including [MS-OI29500] and [MS-XLSB], which indicate that the SUM function can accept both references and values as arguments. However, I was unable to find definitive information about how the current calculation engine actually implements this internally, which is why I posted the question here.
I agree with your point that confirming this likely requires input from members of the development team or others with deep knowledge of the core engine.
I sincerely appreciate your kind and professional assistance.
I suspect you need a member of the development team to know for sure or, otherwise someone like Charles Williams who regularly codes in conjunction with Excel. My expectation is that the range A1:B5 remains as a reference until you take some action to force it to convert. For example
= SUM(0+$E$5:$F$16 $D$8:$G$11)
still performs the array intersection before the summation. OFFSET will also pass overlapping range references around if required to do so.
