#### Response title

This is preview!

Loading...

Copy code

Permalink

Please tell us why you want to mark the subject as inappropriate.
(Maximum 200 characters)

Private Message

Feedback

Private Message

Type the characters you see in the picture below.

Type the characters you see in the picture below.

Attach files

Loading User Profile...

This is preview!

Sign In

New to this Portal? Click here to Sign up

You can also use the below options to login

- Login with Facebook
- Login with Google
- Login with Yahoo

- Search All
- Tags
- Author
- Advanced Search

Move this topic

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.

**How to write an array formula:**

**How to edit an array formula:**

**How to delete an array formula:**

**How to select the whole range where an array formula has been applied:**

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))

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.

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.

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.

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

I'm having several issues with array formulas.

1) When I download a spreadsheet to xls or xlsx they disappear

2) When I upload a spreadsheet with them - I have to go through field by field and hit cntr-sht-enter

3) When I try use a macro to change the spreadsheet name in the macro the macro is deleted leaving only the result.

Any suggestion for me?

Thanks,

Dennis

Leave a comment on denniswillis1's reply

Change topic type

Link this topic

Provide the permalink of a topic that is related to this topic

Reply to Ramesh S's announcement

© 2016, Zoho Corporation Pvt. Ltd. All Rights Reserved.

Aileen.Callahan@thandora.com
Amp.raja@yahoo.com
Aimee.Reece@thandora.com
Ajomah@thandora.com
Al.Kinnamon@thandora.com
Alison.driggs@thandora.com
Amp.raja@zoho.com
Alleycatpro@thandora.com
Allison.short@thandora.com
AlvaresLaw@thandora.com
Alysha.Fields@thandora.com
Apmgopi@gmail.com
Amanda.Gammons@thandora.com
Amanda.Greene@thandora.com
Amber.Earp@thandora.com
Americanweightlosscenters@thandora.com