Array Formulas in Excel

In this lesson you can learn what are and how to use array formulas. The formula (or function) in itself is not an array or non-arrayed.You decide how the Excel formula is treated. Array is not so much a feature of the same formula, but rather the way that Excel interprets the formula specified. Acceptance of a formula using the key combination {Ctrl + Shift + Enter} for Excel is the order for carrying it in an “array”, having arrays passed as arguments and results obtained.Some of Excel functions by default, as an argument, adopts a range of cells (array) and consequently returns a single value. Outstanding examples are the functions SUM, COUNTIF, AVERAGE, etc. If these features does not matter whether you enter them as functions of the array or not. They still operate on the tables and find the (work properly) in any situation. Fortunately, the weapon of honor other functions that work differently depending on your decisions about their array (sometimes not at all want to work). A perfect example would be the IF function.

When an array formula, and when the regular?

An array formula (entered as an array) you use wherever you want to make a feature that “every day” operates on a single value (cell), suddenly behaved differently, and adopted as an argument and returned as a result of an array of values. Returning to the aforementioned IF function. As an argument it accepts a Boolean value of TRUE or FALSE. In the classic form:

=IF($B$7>”bigger”,”smaller”)

if the value in cell B7 is greater than zero, the function receives as an argument to TRUE and returns a string result “bigger”. However, if you wanted to see more single cells and the outcome of such consultations pass to yet another feature you should use the array version of the below formula:

{=IF($B$7:$B$10>”bigger”,”smaller”)}

IF function Array

Function takes as argument the whole range $B$7: $B$10 The examination of each cell in the memory array is formed

{TRUE, FALSE, TRUE, TRUE}

Then, we create another array, whose values depend directly on values in the first array. If in the first array element is TRUE, the second array will be set to “bigger”. If it is set to FALSE in the second array element takes the value “smaller”. After this operation, the first array is removed from the computer’s memory and, ultimately, the function returns the following array {“bigger”, “smaller”, “bigger”, “bigger”}.

In my example, I entered an array formula to one cell (as seen in the picture above), so as a result I received only a single value corresponding to the first value in the array. But it is enough to introduce our array formula to a range of cells to see all of the scoreboard. In the example (shown below) shows that the resulting array contains exactly four elements that I mentioned above.

IF function array formula

Of course, the array returned by the function if it can be passed on, “work” as an argument to another function.

Example 1 – array SUM function

Imagine that you want to sum the cells of B7: B10, but only those that are bigger than zero. Of course you can use the SUMIF function, however, in our example we want to do only with the array formula. Summing up our range of cells will need to somehow get rid of the value of -32. For the SUM function you have to pass an array, which will have only values greater than zero. Wherever value is less than zero, we put a zero, which of course will not affect the result. As you know, a temporary table with values you can get using the IF function. Finally, the formula would look like this:

{=SUM(IF($B$7:$B$1>0,$B$7:$B$10,0))}

SUM function IF function array

The examination of each cell in the range $B$7: $B$10 (or greater than zero) in the memory array is formed {TRUE, FALSE, TRUE, TRUE}. Then another table is created. If the first array element is TRUE, the second table shows the value of that cell. If the value is “false”, the second element in the array may be zero. After this operation, the first array is removed from the computer’s memory and, ultimately, the IF function returns the array {23, 0, 15, 7}. This array is then passed as an argument to the SUM function, which, in accordance with its intended purpose, returns the sum of all elements in the array. In our example 45. Finally, see what happens even if we ask Excel treats the above formula in an array of notes.

IF function SUM function non-array

None of the above tables will not be in such a case created. Only one cell range to be tested (cell line, in which the function). In our case 15> 0, so the first argument of the IF function receives the value TRUE. Then passed to the SUM function is the entire range B7: B10, and consequently the function returns a value of 13 (23-32 15 +7). If the cell instead of 15 was the number less than zero IF function as an argument to be given the value FALSE, and consequently to the SUM function would be given only the value zero. Our SUM would return the result as zero.

Example 2 – array transpose function

TRANSPOSE function is a simple example of Table Functions that Excel offers quite a lot. Because they are rarely used in business and are useful primarily for advanced math and statistics, the other array functions will not be discussed in this course. Select range F3: H3, in which you want the resulting array, the introduction of array functions always start by pressing the F2 key.

You type an array function =TRANSPOSE (C3: C5) following array functions always:

TRANSPOSE function array

You end typing pressing Ctrl + Shift + Enter.

TRANSPOSE function array formula

As a result of these activities in cells F3: H3, we feature an array stored in curly braces {}. Although the function ‘looks as if it was placed in three cells, this is one feature, and make changes in any of the cell causes changes in all cells.

Changes to the beginning of pressing F2, and after the introduction of ‘exit’ from an array function by pressing Ctrl + Shift + Enter.

Excel TRANSPOSE array formula

In case of an array of arrays change is not possible, such as in the example above, you can not add a column between the ‘F’ and ‘G’. There will also be possible to introduce any changes other than by pressing F2 to start and end with Ctrl + Shift + Enter. For all such attempts will be displayed:

Microsoft Excel array functions message

The function simply delete the array by selecting the entire range in which it is located and press ‘Del’.

I was a little on this. It was to be only about identifying the function of array, and ended up on the description of how they work. It seems to me that the proper “diagnosis” is entirely dependent on an understanding of how the arrays in formulas. When you realize you could say that a particular formula should be entered as an array formula.

Of course, introduced as a non-arrayed can return some output (which you may have to just convince yourself). But if you understand the mechanism, you will see that this result is poor and to work properly it is necessary, however, Ctrl + Shift + Enter. Like anything, understanding and use of the array formulas takes practice. But it is worth spending some time to understand everything. Tables in the formulas allow for the solution of many problems that at first glance appear to be unsolvable.

Template

You can download the Template here – Download