Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help in a circular reference
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help in a circular reference
On Sep 18, 9:18 am, Rayasiom
wrote: 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 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help in a circular reference
Genius!!
"joeu2004" wrote: On Sep 18, 9:18 am, Rayasiom wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issues with my excel formula as it needs Circular reference - hlp | Excel Worksheet Functions | |||
circular reference formula | Excel Discussion (Misc queries) | |||
circular reference | Excel Discussion (Misc queries) | |||
circular reference when using a UDF | Excel Discussion (Misc queries) | |||
Avoiding circular reference on formula | Excel Discussion (Misc queries) |