chaserracer83
New Member
- Joined
- Oct 8, 2012
- Messages
- 27
Good afternoon, I am trying to do a tricky little calculation. It requires filtering values from the current table, and multiplying them by values in another table through a matrix type process. The original formula in Excel uses MMULT. I've tried several things, but nothing accomplishes what I am looking to do...
I have two tables, lets call them "Data" and "Timing". Data contains three fields; Key, Month, and Loss. Timing contains two fields; Month, and Timing Curve.
I need a field in the Data table that does the following:
1) Filters for records in the Data table that have the same Key, a Month less than or equal to the current record, and no Month that is less than the current record Month-20
2) For each record not filtered out from the calculation, the Loss number is multiplied by the Timing Curve from the Timing table. The Timing Curve value that is multiplied depends on how many months have passed since the current record. So for example, imagine a record in the Data table, for a particular key, where month is 10:
The function would take the Loss number for this record and multiply it by Timing Curve at Month 1 (because its been one month since this loss happened).
Then it would take the loss number for month 9 and multiply it by Timing Curve at Month 2
Then it would take the loss number for month 8 and multiply it by Timing Curve at Month 3, and so on...
For a record where month is 11, 10 would be multiplied by Timing Curve at Month 2 because 10 is now one month older
3) After the function has calculated the product of each Loss number with the Timing Curve, it sums up all the values
Hopefully there is an easy solution for this because it has really tripped me up. I have created a sample set of data that can be downloaded at:
https://www.dropbox.com/s/r35bxy7vvvlv1mr/Sample Data.xlsx?dl=0
Any help you are able to offer on this is greatly appreciated.
Thank you,
Chase
I have two tables, lets call them "Data" and "Timing". Data contains three fields; Key, Month, and Loss. Timing contains two fields; Month, and Timing Curve.
I need a field in the Data table that does the following:
1) Filters for records in the Data table that have the same Key, a Month less than or equal to the current record, and no Month that is less than the current record Month-20
2) For each record not filtered out from the calculation, the Loss number is multiplied by the Timing Curve from the Timing table. The Timing Curve value that is multiplied depends on how many months have passed since the current record. So for example, imagine a record in the Data table, for a particular key, where month is 10:
The function would take the Loss number for this record and multiply it by Timing Curve at Month 1 (because its been one month since this loss happened).
Then it would take the loss number for month 9 and multiply it by Timing Curve at Month 2
Then it would take the loss number for month 8 and multiply it by Timing Curve at Month 3, and so on...
For a record where month is 11, 10 would be multiplied by Timing Curve at Month 2 because 10 is now one month older
3) After the function has calculated the product of each Loss number with the Timing Curve, it sums up all the values
Hopefully there is an easy solution for this because it has really tripped me up. I have created a sample set of data that can be downloaded at:
https://www.dropbox.com/s/r35bxy7vvvlv1mr/Sample Data.xlsx?dl=0
Any help you are able to offer on this is greatly appreciated.
Thank you,
Chase
. 
