I have a small problem I would like some help with if possible. Basically what I have is the below (dummy numbers):
The original cashflow line is what I am working off.
I need to produce two alternative rows below this line which do the following:
Row 9: Subtract all positive values AFTER the financing date (31 March in this case, this has to be able to change at a later point if required) until the amount subtracted has reached the Threshold amount (Cell B1). I need to ONLY subtract positive amounts and ignore negative amounts. Then once the threshold amount has been matched, I want to add back in the total threshold amount on the closing date (Cell L5, however this may change also so that will need to be taken into account). I have shown this row as I would like to see it output as an example.
Row 11: I want to ignore all values until you reach the financing date (31 march). Then ON the financing date, subtract the entire threshold, then proceed to ignore any values until the closing date (31 September) has been reached, then add the full threshold back in ON the closing date. It's the same case above where the financing date and the closing date can change in the future so this would need to update dynamically. I have also shown an example row (row 11) where the inputs I would require are shown.
Let me know if any of the above needs clarification,
(Other forum where this was asked: http://www.excelforum.com/excel-general/…is-reached.html )