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
--

StompS
Portland, OR
http://www.geocities.com/pdxinvestr/Stomps.html

[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$6:D7),"")
Full Payment:
=IF(H4,H4*G4-SUM($D$6:D8),"")

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk

"StompS" <[email protected]> wrote in message
news:[email protected]...
>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
> --
>
> StompS
> Portland, OR
> http://www.geocities.com/pdxinvestr/Stomps.html
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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$6:D7),"")
> Full Payment:
> =IF(H4,H4*G4-SUM($D$6:D8),"")
>
> --
> HTH
>
> Sandy
> [email protected]
> [email protected] with @tiscali.co.uk
>
> "StompS" <[email protected]> wrote in message
> news:[email protected]...
>>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
>> --
>>
>> StompS
>> Portland, OR
>> http://www.geocities.com/pdxinvestr/Stomps.html
>>
>>
>
>

[Sandy Mann]

I labelled cells C6:C9 "1st Payment", "2nd Payment", "3rd Payment", "Full
Payment" respectively and put the formulas in D6:D9. 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
[email protected]
[email protected] with @tiscali.co.uk


"StompS" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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$6:D7),"")
>> Full Payment:
>> =IF(H4,H4*G4-SUM($D$6:D8),"")
>>
>> --
>> HTH
>>
>> Sandy
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>> "StompS" <[email protected]> wrote in message
>> news:[email protected]...
>>>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
>>> --
>>>
>>> StompS
>>> Portland, OR
>>> http://www.geocities.com/pdxinvestr/Stomps.html
>>>
>>>
>>
>>
>
>

[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" <[email protected]> wrote in message
news:ua%[email protected]...
>I labelled cells C6:C9 "1st Payment", "2nd Payment", "3rd Payment", "Full
> Payment" respectively and put the formulas in D6:D9. 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "StompS" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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$6:D7),"")
>>> Full Payment:
>>> =IF(H4,H4*G4-SUM($D$6:D8),"")
>>>
>>> --
>>> HTH
>>>
>>> Sandy
>>> [email protected]
>>> [email protected] with @tiscali.co.uk
>>>
>>> "StompS" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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
>>>> --
>>>>
>>>> StompS
>>>> Portland, OR
>>>> http://www.geocities.com/pdxinvestr/Stomps.html
>>>>
>>>>
>>>
>>>
>>
>>
>
>
>

[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
[email protected]
[email protected] with @tiscali.co.uk


"StompS" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:ua%[email protected]...
>>I labelled cells C6:C9 "1st Payment", "2nd Payment", "3rd Payment", "Full
>> Payment" respectively and put the formulas in D6:D9. 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
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "StompS" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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$6:D7),"")
>>>> Full Payment:
>>>> =IF(H4,H4*G4-SUM($D$6:D8),"")
>>>>
>>>> --
>>>> HTH
>>>>
>>>> Sandy
>>>> [email protected]
>>>> [email protected] with @tiscali.co.uk
>>>>
>>>> "StompS" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>>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
>>>>> --
>>>>>
>>>>> StompS
>>>>> Portland, OR
>>>>> http://www.geocities.com/pdxinvestr/Stomps.html
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>

[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


"Sandy Mann" <[email protected]> wrote in message
news:%[email protected]...
> 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
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "StompS" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:ua%[email protected]...
>>>I labelled cells C6:C9 "1st Payment", "2nd Payment", "3rd Payment", "Full
>>> Payment" respectively and put the formulas in D6:D9. 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
>>> [email protected]
>>> [email protected] with @tiscali.co.uk
>>>
>>>
>>> "StompS" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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" <[email protected]> wrote in message
>>>> news:[email protected]...
>>>>> 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$6:D7),"")
>>>>> Full Payment:
>>>>> =IF(H4,H4*G4-SUM($D$6:D8),"")
>>>>>
>>>>> --
>>>>> HTH
>>>>>
>>>>> Sandy
>>>>> [email protected]
>>>>> [email protected] with @tiscali.co.uk
>>>>>
>>>>> "StompS" <[email protected]> wrote in message
>>>>> news:[email protected]...
>>>>>>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
>>>>>> --
>>>>>>
>>>>>> StompS
>>>>>> Portland, OR
>>>>>> http://www.geocities.com/pdxinvestr/Stomps.html
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>