Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formulas | Excel Worksheet Functions | |||
Help With Array Formulas... | Excel Discussion (Misc queries) | |||
array formulas | Excel Worksheet Functions | |||
Array formulas | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions |