• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What does an Array formula do?

Rakesh Menon

New Member
I am not sure if this has been asked before. In the attached excel sheet, I have two sets of the same numbers - one with the array (curly brackets) and one without.

The results for these two are completely different. I would like to understand what the Array action does to the formula.

I got this on a file from one of the colleagues and did not understand why the number with the array is different, when the formula on the face of it should give a different result.
 

Attachments

  • Array formula example.xlsx
    10.5 KB · Views: 13
Hi Rakesh ,

The most complete understanding comes when you do it and see for yourself.

The Evaluate Formula button is your best assistant ; place the cursor in Q12 and press this button , and go from one step to the next , seeing the evaluation window for the intermediate results. Do the same with the cursor in Q6.

Narayan
 
Thanks Narayan. That helps to understand what happens. Still am wondering why we would use the curly brackets. What's the purpose? Why not have a straightforward formula instead?
 
Curly brackets are Excel's way of indicating an array formula. These are not inserted "manually" but show up when you do CTRL+SHIFT+ENTER (which is also called Array Entering a formula).

Array formulas are very powerful as they can do very complex calculations which may involve several helper cells otherwise.

And answer to your second question is again in your example. A formula which needs to be array entered if entered normally then would give incorrect result(s). As the name suggests, array entry is not limited to one cell. But an array can also be having one element or cell which is the case with your example. It is little misleading in that sense. If you check couple of examples involving FREQUENCY and TRANSPOSE it will be easier for you to understand the multiple cell usage part.

Crude example of changing way of entry so that application understands you expect different style / result is perhaps this:
If you were to enter your name "Rakesh Menon" in cell A1:A10 then you will probably type in cell A1 and then copy paste it or do CTRL+D for selection.
Or
You select complete range A1:A10 and then type in "Rakesh Menon" and press CTRL+ENTER. Excel will understand the difference in entering the data.

Hope this helps.
 
Back
Top