Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Issues with my excel formula as it needs Circular reference - hlp im_not_excelling Excel Worksheet Functions 1 July 4th 06 08:21 AM
circular reference formula Abhi Excel Discussion (Misc queries) 4 March 12th 06 03:22 PM
circular reference hfuk Excel Discussion (Misc queries) 1 October 12th 05 07:22 PM
circular reference when using a UDF Saira Excel Discussion (Misc queries) 1 September 21st 05 06:39 PM
Avoiding circular reference on formula Chuck W Excel Discussion (Misc queries) 4 February 22nd 05 03:52 AM


All times are GMT +1. The time now is 10:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"