Answered by:
How does an array formula work in XLSX?
Question

I have a formula which clearly applies to several cells. Here's the row with the formula itself in B12 and it is applied in B13 (and further down but this is enough I think):
<row r="12" spans="1:5" x14ac:dyDescent="0.25"> <c r="A12" s="5" t="e"> <f ca="1">B7</f> <v>#NAME?</v> </c> <c r="B12" t="e"> <f t="array" aca="1" ref="B12:B16" ca="1">FREQUENCY(B3, A12:A15)</f> <v>#NAME?</v> </c> <c r="C12" s="6"/> </row> <row r="13" spans="1:5" x14ac:dyDescent="0.25"> <c r="A13" s="5" t="e"> <f ca="1">A12+B9</f> <v>#NAME?</v> </c> <c r="B13" t="e"> <f ca="1"/> <v>#NAME?</v> </c> <c r="C13" s="6"/> </row>
18.3.1.40 discusses the <f> element and what t="array" means in general. But it does not discuss how it works in practice. Does the formula get evaluated once and it then applies its results to all of the cells in ref="B12:B16" as their value? Or does something else occur?
thanks  dave
Who will win The Windward International Collegiate Programming Championships?
Answers

Hi David,
In your sample spreadsheet there is only one FREQUENCY function, it is in your posted snippet, too
<f t="array" aca="1" ref="B12:B16" ca="1">FREQUENCY(B3, A12:A15)</f>From the description of the function, it “returns a vertical array of numbers”, this case is explained in ISO/IEC 295001 section 18.17.2.7 which is referenced from 18.3.1.40 mentioned in your post. The function will be called once and the cells B12:B16 are populated with the resulting array values. The section 18.3.1.40 f (Formula) for ca=”1” states “this formula needs to be recalculated the next time calculation is performed”. I hope this answers your question.
Thanks, Vilmos
 Marked as answer by DavidThi808 Saturday, February 1, 2014 5:30 PM
All replies


Hi David,
I am the engineer who will be working with you on this issue. Your posting contains a snippet indicating you have a sample file, in order to see the whole picture, please post the file or send it as attachment to ‘dochelp (at) microsoft (dot) com’ and in the email indicate that it is for me. Be sure that the file doesn’t contain any confidential information.
Regards,
Vilmos Foltenyi  MSFT 

Hi Vilmos;
Any luck yet?
thanks  dave
Who will win The Windward International Collegiate Programming Championships?


Hi David,
In your sample spreadsheet there is only one FREQUENCY function, it is in your posted snippet, too
<f t="array" aca="1" ref="B12:B16" ca="1">FREQUENCY(B3, A12:A15)</f>From the description of the function, it “returns a vertical array of numbers”, this case is explained in ISO/IEC 295001 section 18.17.2.7 which is referenced from 18.3.1.40 mentioned in your post. The function will be called once and the cells B12:B16 are populated with the resulting array values. The section 18.3.1.40 f (Formula) for ca=”1” states “this formula needs to be recalculated the next time calculation is performed”. I hope this answers your question.
Thanks, Vilmos
 Marked as answer by DavidThi808 Saturday, February 1, 2014 5:30 PM

Hi;
18.17.2.7 is what I needed (missed that in 18.3.1.40).
thanks  dave
Who will win The Windward International Collegiate Programming Championships?