Rounding Net Pay Circular Reference Problem

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

 

[Bernie Deitrick]

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

 

[Gerard]

Thanks Bernie, Much appreciated!