Formulas are being deleted and changed to =#N/A

Frustrated AHHH RM · External Since: Sep 16, 2009 Posts: 1

I am using Excel 2007 and there is a major problem which I can't find a
solution to online or through our IT people.

Formulas that use "yearfrac", "round", "edate", etc. are being deleleted and
in it's place #N/A is being populated in the cell. The cell itself will
still give you the result, but the underlying formula is gone.

For example,
Formula: =edate(12/31/08,12)
Result: 12/31/09

is turning into

Formula: =#N/A
Result: 12/31/09.

When you click into the cell, the result also disappears.

Does anyone know of any solutions to this?

It is happening in many files across our office and I have read of other
users having similar problems.

Thanks all

garfield-n-odie [MVP] · External Since: Feb 13, 2006 Posts: 1969

Sounds like the Excel file was created on a computer with the
Excel Analysis Toolpak installed, and you are now opening the
Excel file on a computer that does not have the Excel Analysis
Toolpak installed.

Frustrated AHHH RM wrote:

> I am using Excel 2007 and there is a major problem which I can't find a
> solution to online or through our IT people.
>
> Formulas that use "yearfrac", "round", "edate", etc. are being deleleted and
> in it's place #N/A is being populated in the cell. The cell itself will
> still give you the result, but the underlying formula is gone.
>
> For example,
> Formula: =edate(12/31/08,12)
> Result: 12/31/09
>
> is turning into
>
> Formula: =#N/A
> Result: 12/31/09.
>
> When you click into the cell, the result also disappears.
>
> Does anyone know of any solutions to this?
>
> It is happening in many files across our office and I have read of other
> users having similar problems.
>
> Thanks all

Frustrated AHHH RM · External Since: Sep 17, 2009 Posts: 1

The files typically aren't open on different computers. The formulas will
be working one minute, and then when I re-open the file later on, they are
gone.

The Analysis ToolPak and the Analysis ToolPack - VBA are both installed.

Pluto · External Since: Oct 02, 2009 Posts: 1

I've been having the same problem as well!
I've created the spreadsheet on the same computer that I open with - and
it's always the same people and same computers that are opening the same file.
None of our computers have the Analysis ToolPak installed...
Is there a solution to this? If the Analysis Tookpak is installed, would
that prevent the formulas to get deleted and changed to =#N/A? Please
help....

Thank you...

Simon Murphy · External Since: Jul 18, 2009 Posts: 8

This is a known issue with 2007 and the .xls file format. we fixed it by
moving to .xlsm
see here for more info, and fellow sufferers:

http://smurfonspreadsheets.wordpress.com/2009/02/23/excel-2007-na-error/

Cheers
Simon
Excel development website: www.codematic.net

Pluto wrote:
> I've been having the same problem as well!
> I've created the spreadsheet on the same computer that I open with - and
> it's always the same people and same computers that are opening the same file.
> None of our computers have the Analysis ToolPak installed...
> Is there a solution to this? If the Analysis Tookpak is installed, would
> that prevent the formulas to get deleted and changed to =#N/A? Please
> help....
>
> Thank you...