XIRR and #NAME? Error

[Howard Kaikow]

I have programmatically created a workbook.

Several cells have a .Formula inserted of the form

=XIRR(B47:B49,A47:A49)

Which looks valid to my olde eyes.

However, when I manually open the workbook:

1. All cells with an XIRR are flagged as #NAME? (TH eAnalysis Tool Pack is
loaded).
2. If I select a cell with an XIRR, then select the forula in the formula
bar, then use Enter, the forulma
is calculated, so there is nothing wrong with the formula.

What's the workaround?

 

[Alex Simmons]

I have programmatically created a workbook.

Several cells have a .Formula inserted  of the form

=XIRR(B47:B49,A47:A49)

Which looks valid to my olde eyes.

However, when I manually open the workbook:

1. All cells with an XIRR are flagged as #NAME? (TH eAnalysis Tool Pack is
loaded).
2. If I select a cell with an XIRR, then select the forula in the formula
bar, then use Enter, the forulma
is calculated, so there is nothing wrong with the formula.

What's the workaround?

Are you sure you have the correct syntax, i.e.:

.Formula = "=XIRR(B47:B49,A47:A49)"

Alex

 

[Howard Kaikow]

It's a bug, e.g., see KB article 291058.

Apparently, the bug has been around since at least Excel 97.
I guess that it does not pay to wonder just how badly designed is Excel if
this problem cannot be fixed in over 10 years,
no point in wondering!