Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatability mo
XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in
compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatability mo
Is the Analysis ToolPak add-in installed in 2007? 2003?
http://office.microsoft.com/en-us/ex...090731033.aspx If it is not installed, install it and retry. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Wibbs" wrote: XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatabilit
Analysis ToolPak add-in is installed.
"ryguy7272" wrote: Is the Analysis ToolPak add-in installed in 2007? 2003? http://office.microsoft.com/en-us/ex...090731033.aspx If it is not installed, install it and retry. HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Wibbs" wrote: XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatability mo
"Wibbs" wrote:
XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. Please clarify the problem statement. First, do you mean that the problem is seen when the workbook is opened in Excel 2007? Or do you mean when it is opened in Excel 2003? Also, is it opened on the same computer, or on a different computer? Is the file on a shared disk and opened over the network? Second, do you mean the #N/A error or the #NAME? error? The #NAME? error suggests that the ATP has not been enabled ("loaded"). Note that the ATP can be installed, but not enabled. See Tools Add-Ins in Excel 2003. The #N/A error indicates that a formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Since these are programmable errors, you need to look at the worksheet and macro designs to understand the conditions when these errors are returned. The Evaluate Formula feature might help. See Tools Formula Auditing in Excel 2003. ----- original message ----- "Wibbs" wrote in message ... XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatabilit
Workbooks were originally written in 2003 and are on a network - users work
via TS. Currently in middle of migration, so some users are opening in 2007 as a 2003 compatible file. Seems to be an intermittent problem, sometimes the XIRR and EDATE functions seem to be OK, othertimes they are replaced by #N/A. It is not the case that formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Currently using Service Pack 1 and operating via Terminal Services. Thanks "JoeU2004" wrote: "Wibbs" wrote: XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. Please clarify the problem statement. First, do you mean that the problem is seen when the workbook is opened in Excel 2007? Or do you mean when it is opened in Excel 2003? Also, is it opened on the same computer, or on a different computer? Is the file on a shared disk and opened over the network? Second, do you mean the #N/A error or the #NAME? error? The #NAME? error suggests that the ATP has not been enabled ("loaded"). Note that the ATP can be installed, but not enabled. See Tools Add-Ins in Excel 2003. The #N/A error indicates that a formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Since these are programmable errors, you need to look at the worksheet and macro designs to understand the conditions when these errors are returned. The Evaluate Formula feature might help. See Tools Formula Auditing in Excel 2003. ----- original message ----- "Wibbs" wrote in message ... XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatabilit
"Wibbs" wrote:
Workbooks were originally written in 2003 and are on a network - users work via TS. Currently in middle of migration, so some users are opening in 2007 as a 2003 compatible file. Seems to be an intermittent problem, sometimes the XIRR and EDATE functions seem to be OK, othertimes they are replaced by #N/A. That is much clearer. One detail remains unclear to me: who is seeing the intermittent #N/A error: the Excel 2007 user or the Excel 2003 user? Have you observed this "intermittent" behavior yourself, or are people simply reporting it to you? Meaning no disrespect, but in my experience, most of the time that customer report "intermittent" behavior, it is really because circumstances changed; for example, they changed the values of some parameters. Since the users are using Terminal Services to access the file and/or Excel, we can rule out the network as a source of intermittent behavior. All the processing is taking place on the central computer. I am not aware of any circumstances where XIRR and EDATE might return the #N/A error, at least not according to Excel 2003 documentation. Does the Excel 2007 Help pages indicate any circumstances when that might happen? I see that this problem has been reported and discussed several times since 2007. I have not found any resolution of the problem. However, in one case, forcing recalculation remedied the error (ctrl-alt-F9). ----- original message ----- "Wibbs" wrote in message ... Workbooks were originally written in 2003 and are on a network - users work via TS. Currently in middle of migration, so some users are opening in 2007 as a 2003 compatible file. Seems to be an intermittent problem, sometimes the XIRR and EDATE functions seem to be OK, othertimes they are replaced by #N/A. It is not the case that formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Currently using Service Pack 1 and operating via Terminal Services. Thanks "JoeU2004" wrote: "Wibbs" wrote: XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. Please clarify the problem statement. First, do you mean that the problem is seen when the workbook is opened in Excel 2007? Or do you mean when it is opened in Excel 2003? Also, is it opened on the same computer, or on a different computer? Is the file on a shared disk and opened over the network? Second, do you mean the #N/A error or the #NAME? error? The #NAME? error suggests that the ATP has not been enabled ("loaded"). Note that the ATP can be installed, but not enabled. See Tools Add-Ins in Excel 2003. The #N/A error indicates that a formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Since these are programmable errors, you need to look at the worksheet and macro designs to understand the conditions when these errors are returned. The Evaluate Formula feature might help. See Tools Formula Auditing in Excel 2003. ----- original message ----- "Wibbs" wrote in message ... XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
XIRR and EDATE functions return as #NA in 2007 in compatabilit
I had a theory. It did not pan out in my experiments. But my situation is
probably very different from yours, so it might be worth pursuing, if you think it might be applicable. Do the workbooks that exhibit the problem have links to other workbooks? If they do, I wonder how they behave under each of the following conditions: (a) the link target has a new file name (i.e. the link no longer points to an Excel workbook); (b) the link target is currently open writable (i.e. not read-only) by another user; and (c) the link target cells no longer have valid values for XIRR or EDATE. None of these presents a challenge in my environment; Excel is well-behaved. But I have WinXP and Excel 2003; you have Excel 2007 and perhaps Vista. Also, you might be using a different type of file system. If that does not pan out, look for factors that might be common among the workbooks that exhibit problems. Often, the perception of "intermittent" behavior boils down to a specific set of conditions that cause a very reproducible behavior, once it is understood. When you finally isolate the problem, I hope you will post an update to this thread. ---- original message ----- "Wibbs" wrote in message ... Workbooks were originally written in 2003 and are on a network - users work via TS. Currently in middle of migration, so some users are opening in 2007 as a 2003 compatible file. Seems to be an intermittent problem, sometimes the XIRR and EDATE functions seem to be OK, othertimes they are replaced by #N/A. It is not the case that formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Currently using Service Pack 1 and operating via Terminal Services. Thanks "JoeU2004" wrote: "Wibbs" wrote: XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. Please clarify the problem statement. First, do you mean that the problem is seen when the workbook is opened in Excel 2007? Or do you mean when it is opened in Excel 2003? Also, is it opened on the same computer, or on a different computer? Is the file on a shared disk and opened over the network? Second, do you mean the #N/A error or the #NAME? error? The #NAME? error suggests that the ATP has not been enabled ("loaded"). Note that the ATP can be installed, but not enabled. See Tools Add-Ins in Excel 2003. The #N/A error indicates that a formula refers to a cell that uses the NA() function or to a UDF that returns the xlErrNA value. Since these are programmable errors, you need to look at the worksheet and macro designs to understand the conditions when these errors are returned. The Evaluate Formula feature might help. See Tools Formula Auditing in Excel 2003. ----- original message ----- "Wibbs" wrote in message ... XIRR and EDATE functions are sometimes changed to #NA in 2007 when saved in compatability mode. This does not happen everytime, but has happened to a number of spreadsheets. These provide crucial information to the company and would like to know how to fix this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Compatability | Excel Discussion (Misc queries) | |||
When save as another file, EDATE and XIRR functions return #N/A | Excel Worksheet Functions | |||
XIRR 0.00% Return with Small First Deposit | Excel Worksheet Functions | |||
Excel 2007 Compatability | Excel Discussion (Misc queries) | |||
edate and yearfrac functions not tallying | Excel Worksheet Functions |