Why use array formulas? In this video we'll look at three basic array formula examples.
The latest version of Excel ships with new functions like UNIQUE, SORT, FILTER and so on that make certain array formulas easy.
But you can still build traditional array formulas as well, and they can solve some tricky problems.
In this first example, we have high and low temperatures for seven days. We want to calculate the biggest change on any given day.
This requires an array formula, because we don't have a column in the data that calculates the change.
I'll start by subtracting the lows from the highs. This is an array operation, and we get all 7 results in a dynamic array.
Now all I need to do is wrap the MAX function around this formula. MAX then returns the largest value, which is 32.
Next, we have the same data, but notice Friday and Sunday are are missing low values.
Our formula in F5 is now reporting 88 as the biggest change, since blank cells are treated like zero in a formula.
We need a way to filter out the empty cells.
I'll first remove the MAX function and let the differences spill onto the worksheet again.
Now, to remove cells that are empty, I'll use the IF function.
If D5:D11 is not empty, return the differences.
I don't need to add a value if false, because IF will automatically return FALSE. If the logical test is FALSE.
When I enter the formula, you we now get FALSE for the two missing values.
Now I can use the MAX function again. MAX automatically ignores TRUE and FALSE values, so the result is again 32.
In the last example, we have a list of numbers that contain decimal values. Our goal is to return the sum of these numbers not including the decimals. In other words, we want the sum of the integers only.
How can we do this with an array formula?
This is a case where it makes sense to feed the array into another function for processing.
I'm going to use the TRUNC function. TRUNC stands for truncation. True to its name, TRUNC simply chops off the decimals. Now all I need to do is wrap the SUM function around the formula, and we have the sum of whole numbers only.
As you can see, traditional array formulas can solve some tricky problems, and building array formulas in the dynamic array version of Excel is easier than ever.
The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....
The Excel TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.