Add the remainder to the last value

[gobblechops]

Hi,

I have a prepayment calculator with the months listed and how many days in each month shown as well.

If a month range is entered it will divide a monetary value by the total number of days within this month range.

It will then multiply the result by the number of days in each of the individual months to give a value for each month (rounded to 2 d.p)

i.e - The monetary value is £4,166.67

The month range is January to May

The total number of days in these months is 151

The formula will calculate £4,166.67/151 = £27.5938410.....

This result is multiplied by the number of days in each of these individual months and rounded to 2 d.p

January £27.5938410 * 31 = £855.41 (to 2 d.p)
February £27.5938410 * 28 = £772.63 (to 2 d.p)
March £27.5938410 * 31 = £855.41 (to 2 d.p)
April £27.5938410 * 30 = £827.82 (to 2 d.p)
May £27.5938410 * 31 = £855.41 (to 2 d.p)

The issue is that the total of the sum is £0.01 higher than my original value of £4,166.67 and this can vary between -£0.03 to £0.03.

I would like to add or subtract this remaining amount from the last monthly value so the remainder of the summed individual values is always £0.00

For reference my formula in row 5 column AD is =IF(AND(AD$4>=$O5,AD$4<=$P5),ROUND(($H5)/$Q5*AD$3,2),0)

AD4 is the month in the table, O5 is the first selected month, P5 is the last selected month, H5 is the monetary value, Q5 is the sum of the days in the month range, AD3 is the number of days in the month.

Thanks.

 

[Rick Rothstein]

Simply make the last month's calculation the difference between the total amount and the sum of the amounts for the previous months.

 

[gobblechops]

Thanks for the reply,

My grid contains all of the months of the year and the formula is repeated across all of the months and therefore in the example, June would have a value of £0.00 because it does not fall within the specified month range.

How can the formula be amended for the 'last' cell that has a value greater than 0 in it?

 

[gobblechops]

OK I have it, IF the last month in the grid = the selected last month then (monetary value - sum of previous months containing values), IF false then run the original formula.

Thanks.

=IF(AC4=$P$5,($H$5-SUM($R$5:AB5)),IF(AND(AC$4>=$N5,AC$4<=$O5),ROUND(($H5)/$P5*AC$3,2),0))