Home // Video Tutorials // 6 - 10 (Advanced) // 7 // NA error Excel 2003 to 2007

NA error Excel 2003 to 2007

Understanding the NA error when moving from Excel 2003 to 2007.

NA error Excel 2003 to 2007

There seems to be a bit of a problem in Excel files that are converted from Excel 2003 to Excel 2007 but stay in what is called “Compatibility Mode” so that they can be viewed in either Excel 2003 and 2007. This can be referred to as the NA error in Excel 2003 to 2007.

The problem also only seems to occur with functions that are apparent in the Analysis Tool Pack eg EOMONTH, XNPV, XIRR, NOMINAL, EFFECT, EDATE, CUMPRINC AND CUMIPMT etc. You’ll notice that in this file, this cell , G31, contains a formula that makes use of the NOMINAL function. What it is trying to do is create a benchmark or an index for the CPI based on whatever number is here. Now what seems to happen is when you take a file that was in Excel 2003 and you open it up in Excel 2007 and it is not consistent at all, a message suddenly appears that says “file error , data may have been lost”. But for all intents and purposes when you look at the spreadsheet you can’t see any obvious errors. If you look more closely, however, so we are going to a file that has got it and you look at the same cell, what you’ll discover is that the formula that previously existed suddenly disappears and is replaced by =#N/A. So if you look at the file, you’ll see previously we had a formula like this and now we have this formula. The strange thing about this, though, is that even though it has got a nonsensical formula, there are still numbers showing here – which doesn’t quite make sense. So if I go to an empty sheet – if I’ve got a formula that says =#N/A, it should generate an N/A error message and any formula referring to it, should generate an error and therefore the whole spreadsheet should be corrupted and you should see that there is an error. However with this error even though the underlying formula is nonsensical the number is still shown and the rest of the spreadsheet continues to make use of that number. So now let’s look at what happens when we start changing some of the inputs – I will go back to the pre-error – this number here – 182.54 – I will go in this cell and let’s change it to 6%. When I click “enter”, you’ll notice that it changes the index and therefore the rest of the spreadsheet changes. If, however, with this error in it we change the number to 6%, you’ll see nothing happens. Because there is no formula, no change is made but the rest of the spreadsheet continues to look at this number (182.54) and it works with this number. This can have a serious effect on your models because even though you think you’re makng changes and it is being reflected, it is actually NOT being reflected.

How can you see if you have got this problem? The easiest solution is to use the Find key (Control F) and type in Find what: =#N/A. To make it more thorough click on “options”. Instruct Excel not just to look at the sheet but at the entire workbook. Type “next” and it will show you every situation. You will see here I have something which is representing a date and it has the formula =#N/A – so there is another problem. Basically you will find this in any single cell which has Analysis Tool Pack functions in it. Depending on what your spreadsheet is doing it could have a serious impact on whether the answers you are looking at are correct or not.

Unfortunately there seems to be no easy way to correct this mistake, you can’t undo. Once you have the =#N/A error you have to recreate the formula and try to figure out what was there previously and replace it to get it to work again or find an older version of the file which hasn’t been corrupted.

Some of the formula that may be affected by the =#n/a error include.

Depreciation Formulas

* AMORLINC – (for the French accounting system) Depreciation for each accounting period
* AMORDEGRC – (for the French accounting system) Uses a depreciation coefficient
Formulas for Interest, Cash Flow, Investments, Annuities

* CUMIPMT – Cumulative Interest Payment
* CUMPRINC – Cumulative Principal
* EFFECT – Effective annual interest rate
* FVSCHEDULE – Future Value with a variable rate
* XIRR – Internal Rate of Return (not necessarily periodic)
* XNPV – Net Present Value (not necessarily periodic)
Functions for Coupons

* COUPDAYBS – Days from the Beginning of the Coupon period to the Settlement date
* COUPDAYS – Days in the coupon period that contains the Settlement date
* COUPDAYSNC – Days from the Settlement date to the Next Coupon date
* COUPNCD – Next Coupon Date after the settlement date
* COUPPCD – Previous Coupon Date before the settlement date
* COUPNUM – Number of coupons between the settlement and maturity date
Finance Formulas for Securities

* ACCRINT – Accrued Interest
* ACCRINTM – Accrued Interest at Maturity
* DISC – Discount rate
* DURATION – Annual Duration
* INTRATE – Interest rate for a fully invested security
* MDURATION – Macauley modified duration (with an assumed par value of $100)
* NOMINAL – Annual nominal interest rate
* ODDFPRICE – Price per $100 face value with an Odd First period
* ODDFYIELD – Yield with an Odd First period
* ODDLPRICE – Price per $100 face value with an Odd Last period
* ODDLYIELD – Yield with an Odd Last period
* PRICE – Price per $100 face value
* PRICEDISC – Price per $100 face value of a Discounted security
* PRICEMAT – Price per $100 face value of a security that pays interest at Maturity
* RECEIVED – Amount received at maturity for a fully invested security
* YIELD – Yield on a security that pays periodic interest
* YIELDDISC – Annual yield for a discounted security (Treasury bill)
* YIELDMAT – Annual yield of a security that pays interest at maturity
Formulas for Dollar Price Conversions

* DOLLARDE – Converts a dollar price from a Fraction to a Decimal number
* DOLLARFR – Converts a dollar price from a Decimal number to a Fraction
Treasury Bill Functions

* TBILLEQ – Bond-equivalent yield for a Treasury Bill
* TBILLPRICE – Price per $100 face value for a Treasury Bill
* TBILLYIELD – Yield for a Treasury Bill