Abstract
Transcript
In this video we’ll look at three basic array formula examples.
In this first example, we have high and low temperatures for seven days.
We want to calculate the biggest change on any given day.
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 theMAX functionaround 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.
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 theIF function.
If D5:D11 is not empty, return the difference.
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 theTRUNC function.TRUNC stands for truncation.
True to its name, TRUNC simply chops off the decimals.
