Array Formulas in Zoho Sheet

Array Formulas in Zoho Sheet

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:
  1. 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.

  2. 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.


We hope you find this useful. Learn more about Array Formulas:  http://www.google.com/search?q=array+formulas


      • Sticky Posts

      • Slicers are now available in Zoho Sheet—filter your data interactively

        At Zoho Sheet, we diligently track user requests and feedback. In line with this, based on extensive user requests, we've integrated Slicers to pivot tables and are delighted to announce its release. Slicers are interactive visual filters that have add,

      • Check out the new Checkbox in Zoho Sheet

        Checkbox, the simple GUI widget that lets you choose between two mutually exclusive options i.e Yes or No, is now in Zoho Sheet. Having made it across the web, Checkbox is familiar but is still elusive to most spreadsheet software. Special developer ribbons, macros and all other complexities to add a checkbox to a spreadsheet? Not in Zoho Sheet. How to add Checkbox to Zoho Sheet? Adding a Checkbox in Zoho Sheet is simple. Select a cell/range and choose "Checkbox" in "Input Controls" under the Format

      • Format and analyze spreadsheet data with Zoho Sheet's Table

        While a spreadsheet helps you compile data for easy analysis, formatting helps make your data more comprehensible. We are happy to announce the release of Table, a crucial tool for improving data formatting in Zoho Sheet. More than a formatting tool,

      • Introducing Data Bars: Graphically represent changes in data within the cells

        Conditional formatting has helped millions of spreadsheet users analyze and highlight their data more efficiently. In addition to the classic rules, color scales, and icon sets available in Zoho Sheet, you can now apply Data Bars, a convenient method

      • Zia in Zoho Sheet now makes suggestions on conditional formats, picklists, and checkboxes

        Zia, Zoho's popular AI assistant, has been helping users succeed across Zoho applications since inception. In Zoho Sheet, Zia helps users efficiently build reports and analyze data with recommendations about data cleaning, charts, and pivot table. Also,