Round up/down complication access/excel

[mwheads]

Hi Everyone

Does anybody know why access rounds a differnent way to excel. I have an access query (with 2 decimal rounding and all amounts are certainly 2 decimals) that when outputted to excel will often give a total that is 0.01 difference from the same datasource.

It is more of an irritaion that anything as the access side is posted through our books for one routine and the excel side for another routine and they often do not match exactly and that is untimately what I am looking for.

Does anybody know why, or have a solution.?

Everything on the access side is definately rounded correctly, is this a normal problem?

 

[PHV]

Excel round to the nearest even digit, ie financial rounding if I remember well.
You may try doing your custom rounding, like this:
RoundedValue = CLng(100 * (ValueToRound + 0.05)) / 100

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mwheads]

Thanks PHV but I still have the same problem, it is very strange that 2 programs interpret the same formula's in a different way. I will keep trying (anything)!

 

[GummowN]

It is called banking rounding. Loads of stuff written on it if you google or yahoo it.

Rounding works if you want the total of rounded numbers to be the rounded total of the sum.

Total = sum(rnd(itm))
is the same as
rnd(Total) = sum(itm)

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[Ed2020]

I always use the Excel Worksheet round function in Access.

Add a reference to the Excel Object Library and then use:

Excel.WorksheetFunction.Round()

The Access round function also suffers from Floating Point errors.

Ed Metcalfe

Please do not feed the trolls.....

 

[Ed2020]

Have a look at this for further help....

http://www.tek-tips.com/viewthread.cfm?SQID=870180&SPID=181&newpid=181&page=2

Ed Metcalfe.

Please do not feed the trolls.....

 

[mwheads]

Thanks Ed2020, at least I know the reason now and PHV you put me on track to construct the following:

I did work out an alternative that works, its not pretty, but it works perfectly (for anyone who's interested)

=iif(right(round([endresult],3),1=5),round([endresult]+0.001,2),round([endresult]),2))

Basically, if the 3rd decimal is 5 (which was the problem in the first place) then notch it up by 0.001 OTHERWISE do the normal rounding.

Ciao
PCH (SouthAfrica)

 

[GummowN]

Another alternative I have used before:

Public Function NGRound (dblAmount as double) as double
NGround = (int((dblamount*100)+0.5))/100
End Function

You can mess about with adding a decimal place mover which does a power of 10 thing if you like

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.