Hello,
Calculating payment lines with different scenarios and periods
I am working on contracts data in order to calculate the total amount at a certain point of time for reporting. All contracts have different durations and there might be multiple payment lines for that same contract. Some contracts might have multiple occurences such as 0, 1, 2, 3 (ie. occurence 0 means the original contract, occurence 1 means the contract has been modified due to change in contract services, amount or term. There might be multiple payment lines under each occurance such as payment line # 1.0, 2.0 incrementally by 1. Howerver, the payment lines might be modified too due to changes as above. If the payment line 1.0 is modified, the modified line is 1.01. The fixed monthly amount is provided for all occurence, so I want to calculate the total amount of all the contract for the certain point of time, such as by Feb 29, 2012. For some contract the start date is not the first day of the month, so I need to consider calculating the partial amount as well.
I tried using IF(AND) function to take all these different scenario into account, but ends up with super long formula, which confused me at the end. So, I wonder if I can get help on the VBA code naming a variable that different formula can be applied to different scenario.
I uploaded the sample workbook below. Appreciate your input.
https://docs.google.com/spreadsheet/ccc?key=0ArVVgDn9sCp8dFdlNk85UzJIMkZkTVhrZGZlcFpoalE
Calculating payment lines with different scenarios and periods
I am working on contracts data in order to calculate the total amount at a certain point of time for reporting. All contracts have different durations and there might be multiple payment lines for that same contract. Some contracts might have multiple occurences such as 0, 1, 2, 3 (ie. occurence 0 means the original contract, occurence 1 means the contract has been modified due to change in contract services, amount or term. There might be multiple payment lines under each occurance such as payment line # 1.0, 2.0 incrementally by 1. Howerver, the payment lines might be modified too due to changes as above. If the payment line 1.0 is modified, the modified line is 1.01. The fixed monthly amount is provided for all occurence, so I want to calculate the total amount of all the contract for the certain point of time, such as by Feb 29, 2012. For some contract the start date is not the first day of the month, so I need to consider calculating the partial amount as well.
I tried using IF(AND) function to take all these different scenario into account, but ends up with super long formula, which confused me at the end. So, I wonder if I can get help on the VBA code naming a variable that different formula can be applied to different scenario.
I uploaded the sample workbook below. Appreciate your input.
https://docs.google.com/spreadsheet/ccc?key=0ArVVgDn9sCp8dFdlNk85UzJIMkZkTVhrZGZlcFpoalE