Formula for Extracting Installments due with Amount

[vicky6615]

Hello!

Can anyone help me with extracting Installment dues from a set of table.

Test Data with result required is attached herewith

Thank & regard
Vikash

[kaasplankje]

Not sure what's happening in column H as it doesn't match column B.
Can you not simply copy column B to E to another sheet?

[Marc L]

According to the attachment a VBA demonstration as a beginner starter :

Sub Demo1() 
    Dim V, R&, C%, M 
        V = Range("B3", [F2].End(xlDown)) 
    For R = 1 To UBound(V) 
    For C = 1 To 4 
        M = Application.Min(V(R, C), V(R, 5)) 
        V(R, C) = V(R, C) - M 
        V(R, 5) = V(R, 5) - M 
        If V(R, 5) = 0 Then Exit For 
    Next C, R 
        [H3:K3].Resize(UBound(V)) = V 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[vicky6615]

Hello Sir,

with reference to attached excel sheet, Total Fees in Column A is divided into four installment in column B, C, D & E respectively.

Column F is the amount paid by the customer.

I want to identify the remaining installment amount after adjusting the amount paid by the customer in column H,I,J,K

For Example

In Row 6, Customer has paid Rs. 107500/-, that mean He has 0/- amount due in First Installment, Rs.500/- due in second Installment and Installment 3 and 4 as well.
In Row 9, Customer has paid Rs. just Rs. 5000/-, that mean He has 45000/- amount due in First Installment, and Installment 2, 3 and 4 as well.

Hope the said example will help you to understand what i am looking for

Any help in this regard will be highly appreciated

Thank & regard'
Vikash

[Marc L]

Already helped !

[vicky6615]

Hi Marc!

Thank a ton for the solution, it worked perfectly well with VBA,

Is it possible to do with the help of formula?

Thank & regard
Vikash

[Marc L]

As this is obviously a VBA forum section so next time create your thread on the appropriate forum section …

[kvsrinivasamurthy]

In M3 and copied across and down.

=MIN(MAX(0,SUM($B3:B3)-$F3),B3)