Both Issues 1 & 2 can be circumvented using an IFERROR handler (given use of XL2007)
Though note the #VALUE! errors arise not only as a result of of invalid precedents (ie outside spend curve) but also because on occasion you have non-numeric values in either/and/or Col C & P.
Regards Issue 3
That's a call you have to make and it has a fundamental impact on the above approach.
In theory you could look to pro-rate the remaining balance in line with the remainder of the spend curve but I think that decision would really be determined by the nature of what it is you're calculating.
In short if you come up with some logic you're prepared to adopt for apportioning the remainder then we (the board) can look to implement.
In reality it's simply not worth us (the board) conjuring up some fantastical approach to do this given it's more than likely the logic will fall foul of some business case or other - and remember we know nothing of these rules.
Bookmarks