Zoho Sheet now supports Array Formulas. An array formula is a formula that can perform multiple calculations on one or more items in an array. Array formulas can return either multiple results or a single result. They provide you with more power to your calculations in your spreadsheets.
For example, the following spreadsheet uses Array Formulas. The formulas used are explained below the embedded spreadsheet.
Here are the array formulas used:
- Multi-cell array formulas are used in the Current Price, Change and My Portfolio columns.
For example, the Current Price column has the following formula in all the 5 rows, i.e in cells C6:C10.
=STOCK(B6:B10;"Price")
For each cell in the array, the corresponding cell from its argument, based on the index, is taken and then evaluated. For cell C6 (the first cell in the array), the value in cell B6 (the first cell in the range argument) is used to evaluate the STOCK() function. For cell C7, value in cell B7 is used, and so on.
The 'My Portfolio' column has the following formula in all the 5 rows:
=stocksowned*currentprice-stocksowned*costprice
Here, 'stocksowned' is a named range referring to the range F6:F10, 'currentprice' refers to C6:C10 and 'costprice' refers to E6:E10.
This can also be done without using array formulas. But when you use Array formulas, the formulas will be the same in all the cells and hence there is less possibility of errors in your spreadsheet. You can quickly find out if there is any discrepancy. You can also select one of the cells which has the array formula and press Ctrl+/ to select the whole range where this array formula is applied. This way, you can be sure that your formulas are right.
- The following single-cell array formula is used for the Total in cell G12. It finds the net profit / loss for each stock owned, sums up all the 5 rows and displays the result.
=SUM(C6:C10*F6:F10-E6:E10*F6:F10)
The advantage here is that the SUM value in this case is calculated without requiring an helper column in the 'My Portfolio' column. You can also SUM values based on a criteria, for example: add only the positive numbers in a range of cells. The formula for this would be as follows:
=SUM(IF(G6:G10>0;G6:G10;0))
How to write an array formula:
To write an array formula, you need to select the cells where you want to apply this formula, type the formula and then press Ctrl+Shift+Enter. This will surround your formulas with curly braces indicating that it is an array formula.
How to edit an array formula:
Select the entire range where you have applied the array formula. Press F2 to edit the 1st cell in the range. Modify the formula as needed and press Ctrl+Shift+Enter.
How to delete an array formula:
Select the entire range where you have applied the array formula and then press the 'Delete' key or select 'Clear Contents' from the right click menu to delete an array formula.
How to select the whole range where an array formula has been applied:
Select a cell with array formula. Press Ctrl+/. This will select the entire range where this array formula is used.