using xirr on dynamic pivot table

[deduwa]

Hello - is anyone able to help with the below without the need for having to create a macro.

I have a pivot table with 2 Report filters and 2 columns (Date in column A and cashflow in column B). The 'Row Labels' heading of the pivot is in cell A4 and the date start from cell A5 downwards, with corresponding cashflows starting from cell B5 downwards.

When all the report filters are unticked, I can use the XIRR function against the data in the pivot no problem. If however I tick some items in the Report filter, then the resulting pivot data would cause the XIRR formula to show #VALUE !

My pivot also has a grand total at the bottom.

Is there a way that the XIRR formula can be adapted so that I do not get an error when the number of rows of the pivot changes?

Thanks

 

[joeu2004]

Sight unseen, it is difficult to say for sure. I don't understand why the XIRR function would result in a #VALUE error. I would think it simply returns an incorrect result. (Arguably, that's worse insofar as it is misleading.)

I would guess that the following might work. I make an effort to avoid OFFSET, INDIRECT and other "volatile" functions that might slow Excel recalculations and changes.

=XIRR(B5:INDEX(B5:B100000,MATCH(1E+300,A5:A100000,1)), A5:INDEX(A5:A100000,MATCH(1E+300,A5:A100000,1)))

That presumes that there is no other numeric data in column A below A5; just the dates for the XIRR formula.

Change B100000 and A100000 if you know you will have less data or you might have more data.

If that does not do the trick, I suggest that you upload an Excel file (redacted) that demonstrates the problem to a file-sharing website, and post the public/share URL in a response here. Test the download URL, being careful to log out of the file-sharing website first to be sure there are no permission problems.

 

[deduwa]

Thank you that worked. What does the 1E+300 mean?

 

[joeu2004]

MATCH(1E+300,A5:A100000,1) finds the last numeric value in the range. 1E+300 means 10 to the power of 300; 1 followed by 300 zeros. It is meant to represent "the largest number", so that MATCH keeps searching for the best match until it encounters the last value in the range. 1E+300 is not actually the largest value that Excel can represent; but neither is 9.99999999999999E+307, which some people use. The latter is simply the largest constant that we can enter manually; the largest result of a calculation is about 1.79769313486232E+308. But 1E+300 is easy to remember, and it is probably "large enough". In fact, so is 1E+100, if that is easier for you to remember.

PS.... If you actually had 1E+300 or larger in a cell before the last value in the range, MATCH might mistakenly find that instead. So obviously we are making the assumption that all of the values in the range are less than 1E+300. Usually a valid assumption. (wink)