#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 115
Default Array formulas

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,646
Default Array formulas

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:

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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default Array formulas

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.

"Lynda" wrote:

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Array formulas

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Lynda" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formulas Joe Gieder Excel Worksheet Functions 0 February 19th 09 08:05 PM
Help With Array Formulas... Carl Excel Discussion (Misc queries) 6 February 12th 07 08:51 PM
array formulas Nader Excel Worksheet Functions 16 November 14th 06 04:32 PM
Array formulas Brad Excel Worksheet Functions 2 December 31st 05 03:12 AM
array formulas R.VENKATARAMAN Excel Worksheet Functions 7 June 16th 05 10:25 PM


All times are GMT +1. The time now is 12:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"