Having Trouble With Formula

[AllenRitch]

Two areas contribute to a particular program each having different contribution levels and limits. As payments are made, each contributor should be charged their portion of the payment. The user need only enter the total payment amount and a formula porates the amount accross the two areas. Some how, I need a formula which will prevent the aforementioned formula from causing a particular area from going over their budget. The Ending Balance is the difference between their total budget and the payments that have been made. But I recieve a circular reference error when I try to use the Ending Balance result in the proration of their payments. Any suggestions?

Colmn A Colmn B Colmn C Colmn D
Program Contributors Amounts Percentage
Area 1 $15,000.00 37.50%
Area 2 $25,000.00 62.50%
Total Program Cost $40,000.00 100.00%

Payments Area 1 Area 2 Payment Amt
Payment 1 $5,625.00 $9,375.00 $15,000.00
Payment 2 =D8*C2 =D8*C3
Payment 3
Ending Balance $9,375.00 $15,625.00 $15,000.00

 

[SkipVought]

Hi,

I'd just use conditional formatting to highlight the value/cell whenever it exceeded the budget.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[KenWright]

How could they go over?

If you have already predetermined the total amounts that each should pay, ie the 15,000 and 25,000, and the percentages of 37.50% and 62.50% represent these, and the payments are split according to these percentages.

If you don't put in a total set of payments that exceed 40,000 then I don't see how you could send any of them over budget as they would never be allocated more than the 37.5% and 62.5% respectively?

If I missed something there then i guess you would just use the MIN function with the maximum amount of contribution as one of the arguments, but at the moment I'm just not seeing it.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------