Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I’m working on a XIRR calculation where I come across with some issues that I need some help. I want to add to below “ Trans Type]-=” to exclude where If [Trans Type]=’EMV’ AND [Date]=’11/30/2015’ Also, to include [Trans Type]=’Memo:’ AND [Date]=’12/31/2015’
In words, I want to exclude Trans Type-=’EMV’ with 11/30/2015 date; Also, only take Trans Type=’Memo:’ with 12/31/2015 date.
Below is my current expression.
=XIRR({<Company-={'ABC’},[Trans Type]-={'Income:' ,'Expense:', 'Fees'}>}[Amount],[Date])
Any input will be greatly appreciated!.
Thanks,
Frankie
Try like:
=XIRR({<Company-={'ABC'},[Trans Type]={'Memo:'},[Date]={'12/31/2015'}>
-
<Company-={'ABC'},[Trans Type]={'EMV'},[Date]={'11/30/2015'}>}
[Amount],[Date])
I understood the OP a little differently:
=XIRR(
{
<Company-={'ABC'}, [Trans Type]={'Memo:'}, [Date]={'12/31/2015'}> +
<Company-={'ABC'}, [Trans Type]-={'Memo:'}> -
<Company-={'ABC'}, [Trans Type]={'EMV'}, [Date]={'11/30/2015'}>
}
[Amount],[Date])
(include trans type = memo only for the defined date,
include other trans types for all dates,
but exclude trans type EMV for the second defined date)
Tresesco and Jon thanks for the reply. Will check it out shortly to see how it goes.
Many Thanks!
Frank
All,
Do you know how I can make it dynamic for the Company? I have multiple companies, but I just want to exclude certain companies that start/have the word "GP". I have this added to the expression, it didn't work. {"$(-={'GP,*'})"} .
=XIRR({<Company-={"$(={'GP,*'})"} ,Trans Type]-={'Income:' ,'Expense:', 'Fees'}>}[Amount],[Date])
Hi,
Try like this..
=XIRR({<Company-={'GP*'},[Trans Type]-={'Income:' ,'Expense:', 'Fees'}>}[Amount],[Date])