Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Dec 2001
    Location
    Milford, Connecticut
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Analysis ToolPack errors after upgrade (Excel 2007)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

    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.

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Milford, Connecticut
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

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

    Thanks anyway

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

    Do a search and replace in the worksheet, search for =, replace with = and select lookin formulas.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Milford, Connecticut
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

    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.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Milford, Connecticut
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

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

  9. #9
    Star Lounger
    Join Date
    Dec 2001
    Location
    Milford, Connecticut
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Analysis ToolPack errors after upgrade (Excel 2007)

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

  10. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='pieterse' post='792419' date='07-Sep-2009 13:49']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?[/quote]

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •