Array formulas

[Lynda]

I have taken over a workbook from a former work colleague. The workbook has a
spreadsheet with array formulas in it. I have never used array formulas
before so have needed to do a crash course of trial and error. When I need to
do something I am unfamiliar with I always make a copy first then I work on
the copy. I needed to update the sheet by removing rows. When I have put the
formulas back in the bottom row comes up with #N/A. I thought perhaps it was
because there were no numbers in that row, but on the original sheet there
were no numbers either and the formula was fine. Can someone please help me
and also tell where I might be able to learn more about how these formulas
work.

Cheers
Lynda

 

[Stefi]

See
www.cpearson.com/excel/ArrayFormulas.aspx
office.microsoft.com/.../HA010872901033.aspx
www.ozgrid.com/Excel/arrays.htm

and many others! Make a Google search with keywords "array formula"!

Regards,
Stefi

„Lynda†ezt írta:

 

[JLatham]

A key point to remember about array formulas is that they must be
committed/terminated with [Ctrl]+[Shift]+[Enter], EVEN AFTER EDITING. Just
using the [Enter] key will not commit them as an array formula.

 

[Shane Devenshire]

Hi,

You might want to take a look at John Walkenback's Formula book for a lot of
examples.

Technically, there are many reasons for N/A but in your case it sounds like
you just need to enter the formulas by pressing Shift+Ctrl+Enter instead of
pressing Enter.

Note that in many case the use of array entry can be replaced by using
SUMPRODUCT instead of SUM, which creates formulas which are generally faster
than array formulas. However to help in that area we would need to see
examples of the formulas.