I. Intro
The ARRAYFORMULA function is used to display the values returned by an array formula into a range of cells instead of one cell. ARRAYFORMULA also enables the use of non-array functions with arrays.
II. About the function
- Formula: ARRAYFORMULA(array_formula)
- Parameters:
- Array_formula (required): A math expression that uses one or multiple cell ranges. It can also be a function that returns results in more than one cell.
- Example: =ARRAYFORMULA(A1:A8>5)
III. Steps
Use the ARRAYFORMULA function
- Select a cell.
- Enter the formula into the cell. For example: =ARRAYFORMULA(A1:A8>5).
- Press Enter to display the result.
250px|700px|reset
Delete the ARRAYFORMULA function
Select the cell with the ARRAYFORMULA function and press Delete.
IV. Use case
Sales: Determine whether the sales target is met
Using other functions, such as IF, to determine whether a sales target is met involves multiple steps, especially for larger data sets. Calculating the result is a lot simpler with the ARRAYFORMULA function, which only requires one step.
- Formula used below: =ARRAYFORMULA(B2: B7> 32000)
- About the parameters: The formula below calculates whether sales have gone above 32,000, with the results for the entire range displayed in the cells next to it. TRUE means that the target has been reached, and FALSE means that the target hasn't been reached. Here, we can see that only the February target was not met.
250px|700px|reset