default rounding

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Gross Payment Tax @ 18% Net Payment
365.75 65.84 299.92

How can I correct the above rounding error?

Tax was calculated by multilplying the Gross Payment *18%
Net Payment was calculated using the formulae Gross Payment - Tax @ 18%

As you can see there is a rounding error of 1 pence.

I am familiar with how to round a particular cell, but how do I set up Excel
so that everything automatically rounds to the nearest 1 pence? I.e. Below 5
rounds down to the nearest penny and 5 or above rounds up to the nearest
penny.

I am absolutely sure there must be a way to do this and it must be something
to do with the set up since I re-installed the software that this error is
occuring.
 
Jill

I would not necessarily advocate this but you can turn down excels precision
to that displayed in Tools>options...>calculation>precision as displayed

Alternatively (Preferred), use ROUND, FLOOR, CEILING, etc to ensure you only
round where you want to

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 
This doesn't appear to be causing Excel to crash (the subject of this
newsgroup)

18% of 365.75 is 65.835, which when subtracted from gross leaves 299.915.
Both numbers properly round up by either the ASTM rounding of the VBA Round
function, or the simplified 5's up of the worksheet Round function.
Ultimately, you need to decide who gets the round-up in the case of a tie,
tax or net. If tax, then tax should be =ROUND(gross*18%,2). If net, then
net should be =ROUND(gross*82%,2)

Are you sure that this basic approach is correct? Taxes that I am familiar
with are not a fixed fraction of the whole, but a fixed fraction added to the
value to make the whole. If that were the case, then you would need to
divide gross by 118%

Jerry
 
Back
Top