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 to wrap the MAX function around this formula. MAX then returns the largest value, which is 32. Next, we have the same data, but notice that Friday and Sunday 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 empty cells. I’ll first remove the MAX function and let the calculated 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 difference. 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, 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 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
