13- 17 May, 2013

# NA error Excel 2003 to 2007

Understanding the NA error when moving from Excel 2003 to 2007

• 04:48
• Skill Level:
• What's my skill level

## 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.

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

## Handy Tips & Tricks

Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading

## Creating a Waterfall Chart in Excel the easy way

A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading

"The best Excel course I've ever attended. Thanks!"- F. Swanepoel Sun International

– F. Swanepoel – Sun International

## View Entire Video Gallery ››

• 03:14
• Skill Level:
• What's my skill level

• 00:42
• Skill Level:
• What's my skill level

## Waterfall chart using stock charts

• 07:11
• Skill Level:
• What's my skill level

## Absolute and Relative cell references

• 06:34
• Skill Level:
• What's my skill level