Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 524
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 229
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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
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
Accidently replaced a spreadsheet that I did not want replaced T Excel Discussion (Misc queries) 2 June 28th 07 04:52 PM
Can TRUE/FALSE be replaced by a formula? BCB Excel Discussion (Misc queries) 4 August 4th 06 06:29 AM
Why won't the number 9 enter as that and gets replaced with 0 ins Lorissa New Users to Excel 2 February 24th 06 05:57 PM
Text replaced with # jay_babcock Excel Discussion (Misc queries) 4 October 14th 05 10:28 PM
text replaced by #### Sheda Excel Discussion (Misc queries) 2 January 16th 05 04:23 PM


All times are GMT +1. The time now is 05:59 AM.

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"