Analysis ToolPack errors after upgrade (Excel 2007)

Topic

New Reply

I have a client who is having an issue with the XIRR function in Excel since the conversion from 2003 to 2007. Occasionally, when she opens various files with the XIRR function, she gets a File Error Data may be lost message. The issue seems to be the IRR function in the cell is removed and replaced with #NA, but the value of the IRR is still in the cell. (You see, for example, 12.3%, but in the cell is #NA and not the formula.)

As I’m not familiar with this function at all, I’m hoping for some help from the community. Microsoft’s site has been less than helpful.

Is there something that can be done to make her life easier or does she have to manually replace all the cells with the formula?

Reply | Quote

Replies

Let her try clearing the check box for Analysis Toolpak in whatever is the Excel 2007 equivalent of Tools | Add-Ins. The ATP functions have been incorporated into Excel 2007 itself, so XIRR is now a native Excel function.

Reply | Quote

Already checked that, it wasn’t checked in 2007. We even tried checking and unchecking. Didn’t work

Thanks anyway

Reply | Quote

Do a search and replace in the worksheet, search for =, replace with = and select lookin formulas.

Reply | Quote

I’m not quite sure what you mean. Can you explain a bit more? Replace an equal sign with an equal sign – or equal sign space?

I do appreciate the help, I just want to make sure I understand it.

Reply | Quote

Select Edit | Replace.
Enter = in the Find what box.
Enter = in the Replace with box.
Click Options >>
Make sure that Formulas is selected in the Look in dropdown.
Click Replace All.

Reply | Quote

As HansV said: you replace the equal signs with equal signs. This forces Excel to reconsider what formulas you have in each cell and should convince Excel your formulas belong to the native function list as opposed to the ATP.

Reply | Quote

Thank you all. I will try this and see if it fixes the issue.

Reply | Quote

Apparently, it didn’t work – she’s still having issues. I’ll take any additional suggestions from anyone on the lounge. Thanks!

Reply | Quote

bump…

experiencing same problem with latest excel 2007 updates.

Formulas dependent upon analysis toolpak (such as eomonth?) now show the result still in the cell, e.g. 31 August 2009, but on selecting the cell the contents show #N/A
On some workstations analysis toolpak was not included as an add-in, yet on other workstations analyisis toolpak was included as an add-in.
The cell contents formula remained elusive.

Worksheet was on manual calc but still showed 31 August 209 after saving and re-opening.

When the worksheet was copied (using Ctrl+drag/drop) the cells lost their ‘value’ and revealed #N/A in the visible screen.

No solution as yet other than re-enter the formula to all sheets.
A bit worrying this because we may need to distribute odd worksheets to other remote users and could be embarrassing.

Anyone had any luck with positive solution?

Thanks
Alan

Reply | Quote

In Excel 2007, you should not need the ATP to use these functions, they are built-in.

Could you attach a small sample file that exhibits the problem please?

Reply | Quote

In Excel 2007, you should not need the ATP to use these functions, they are built-in.

Could you attach a small sample file that exhibits the problem please?

I am unable to replicate the error in a small workbook as delicate info in it.
I googled and found lots of feedback re:same issue with EOMONTH producing the error
We suspected it was compatibility issue but our testing has ruled that out.

The workaround was/is to use:
=DATE(YEAR(A1),MONTH(A1),0)
which returned the end date of the previous month of the date contained in A1

Reply | Quote