|Can I alter anything to increase anything less than £5.00 to £5.00? (I know with this data it might mean all payments and can’t be done, but in theory could your calculations do it?)|
With a wee bit of modification:
Column M cell 15 add the formula:
(Were going to use your Rounding Options for the moment. More on this below.)
Now modify the formula in the F column to:
Now when you use your Rounding Options it will be reflected in the formulas.
With this in mind, you can now modify your Round Options to round up from Zero through 5 simply by adding three more options:
No Rounding = 0 in cell M14
1 = 1 in cell M14
2 = 2 in cell M14
3 = 3 in cell M14
4 = 4 in cell M14
5 = 5 in cell M14
Your new formula in cell M15 would be:
If you decide to go this route, then you can do away with the M15 formula altogether and simply have the Column F formula reference M14.
Just be sure to format the cell M14 as percent.
The big draw back in the formulas is in the Column G formula.
Using the =MAX function we find the single largest payment percentage (who we are going to pay the most, Creditor #3 ) and decrease the payment by the total of the increase to those cells that we have manually increased.
With the current data, if you increase the manual payments to 5 then the largest creditor, #3, goes into the negative. Not good.
The modification to your Round Options should help in this area, as now you will be able to utilize 6 payment options instead of the three you currently have.