none
How does an array formula work in XLSX? RRS feed

  • 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?

    Wednesday, January 15, 2014 5:59 PM

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 29500-1 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
    Saturday, February 1, 2014 8:28 AM

All replies

  • Hello David -

    Thank you for contacting Microsoft support. A support engineer will be in touch shortly to assist further.

    Thanks


    Tarun Chopra | Escalation Engineer | Open Specifications Support Team

    Wednesday, January 15, 2014 6:45 PM
  • 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 e-mail indicate that it is for me. Be sure that the file doesn’t contain any confidential information.

    Regards,
    Vilmos Foltenyi - MSFT

    Wednesday, January 15, 2014 10:20 PM
  • Hi David,

    Thank you for sending the sample file, I’m going to analyze it and let you know about my findings, soon.

    Thanks, Vilmos
    Wednesday, January 15, 2014 11:57 PM
  • Hi Vilmos;

    Any luck yet?

    thanks - dave


    Who will win The Windward International Collegiate Programming Championships?

    Tuesday, January 28, 2014 3:55 PM
  • Hi David,

    I am still working on this issue. I hope soon I’ll have more information for you. Your patience is greatly appreciated.

    Thanks, Vilmos

    Tuesday, January 28, 2014 5:34 PM
  • 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 29500-1 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
    Saturday, February 1, 2014 8:28 AM
  • 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?

    Saturday, February 1, 2014 5:30 PM