Formula help in a circular reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I need help to create a formula:

Invoice Amount: ( 10,000.00)
Less Fee: x
Less Insurance: ( 136.32)
Less Gross Pay: ( 1,500.00) [tax 211.79 & other 117.15 = Nett 1,171.06]

Equals Balance remaining: y
less 5% fees: y*0.05
Equals : z

Nett Salary + z should equal 82% of Invoice Amount

I need help in creating a formula for the x Fee, but as it is part of a
formula that creates y and z, a circular reference is created.

Thanks.
 
Invoice Amount: ( 10,000.00)
Less Fee: x
Less Insurance: ( 136.32)
Less Gross Pay: ( 1,500.00) [tax 211.79 & other 117.15 = Nett 1,171.06]

Equals Balance remaining: y
less 5% fees: y*0.05
Equals : z

Nett Salary + z should equal 82% of Invoice Amount

I need help in creating a formula for the x Fee
Click to expand...

Start at the end and work backwards.

z = round( 82%*(invoice amount) - (net salary), 2)

y = round( z/(1-5%), 2)

x = (invoice amount) - insurance - (gross pay) - y

5% fees on y is best computed as y-z to minimize rounding error. But
as a check, you might compute 5%*y on the side.

Also as a check, you might compute (invoice amount)-fee(x)-insurance-
(gross pay) on the side.
 
Genius!!

joeu2004 said:
Invoice Amount: ( 10,000.00)
Less Fee: x
Less Insurance: ( 136.32)
Less Gross Pay: ( 1,500.00) [tax 211.79 & other 117.15 = Nett 1,171.06]

Equals Balance remaining: y
less 5% fees: y*0.05
Equals : z

Nett Salary + z should equal 82% of Invoice Amount

I need help in creating a formula for the x Fee
Click to expand...

Start at the end and work backwards.

z = round( 82%*(invoice amount) - (net salary), 2)

y = round( z/(1-5%), 2)

x = (invoice amount) - insurance - (gross pay) - y

5% fees on y is best computed as y-z to minimize rounding error. But
as a check, you might compute 5%*y on the side.

Also as a check, you might compute (invoice amount)-fee(x)-insurance-
(gross pay) on the side.
Click to expand...
 
Back
Top