14Apr2015

Using Array Formulas in Excel

An Array formula works with an array. You may see array formulas referred to as “CSE formulas,” because you press CTRL+SHIFT+ENTER to enter them into your workbooks.

The following is a list of functions in Excel that use arrays:

  1. LINEST()
  2. MDETERM()
  3. MINVERSE()
  4. MMULT()
  5. SUM(IF())
  6. SUMPRODUCT()
  7. TRANSPOSE()
  8. TREND()

In general, array is a collection of items or values. An array formula is a formula that can perform multiple calculations on one or more of the items in an array. They can return either multiple results or a single result. An array formula that resides in multiple cells is a multi-cell formula, and an array formula that resides in a single cell is a single-cell formula, typically using SUM, AVERAGE, or COUNT, to return a single value into a single cell. Array formula will speed up creating formulas by eliminating unnecessary calculations. Array formulas are considered as a powerful tool in Microsoft Excel.

 

Example 1: How to Transpose using an array formula.

In general we know how to use Transpose by ‘Paste Special Transpose’ option to switch rows to columns or columns to rows. You can also use the TRANSPOSE function to achieve this. But here, we use array to do the transpose with a nifty feature which helps to link the Transposed data with the main table (Available for older versions of Excel only and not available in office 365).

Assume that we have data which is laid out in Columns and now we are interested in having it in Rows as shown in the image below.

array formulas 1

To do that, now let’s create a table with 4 columns and 3 rows to accommodate the data.

array formulas 2

Now, select the whole table and put the Transpose function as shown in image below. As this is an array function we need to press CTRL+SHIFT+ENTER (CSE) instead of just hitting enter. Array function will not work unless we use “CSE”.

array formulas 3

Now the table is generated as shown in the image below. As we can see in the formula that the Transpose function is enclosed in the curly braces indicating that it’s an array function.

array formulas 4

We cannot delete the data from a single cell or delete a row from the newly generated table as it is an array and it will show the following message when a user does it.

array formulas 5

To delete the data and redo it, you need to select the whole range and delete all the data in the second table.

 

Example 2: Let’s see how we can derive Commission for a sales person

Assume we have a sales person and we have a table of data with sales figures as shown in the image below. We are interested in finding his commission without much of a calculations. To achieve this we use array in Look up function.

 

array formulas 6

To calculate Total Commission we use Sum Product with Lookup function. Lookup function can deal with arrays.

=SUMPRODUCT(LOOKUP(B12:B18,D13:E19))

Here Lookup value is B12:B18 which are sales figures from Jan to Jul.  D13:E19 is the look up array and sum product returns the sum of the products of arrays. This results in $3000 as his Total Commission.

array formulas 7

 

Points we need to remember while using Array Formulas:

  1. Use arrays of equal size.
  2. To create an array formula you need to hit CTRL+SHIFT+ENTER
  3. We cannot edit Individual cells if they are in the array
  4. Rows/Columns cannot be deleted if they are in the array.
  5. Array calculations are limited based on the available memory on computer.
  6. Too many array formulas will slow down recalculation.

 

Have any Queries? Feel free to contact us here

  • 14 Apr, 2015
  • Excel for Commerce
  • 0 Comments
  • array formulas, Excel Consultant, Excel Expert,

Categories

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>