XIRR on only visible cells / filtered data

[dantab]

Hi

I am trying to apply the XIRR formula to data that I have selected by a filter. I want the result of the XIRR formula to take into account only the rows of data that are visible after being filtered.

My current formula is: =XIRR(H2:H22,A2:A22), but this takes into account all rows (whether they are viable or not).

Any ideas?

Thanks
Dan

 

[Domenic]

I'd suggest that you copy the filtered data to another location, and use your formula there. Otherwise, try the following formula that needs to be confirmed with CTRL+SHIFT+ENTER...

=XIRR(N(OFFSET(H2:H22,SMALL(IF(SUBTOTAL(3,OFFSET(A2:A22,ROW(A2:A22)-ROW(A2),0,1)),ROW(A2:A22)-ROW(A2)),ROW(INDIRECT("1:"&SUBTOTAL(3,A2:A22)))),0,1)),N(OFFSET(A2:A22,SMALL(IF(SUBTOTAL(3,OFFSET(A2:A22,ROW(A2:A22)-ROW(A2),0,1)),ROW(A2:A22)-ROW(A2)),ROW(INDIRECT("1:"&SUBTOTAL(3,A2:A22)))),0,1)))

 

[dantab]

Thanks Domenic!

I don't understand it but it works!

 

[jumbledore]

I'd suggest that you copy the filtered data to another location, and use your formula there. Otherwise, try the following formula that needs to be confirmed with CTRL+SHIFT+ENTER...

=XIRR(N(OFFSET(H2:H22,SMALL(IF(SUBTOTAL(3,OFFSET(A2:A22,ROW(A2:A22)-ROW(A2),0,1)),ROW(A2:A22)-ROW(A2)),ROW(INDIRECT("1:"&SUBTOTAL(3,A2:A22)))),0,1)),N(OFFSET(A2:A22,SMALL(IF(SUBTOTAL(3,OFFSET(A2:A22,ROW(A2:A22)-ROW(A2),0,1)),ROW(A2:A22)-ROW(A2)),ROW(INDIRECT("1:"&SUBTOTAL(3,A2:A22)))),0,1)))

Hi, can you please explain how this formula works? Thanks

 

[jumbledore]

Can somebody please explain how this formula works?