Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
Hi All,
I have recently moved from Excel 2003 to 2007, and noticed a problem with one of my files. The formula is used to calculcate revenues on projects based on the number of work days applicable in the month, and is as follows: =IF($U6BD$2,0,IF($V6<BD$1,0,IF(AND($U6=BD$1,$V6 BD$2),SUM($AO6*(NETWORKDAYS($U6,BD$2)/NETWORKDAYS($U6,$V6))),IF(AND($U6<BD$1,$V6<=BD$2), SUM($AO6*(NETWORKDAYS(BD$1,$V6)/NETWORKDAYS($U6,$V6))),IF(AND($U6<BD$1,$V6BD$2),S UM($AO6*(NETWORKDAYS(BD$1,BD$2)/NETWORKDAYS($U6,$V6))),$AO6))))) Whe U6 = project start date; V6 = project end date; BD1 = month start date, and BD2 = month end date. The formula worked fine in 2003, and works in 2007 now that I have redone it. The problem was that, when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. Does anybody know why that happened, and how I can avoid it, or correct it in future? Thanks in advance, Alan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith
: when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. I suspect you have a #NA somewhere in a precedent cell. Either trace the logic back through the cells and arrays that are used in your formulas, or use the Formula Auditing feature. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
I have also just migrated to Excel 2007 and am experiencing the same
problem. Any formula using Networkdays() is replaced with =#N/A. These formulae always worked in 2003 but now result in a "File Error: Data may have been lost" message when I open the file in 2007, along with all the Networkdays() formulae being replaced with =#N/A. There are several threads on this topic, but so far no answers. Sean "Stan Brown" wrote: Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith : when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. I suspect you have a #NA somewhere in a precedent cell. Either trace the logic back through the cells and arrays that are used in your formulas, or use the Formula Auditing feature. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
Hi
Press the round office buttonExcel OptionsAddinsGocheck Analysis ToolpakOK -- Regards Roger Govier "Sean" wrote in message ... I have also just migrated to Excel 2007 and am experiencing the same problem. Any formula using Networkdays() is replaced with =#N/A. These formulae always worked in 2003 but now result in a "File Error: Data may have been lost" message when I open the file in 2007, along with all the Networkdays() formulae being replaced with =#N/A. There are several threads on this topic, but so far no answers. Sean "Stan Brown" wrote: Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith : when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. I suspect you have a #NA somewhere in a precedent cell. Either trace the logic back through the cells and arrays that are used in your formulas, or use the Formula Auditing feature. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
This will happen with formulas using Analysis ToolPak functions,
because those are now built-in Excel functions without an add-in. I haven't found a workaround, but then again, I hardly used ATP worksheet functions prior to 2007. On Jan 22, 8:30*pm, Sean wrote: I *have also just migrated to Excel 2007 and am experiencing the same problem. Any formula using Networkdays() is replaced with =#N/A. These formulae always worked in 2003 but now result in a "File Error: Data may have been lost" message when I open the file in 2007, along with all the Networkdays() formulae being replaced with =#N/A. There are several threads on this topic, but so far no answers. Sean "Stan Brown" wrote: Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith : when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. I suspect you have a #NA somewhere in a precedent cell. Either trace the logic back through the cells and arrays that are used in your formulas, or use the Formula Auditing feature. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA * * * * * * * * * * * * * * * * *http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." *-- Marie Dressler, in /Dinner at Eight/ |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
Roger;
I'm not sure what you are suggesting. Excel 2007 has these functions built-in, so the add-in shouldn't be necessary. This does not happen on all files and it does not happen every time on the troublesome ones. So the Networkdays() function is working most of the time, but now and again just disappears! Sean "Roger Govier" wrote: Hi Press the round office buttonExcel OptionsAddinsGocheck Analysis ToolpakOK -- Regards Roger Govier "Sean" wrote in message ... I have also just migrated to Excel 2007 and am experiencing the same problem. Any formula using Networkdays() is replaced with =#N/A. These formulae always worked in 2003 but now result in a "File Error: Data may have been lost" message when I open the file in 2007, along with all the Networkdays() formulae being replaced with =#N/A. There are several threads on this topic, but so far no answers. Sean "Stan Brown" wrote: Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith : when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. I suspect you have a #NA somewhere in a precedent cell. Either trace the logic back through the cells and arrays that are used in your formulas, or use the Formula Auditing feature. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
Iliace;
I have been wondering if that might have something to do with it. When I first installed XL2007, I added in the Analysis ToolPack, assuming that it would be necessary (there was no warning that I should not). When I started getting the problems, I checked the web and discovered that Networkdays() was now a native function. I removed the TPA but the problems persist. I'm thinking now that I should uninstall and reinstall Office 2007. You say you haven't found a workaround - does that mean that you have also experienced the problem? Sean "iliace" wrote: This will happen with formulas using Analysis ToolPak functions, because those are now built-in Excel functions without an add-in. I haven't found a workaround, but then again, I hardly used ATP worksheet functions prior to 2007. On Jan 22, 8:30 pm, Sean wrote: I have also just migrated to Excel 2007 and am experiencing the same problem. Any formula using Networkdays() is replaced with =#N/A. These formulae always worked in 2003 but now result in a "File Error: Data may have been lost" message when I open the file in 2007, along with all the Networkdays() formulae being replaced with =#N/A. There are several threads on this topic, but so far no answers. Sean "Stan Brown" wrote: Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith : when I opened the sheet this morning (in compatability mode initially), all the formulas had been replaced with =#N/A. I suspect you have a #NA somewhere in a precedent cell. Either trace the logic back through the cells and arrays that are used in your formulas, or use the Formula Auditing feature. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
I assume that rebuilding the formula chain will solve the problem; CTRL+ALT+SHIFT+F9
-- Kind regards, Niek Otten Microsoft MVP - Excel "Sean" wrote in message ... | Iliace; | | I have been wondering if that might have something to do with it. When I | first installed XL2007, I added in the Analysis ToolPack, assuming that it | would be necessary (there was no warning that I should not). When I started | getting the problems, I checked the web and discovered that Networkdays() was | now a native function. I removed the TPA but the problems persist. I'm | thinking now that I should uninstall and reinstall Office 2007. | | You say you haven't found a workaround - does that mean that you have also | experienced the problem? | | Sean | | | | "iliace" wrote: | | This will happen with formulas using Analysis ToolPak functions, | because those are now built-in Excel functions without an add-in. I | haven't found a workaround, but then again, I hardly used ATP | worksheet functions prior to 2007. | | | On Jan 22, 8:30 pm, Sean wrote: | I have also just migrated to Excel 2007 and am experiencing the same | problem. Any formula using Networkdays() is replaced with =#N/A. These | formulae always worked in 2003 but now result in a "File Error: Data may have | been lost" message when I open the file in 2007, along with all the | Networkdays() formulae being replaced with =#N/A. There are several threads | on this topic, but so far no answers. | | Sean | | "Stan Brown" wrote: | Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith | : | when I opened the sheet this morning (in | compatability mode initially), all the formulas had been replaced with =#N/A. | | I suspect you have a #NA somewhere in a precedent cell. Either trace | the logic back through the cells and arrays that are used in your | formulas, or use the Formula Auditing feature. | | -- | Stan Brown, Oak Road Systems, Tompkins County, New York, USA | http://OakRoadSystems.com/ | "If there's one thing I know, it's men. I ought to: it's | been my life work." -- Marie Dressler, in /Dinner at Eight/ | | |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula replaced with =#N/A in 2007 - why?
Nieck;
Unfortunately, no. What is happening is that when I open the file, I get the message: "File Error: Some Data may have been Lost" and when I look at the offending cells I see the numbers (values) that should be there, i.e. everything looks OK. But if I look at the formula in each cell, it has been changed to: "=#N/A". After I press Ctrl-Alt-Shft-F9 all the visible values change to "#N/A". So, although Ctrl-Alt-Shft-F9 doesn't fix it, I am glad you mentioned it because it's a quicker way of finding where the problems are. Sean "Niek Otten" wrote: I assume that rebuilding the formula chain will solve the problem; CTRL+ALT+SHIFT+F9 -- Kind regards, Niek Otten Microsoft MVP - Excel "Sean" wrote in message ... | Iliace; | | I have been wondering if that might have something to do with it. When I | first installed XL2007, I added in the Analysis ToolPack, assuming that it | would be necessary (there was no warning that I should not). When I started | getting the problems, I checked the web and discovered that Networkdays() was | now a native function. I removed the TPA but the problems persist. I'm | thinking now that I should uninstall and reinstall Office 2007. | | You say you haven't found a workaround - does that mean that you have also | experienced the problem? | | Sean | | | | "iliace" wrote: | | This will happen with formulas using Analysis ToolPak functions, | because those are now built-in Excel functions without an add-in. I | haven't found a workaround, but then again, I hardly used ATP | worksheet functions prior to 2007. | | | On Jan 22, 8:30 pm, Sean wrote: | I have also just migrated to Excel 2007 and am experiencing the same | problem. Any formula using Networkdays() is replaced with =#N/A. These | formulae always worked in 2003 but now result in a "File Error: Data may have | been lost" message when I open the file in 2007, along with all the | Networkdays() formulae being replaced with =#N/A. There are several threads | on this topic, but so far no answers. | | Sean | | "Stan Brown" wrote: | Fri, 23 Nov 2007 11:40:01 -0800 from Alan Smith | : | when I opened the sheet this morning (in | compatability mode initially), all the formulas had been replaced with =#N/A. | | I suspect you have a #NA somewhere in a precedent cell. Either trace | the logic back through the cells and arrays that are used in your | formulas, or use the Formula Auditing feature. | | -- | Stan Brown, Oak Road Systems, Tompkins County, New York, USA | http://OakRoadSystems.com/ | "If there's one thing I know, it's men. I ought to: it's | been my life work." -- Marie Dressler, in /Dinner at Eight/ | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accidently replaced a spreadsheet that I did not want replaced | Excel Discussion (Misc queries) | |||
Can TRUE/FALSE be replaced by a formula? | Excel Discussion (Misc queries) | |||
Why won't the number 9 enter as that and gets replaced with 0 ins | New Users to Excel | |||
Text replaced with # | Excel Discussion (Misc queries) | |||
text replaced by #### | Excel Discussion (Misc queries) |