Knowledge

"The first opinion that is formed of a ruler's intelligence is based on the quality of the men he has around him."

-- Niccolo Machiavelli

AA Arrays Introduction

Getting started with array formulae

By Neale Blackwood, 13 Jan 10

Introduction

Array formulas are usually considered to be either black voodoo magic, or an advanced topic.

We think this could be one reason why many people don't use them.

Another reason is that they can slow down calculation time - less of an issue with today's PC's but still a consideration if you use them a lot.

But if you would like to use them, here's some info on how to do it.

Entering an Array Formula

You have to enter array formulas in a special way.

Once you create the formula you must hold the Ctrl and Shift keys and then press Enter.

This is commonly referred to CSE (Ctrl Shift Enter) and it puts curly { } brackets around the formula.

If you edit the array formula you must press CSE when you update it. If you forget, the formula may show an error message or contain an incorrect result.

We'll see if we can shine some light ("array of sunshine" you might say!) on arrays to make them easier to understand and use.

We'll start with a single cell array formula.

Example

Let's say you have two columns (C, D). Both columns have distance values in metres. An extra Column (E) contains the difference on each row between these columns (C,D).  

You need a single formula to add up all the differences on each row. You want to find the total distance difference, regardless of any negatives. So whether the row difference is -10 or 10 we want to use 10. So we can't use normal SUM formula.

One array formula can do it all!

In this example, Excel goes through rows 1 to 15 and deducts the value in column D from the value in column C and retains each value. Then all these values are converted to their absolute values and summed.

Print   Email Share:

Be the First to Comment!

Related Articles

Array formula to SUM rounded values - I format many of my reports to zero decimal places. This means that in some cases the SUM function will show a different total than the displayed numbers indicated. If I format two numbers 5.5 and 7.5 with zero decimal places, they will display as 6 and 8 respectively. When summed that result is 13. Can I get the SUM function to SUM the displayed values, in this case 14?

The solution is your problem is an array formula. Many of the limitations of Excel's functions can be overcome by using an array formula ...

Formula for the last used cell in a column - Is there a formula to find the last used cell in a column?

You can use an array formula to find the last used cell reference. The following formula will find the last used cell in column A, except if the last used cell is A1048576 ...

Last Used Cell in a Row - Find the reference of the last used cell in a row

One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles ...