An array is a table containing values. An array formula used to perform multiple calculations on “n” number of data or items in an array. Google sheet array formula also performing multiple calculations in rows and columns.
Usage of array formula helps us to ensure accuracy and safety of data in our operations. Hence it reduces the risk of error or overwrites in the google sheet.
So now let’s know how this function works in Google sheet.
Syntax
Syntax
ARRAYFORMULA(array_formula)
Here the ‘array_formula’ may be
- a range
- a mathematical expression using one cell range or multiple ranges of the same size, or
- a function that returns a result greater than one cell
Table of Contents
Google sheet Array with a Simple Example
Created an array below shows five products, its cost, price and quantity
To find the profit of the product(Price-Cost* Qty), What we will do?
To find the profit of the product(Price-Cost* Qty), What we will do?
Normally we will enter the equation in the array as shown below. But this equation is applied to only one cell in the array(profit).
If we want to find the profit for all the products, we need to just drag the equation to down to all the cells corresponding to the products.
Array Formula in Google sheet
Refer the picture below, here you can see how to use the array formula in google sheet.
In “profit” column, we have to enter the formula as
=ARRAYFORMULA((D4:D8–C4:C8)*E4:E8)
then press the Enter Key.
When we complete this process, all the rows in the “Profit ” column will be filled with the amount of profit as shown below.
If you want to get updated the “profit” column whenever you enter new product details in the sheet, you just alter the formula from the first cell in the “profit” column as shown below.
=ARRAYFORMULA((D4:D–C4:C)*E4:E)
But in the above table, we can see “Zero” in the “profit” column as we did not enter the corresponding the product data. To avoid showing the zero in the “Profit” column, we have to change the formula with ‘IF function’ as below.
=ARRAYFORMULA(IF(B4:B<>“”,((D4:D–C4:C)*E4:E),“”))
This formula will help the Google sheet to clear the “zero” in the cells where we applied our formula as shown below and the profit will be calculated whenever you enter the new product details in the sheet.
Selection of Unlimited Rows
You may notice that the formula contained C4:C, D4:D,E4:E. All these are representing columns of unlimited rows. Once you enter the C4:C, the google sheet selects the entire rows in the column ‘C’ from the fourth cell onwards(C4). Please refer the table to the right
Array Formula with Ctrl+Shift+Enter
Once you enter the formula in a single cell as given below, then press Ctrl+Shift+Enter keys. It will automatically create an array formula
This is a very simple example used to explain the basic concept of the array formula in the google sheet. If you want to know more about array formula, please click here
Please comment and share it with social media and your friends.