formula lending program

[vicente_maristela]

Little help guys
This is a trial lending worksheet

Only cell you can encode on is PAY2

1. PAY1 cant exceed Due
2. If PAY2 is less than Due, must get from ADV (if any)
*transferred amount from ADV TO PAY1 cannot exceed Due

[vicente_maristela]

anyone? please

[Melvosh]

You're more likely to get assistance if you can post a sample workbook. It makes it far easier for those that can provide help to see exactly what you're working with.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[vicente_maristela]

HELLO,

here is the excel worksheet

[JeteMc]

Hello vicente_maristela and Welcome to Excel Forum.
See if the following does what you want:
1. Paste the following into cell E5 and drag the fill handle down: =MIN(D5,SUM(F5,G4))
2. Paste the following into cell G5 and drag the fill handle down: =SUM(F$5:F5)-SUM(D$5:D5)
3. Paste the following into cell H5 and drag the fill handle down: =I$2-SUM(F$5:F5)
If this doesn't do what you want, it would help us if you would manually fill in values so that we may attempt to replicate them using formulas/code. Also if it is not obvious as to how a value is obtained, include an explanation.
Let us know if you have any questions.

[vicente_maristela]

Hello vicente_maristela and Welcome to Excel Forum.
See if the following does what you want:
1. Paste the following into cell E5 and drag the fill handle down: =MIN(D5,SUM(F5,G4))
2. Paste the following into cell G5 and drag the fill handle down: =SUM(F$5:F5)-SUM(D$5:D5)
3. Paste the following into cell H5 and drag the fill handle down: =I$2-SUM(F$5:F5)
If this doesn't do what you want, it would help us if you would manually fill in values so that we may attempt to replicate them using formulas/code. Also if it is not obvious as to how a value is obtained, include an explanation.
Let us know if you have any questions.

Hello,

Thanks for the initial help

Just a little more help.

As you can see in cell F7, the ADVANCE payment in G6 was added but not G5, which should be

Thanks again

[vicente_maristela]

Actually, it did ADD

found another problem

in cell E5 and below. how to make all negative values be inputed as 0?

[JeteMc]

Try pasting the following into cell E5 and copying down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[vicente_maristela]

Hello,

thanks again, just a small setback

please look into cell E8. value must be 900; so as E9, value must be 860.

this is because G7 has a value of (60), thats why it was subtracted to E8

[JeteMc]

Try: =IF(F5="","",MAX(0,MIN(D5,MAX(F5,SUM(F5,G4)))))
Let us know if you have any questions.