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.

What formula are you currently using?

"Gerard" wrote:

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.

Dave F wrote:
What formula are you currently using?

=FLOOR(x,5)

Rounds the net pay to the nearest (lowest) $5 multiple. But then how
can I re add this to the tax amount and calculate a new net figure?

Assuming your data above is in cells A1 to B6, you can add these labels
in A8 to A10:

A8: Adjusted Net
A9: Difference
A10: Adjusted Tax

and add these formulae in B8 to B10:

B8: =FLOOR(B6,5)
B9: =B6-B8
B10: =B2+B9

Hope this helps.

Pete

Gerard wrote:
Dave F wrote:
What formula are you currently using?

=FLOOR(x,5)

Rounds the net pay to the nearest (lowest) $5 multiple. But then how
can I re add this to the tax amount and calculate a new net figure?

You are spot on, Thanks for your time Pete


Pete_UK wrote:
Assuming your data above is in cells A1 to B6, you can add these labels
in A8 to A10:

A8: Adjusted Net
A9: Difference
A10: Adjusted Tax

and add these formulae in B8 to B10:

B8: =FLOOR(B6,5)
B9: =B6-B8
B10: =B2+B9

Hope this helps.

Pete

Gerard wrote:
Dave F wrote:
What formula are you currently using?

=FLOOR(x,5)

Rounds the net pay to the nearest (lowest) $5 multiple. But then how
can I re add this to the tax amount and calculate a new net figure?

You're welcome.

Pete

Gerard wrote:
You are spot on, Thanks for your time Pete


Pete_UK wrote:
Assuming your data above is in cells A1 to B6, you can add these labels
in A8 to A10:

A8: Adjusted Net
A9: Difference
A10: Adjusted Tax

and add these formulae in B8 to B10:

B8: =FLOOR(B6,5)
B9: =B6-B8
B10: =B2+B9

Hope this helps.

Pete

Gerard wrote:
Dave F wrote:
What formula are you currently using?

=FLOOR(x,5)

Rounds the net pay to the nearest (lowest) $5 multiple. But then how
can I re add this to the tax amount and calculate a new net figure?