Rounding Net Pay Circular Reference Problem

  • Thread starter Thread starter Gerard
  • Start date Start date
G

Gerard

Hi I am constructing a spreadsheet to calculate pays. Problem is I want
to round the pays to the nearest $5 and put the balance from the net
pay as extra tax.
I am using the tax figure to calculate the net pay... which results in
a circular reference error.
Going to Tools, Options and turning Iteration is not an option and does
not suit my purpose. I am trying to find a formula workaround.

Gross $1,155.75
Tax $276.00
Union $6.92
Add. Super $10.00
Allowances $60.00
Net $922.83
I would like to round the net pay to $920 in this instance and add the
remainder (2.83) as extra tax. Problem is the tax amount is used to get
the net amount.
Any solutions would be greatly appreciated.
 
Gerard,

Use two cells for tax - one as tax, one as adjusted tax, and don't include the adjusted tax in your
SUM formula for net pay. Instead, use a formula like

=B3+TaxDifference

(But in your example, you would need to subtract the tax to get the net pay to go down by 2.78)

Also use two cells for net pay - one as pay, one as adjusted pay, one that references the
non-adjusted cell, the other that ref's the adjusted cell..

HTH,
Bernie
MS Excel MVP
 
Back
Top