Sum of rounded values to equal original value

[gobblechops]

Hi all,

I have a random monetary value that is divided by a random number between 2 and 12.

For example £6,992.00 divided by 6 = 6 equal amounts (after using the ROUND formula) of £1,165.33.

I need the sum of the rounded instalments to always equal the original value (in this case £6,992.00).

I would like the last instalment to automatically carry the difference of £0.02.

Any help appreciated,

Thanks.

 

[dreid1011]

How about this? Only guessing at the layout of your data, so the formula may need to be adjusted.


=IF((COLUMN()-2)>$B1,"",IF((COLUMN()-2)=$B1,$A$1-SUM(B1:$C1),ROUND($A$1/$B1,0)))

I assumed A1 held the amount, B1 held the divisor and the installments dragged to the right columns C1:N1 with sum of installments in column O.

 

[lrobbo314]

Here's a VBA solution as well.

Sub Payments()
Dim Total       As Double: Total = Application.InputBox("Enter Amount")
Dim Payments    As Integer: Payments = Application.InputBox("Enter Payments")
Dim Initial     As Double: Initial = Round(Total / Payments, 2)
Dim AR          As Variant


ReDim AR(1 To Payments)


For i = 1 To Payments
    If i < Payments Then
        AR(i) = Initial
        Total = Total - Initial
    Else
        AR(i) = Total
    End If
Next i


Range("A1").Resize(Payments, 1).Value = Application.Transpose(AR)
End Sub