Formula To Split The Money

Try:
[AMOUNT INDIVIDUAL SUBMITTED * MIN(1,OVERALL APPROVED%/OVERALL SUBMITTED %)]
OR
MIN(AMOUNT INDIVIDUAL SUBMITTED * OVERALL APPROVED% / OVERALL SUBMITTED %,TOTAL CONTRACTUAL AMOUNT)

Originally Posted by ragulduy

Try:
[AMOUNT INDIVIDUAL SUBMITTED * MIN(1,OVERALL APPROVED%/OVERALL SUBMITTED %)]
OR
MIN(AMOUNT INDIVIDUAL SUBMITTED * OVERALL APPROVED% / OVERALL SUBMITTED %,TOTAL CONTRACTUAL AMOUNT)

Thank you for the help, really.

The first one doesn't work if the approved % is higher than submitted %.

The second one work but let say the approved % is 24% then the total amount of all contractor received will be less than $1,728 which is the total approved amount (it had to be distribute fully).

That's the problem I try to overcome as well.....

Why would you want to pay the contractor more than what they bill?

But anyway, the method would be to sum the totals of each contractor and divide that equally between those who haven't exceeded the total payment if it doesn't total the amount approved.

If you upload a workbook with an example of the layout of your data I can give you an example.

The attachment is coming up as invalid - could you try and upload again?

Originally Posted by ragulduy

The attachment is coming up as invalid - could you try and upload again?

Let me know if still doesn't work.

I couldn't really make sense of that so I made my own example... See the attachment.

Great! The result at Actual column is exactly what I want it to show.

Here comes another question which is on the second bill. On second bill the contractors ask for bill as A=$1,500 ; B =$2000 ; C=$0 (He had done his work on previous job) ; Total=$3,500 ; %=$3,500/$7,200=48.61%. The owner later on approve 50% (how good he is lols). I tried to fit your formula but can't seems to know how.

If you use my Excel file, Pay App = Bill Pay. You first get the bill from contractors so you enter their value under "Submitted" in each Sub Tab Sheet. Then it populate the % into Owner Tab. Once you get approved you enter that % in Owner Tab under "Approved" which suppose to return the payment for each contractors the first bill. Same goes to next bill. Another guide is the info under "Overall" represent the amount of work each contractor had done.

I had attach the example with your sample excel.

Have a look at the attachment

Originally Posted by ragulduy

Have a look at the attachment

Please see my attach file with my modification.

Now I have several questions:

1. Is it possible to combine all the formula from "Approved" and "Limited" into "Actual", so that I will only have "Ask" and "Actual" column? (I wonder if it can be done without just hide the column)

2. How do I make it work while put the calculation row of A B C into separate sheet?

This attempt reach a dead end, see thread below.

http://www.excelforum.com/excel-form...oach-fail.html