Friday, 28 September 2018

How to Use Array Formula in Google Sheet


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

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


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?

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

Array Formula in Google sheet


Refer the picture below, here you can see how to use the array formula in google sheet. 

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.

Array Formula in Google Sheet


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

Array formula in Google sheet

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.

Qatar Airways