Valid formulas which previously worked suddenly transformed into "=#N/A"

ChainlinkMarines

New Member
Joined
Jun 16, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello All,

Hope this finds you well. Within a large, multi-tabbed workbook, some Excel formulas which always worked fine were suddenly replaced by: =N/A#. How did this corruption happen?

Other info:
1) Please note it is not a problem with the way the formulas were written; they always functioned well before
2) There is no pattern to which formulas it corrupted this way; for example in some cases in a given column, the same formulas for the first few rows remain and then at a certain point get replaced by the =#N/A
3) My Microsoft Office 2010 version started exhibiting a Product Activation Error a few weeks ago so I will likely purchase a new legit version today. But does this have anything to do with corrupting the file in the aforementioned way- wouldn't it just stop working? Strangely it DOES open certain older Excels still!
4) I have Kaspersky anti-virus software, in case that's relevant
5) This Excel workbook was literally born in 1995 and has been expanded/upgraded/made more complex ever since

Many thanks!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
238
Office Version
  1. 365
Platform
  1. Windows
Hi ChainlinkMarines,

I have no idea how this can happen; however, it is a good reason to always keep a backup workbook when dealing with excel models of any kind in case of catastrophe.
Do you have a back-up or a way of retrieving a file from an earlier time-point when it was working?

No idea if it has anything to do with product activation. Would assume not.
One issue would be if the formula contained elements no longer supported by your current excel version e.g., if you'd saved it as the latest version or if somehow, back-compatibility has failed after an update.
Honestly it could be anything. Do you have a means of recovering the old file e.g., file-server backup, Google or One Drive previous version, or something saved in Application data Local folder?

Kind regards,
Doug.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,016
Office Version
  1. 365
Platform
  1. Windows
I very much doubt that the problem you're experiencing is anything to do with corruption, the #N/A error is caused by lookup type functions not being able to find the specified match, most likely it would mean that the formulas have never been updated and newly added data is now outside of the range specified in the formula.
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
238
Office Version
  1. 365
Platform
  1. Windows
I very much doubt that the problem you're experiencing is anything to do with corruption, the #N/A error is caused by lookup type functions not being able to find the specified match, most likely it would mean that the formulas have never been updated and newly added data is now outside of the range specified in the formula.

I looked on Google for this. Turns out that others have experienced this i.e., where existing formulas (not the output of the formula) have become "=#N/A".
@ChainlinkMarines: Am I right in saying that's what you're referring to, rather than an #N/A error outputting from a formula?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,016
Office Version
  1. 365
Platform
  1. Windows
Well spotted, Doug :)

Reading the question again, I would agree that what you are saying is correct.
 

ChainlinkMarines

New Member
Joined
Jun 16, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi Gents, yes, your assumptions are correct; unfortunately it wasn't a formula error but rather some random form of corruption. Luckily I had backed up the file 10 June, so I chose to use that version as a basis and manually make the updates required to bring it up to date, rather than risk trying to "fix" the somehow corrupted file. I also looked online and saw no elegant fix to this weird #N/A error.

Thanks and we'll consider this closed now.
 

Forum statistics

Threads
1,144,698
Messages
5,725,821
Members
422,643
Latest member
elwayfan446

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top