Array Formula in MS-Excel or LibreOffice Calc

Rating 4.00 out of 5
[?]

Preparing MS-Excel based test beds is what my job is, as boring as it sounds it helps me learn new things and understand how powerful a tool MS-Excel really is and use it to get the job done. In this tutorial I am going to share a learning I came across recently, the use of array formulae. For beginners the term Array Formula(AF) can be little intimidating, but actually its as simple as the problem you want to solve and a little knowledge of the basic formulae which you will actually use to formulate the main formula will make you realize how easy this entire process is.

I am using LibreOffice Calc for this tutorial but the same process can be carried out in MS-Excel too. So jumping in, lets take a very simple example to start with. In your spreadsheet application copy the following two columns Number Sold and Unit Price to column A and B respectively (see the screenshot attached).
NUMBER SOLD UNIT PRICE
5                           2200
4                           1800
6                           2300
8                           1700
3                           2000
1                           1600
9                           2150
5                           1950
6                           2250
8                           2000

Now I want to find the total sales for each row. For this lets perform the following steps:

  1. Select the cells C2:C11 and enter =A2:A11*B2:B11 in the formula bar.
  2. Press the Key combination, CTRL+SHIFT+ENTER.

After this process is done, you’ll see the following entries get populated against each row.

If you navigate across the cell rage C2:C11, you’ll notice that all the cell have the same formula, notice the extra {} that enclose the formula which we had entered; this actually tells your spreadsheet application to process the formula as an AF. Now try deleting one of the cell in the range, you wont be able to 🙂 . Which is fine as all of them use the same formula. Now lets go one step ahead, try finding the total sum in one go(please do not take the easier way out by summing the values we found just now 😛 ). You guessed it right, this is how it’ll be done

  1. Select cell C12 and enter =SUM(A2:A11*B2:B11) in the formula bar.
  2. Press our favourite key combination 🙂 CTRL+SHIFT+ENTER to obtain the required result.

Simple isn’t it? Now to a little bit of theory so that we are able to expand our horizons and vision so that we can use this concept well.

If you’ve done even a little programming, you’ve probably run across the term array. For the purpose of this tutorial, an array is just a collection of items. In spreadsheet applications, those items can reside in a single row (called a one-dimensional horizontal array), a column (a one-dimensional vertical array), or multiple rows and columns (a two-dimensional array). You can’t create three-dimensional arrays or array formulas.

An array formula is a formula that can perform multiple calculations on one or more of the items in an array. Array formulas can return either multiple results or a single result. For example, you can place an array formula in a range of cells and calculate a column or row of subtotals. You can also place a formula in a single cell and calculate a single amount. An array formula that resides in multiple cells is called (logically enough) a multi-cell formula, and an array formula that resides in a single cell is called a single-cell formula.

Now lets make things a little complicated, consider this case, I want to find the average of the those sales which fall in top 50% of all the sales. 🙂 . So, lets see how to do this :

  1. Finding out which all formulae that we have to use
    1. Average
    2. Conditional statement (IF)
    3. Percentile to get top 50%
  1. Formulate the main formula now. It’ll look like this

        =AVERAGE(IF($C$2:$C$11>PERCENTILE($C$2:$C$11,0.5),$C$2:$C$11))

3. And CTRL+SHIFT+ENTER and the braces will appear.

Final advice, if you do not press CTRL+SHIFT+ENTER the formula will give an error. Now let your imagination free and try this out.

Anshuman

About Anshuman

Computer Engineer ... MBA .. Endurance Runner ... Physics Geek ... Genius
Bookmark the permalink.

Liked it? Share Your Thoughts!

This site uses Akismet to reduce spam. Learn how your comment data is processed.