Formula calculation

[StompS]

I have a simple amount due to contractors and a schedule of payments when
they reach certain points in the job (ie 25% 50% 75% 100%). The calculations
work out fien if everything goes according to schedule. But, if we amend the
contract and increase the contract price, say, after the 50% payment is
made....when I change the original amount, the two pyments already made will
now change to their respective percentage of the NEW total amount. I don't
want my data entry person to have to manually figure the payments. Is there
a simple fix for this issue?

Example:

Original Data

Formula Amount
Calculated
Contract Amount $5,000
25% Down Contract Amount*.25 $1250
50% Payment Contract Amount*.25 $1250

Change in Contract

Contract Increased to $6000

If I change the Contract Amount, then even though I had paid $1250x2, it
will now reflect that I paid $1500x2

 

[Sandy Mann]

This may be oversimplifying the problem but basically functions are volitile
ie they will recalculate every time that it precedents change. You
thereforte have to have different precedents for each formula:

in G1:G4 I put 25%, 50%, 75% & 100% respectively. Enter the contract value
as it increases in H1:H4.

then in the payments cells:

1st Payment:
=IF(H1,H1*G1,"")
2nd Payment:
=IF(H2,H2*G2-D6,"")
3rd Payment:
=IF(H3,H3*G3-SUM($D$67),"")
Full Payment:
=IF(H4,H4*G4-SUM($D$68),"")

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[StompS]

I'm not sure I understand, is there somewhere I can check to figure out the
concept you've introduced? I'm :THIS" close to figuring out what you are
saying but am not sure where the "D" data came in.....thanks for the help- I
think your formula will do the job if I can figure out where I lost you.....

 

[Sandy Mann]

I labelled cells C6:C9 "1st Payment", "2nd Payment", "3rd Payment", "Full
Payment" respectively and put the formulas in D69. As I said the
percentages are in G1:G4 and the actual values of thre contract are in
H1:H4. When the 1st Payment is due put the value of the contract in H1 -
say $50,000 and D6 will return $12,500. when the 2nd payment - say the cost
of the contract has increased to $60,000 enter that in H2 and D7 will return
$17,500 and so on until the full contract is paid.

Note that if the contract price remains at $60,000 the last two payments
will be reduced to $15,000 each but this is how it should be if half of the
final contract price is to be pain by the second payment.

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[StompS]

Wow, I understand your formula.......I got it to work and it works great!
Here is my challenge, I have a cell for the original contract amount and a
cell for any increase in the amount and (added together) have a cell for the
FULL contract amount (at that time). I can't have the current contract
amount typed in a sperate cell when the next payment is made.. They way I
have it set up is that there is my simple formula with SCHEDULED payments.
The don't add into the formula to amount that has been paid unless the date
field beside each payment <>"" which is the trigger to show payment and
deduct it from the current owed amount. I guess I need some type of formula
that, once the date field is triggered, the number becomes permanent and
will not change if the contract amount changes AFTER that check has been
paid.....am I dreaming?!?!? ; )

Steve

 

[Sandy Mann]

Hi Steve,

As I said before, functions as volatile - with automatic calculation they
will ALWAYS recalculate when their precedent cells change.

The $2 answer is to change the cells that have the calculated values that
you want to remain from fornulas to values by copying the cell and pasting
it back over itself with Paste Special > Values.

The $5 answer is to use VBA code to do your calculation. The calculated
values in the cells will then be values not formulas and so will not change.
If you want to go with the $5 answer send me a copy of your spreadsheet, (or
a sample file with the required cells in the correct positions if the data
is sensitive) and I will send it back with the necessary code.

To send the sheet change my address in the signature by replacing what it
says. (maininator.com is a dummy address)
--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[StompS]

Sandy,

I think I've got it handled here - thanks to you and a whole bunch of other
people. I can't believe how accomodating you all are. Thank you again VERY
VERY much!!!!!

Steve