Circular Reference Problem Calculating Net Pay

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

Gerard

Dave said:
What formula are you currently using?
Click to expand...

=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?
 
P

Pete_UK

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
 
G

Gerard

You are spot on, Thanks for your time Pete


Pete_UK said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Rounding Net Pay Circular Reference Problem 2
Calculating gross when net is known 3
Functions for weekly net pay, single, 1 witholding, no state tax? 2
Formula help in a circular reference 2
Circular Reference 1
Excel 2007 lookup problem continuing ... 3
Circular Reference 1
Macro fails when used to modify existing value 1

Top