# using xirr on dynamic pivot table

#### deduwa

##### Board Regular
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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.

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

Last edited:
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)

Last edited:

Replies
2
Views
458
Replies
0
Views
34
Replies
0
Views
343
Replies
0
Views
176
Replies
1
Views
413

1,203,027
Messages
6,053,120
Members
444,640
Latest member
Dramonzo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back