98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Rounding
Rounding
Resolved · Low Priority · Version 2010
Wed 4 Dec 2013, 17:25Delegate Orla said...
Orla has attended:
Excel Intermediate course
Excel Advanced course
Rounding
When I convert one whole number from £GBP to $USD I get one amount and when I break out the detail on the GBP and convert each amount individually I am off by .01cent...
I've tried rounding but it doesn't seem to work...anything else I can do? Or do I have to live with these rounding differences?
Rate 1.6417
Total £8,985.95 $14,752.24
Gross £17,460.03 $28,664.13
Deduction £7,857.00 $12,898.84
Deduction £617.08 $1,013.06
Total £8,985.95 $14,752.23
Difference $0.00 $0.01
Fri 6 Dec 2013, 12:42Trainer Gary said...
RE: Rounding
Hi Orla
Thanks for getting in touch. I have recreated your data and not had the same issue.
You may have "Precision as displayed" enabled. You can check this by going to File > Options > Advanced > Set precision as displayed.
This will only calculate with visible numbers, whereas Excel normally calculates with as many decimal places as it can.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Fri 6 Dec 2013, 13:32Delegate Orla said...
RE: Rounding
I have set precision as displayed but nothing has happened...
Eg
=round(14752.24/1.6417,2)
Answer 8985.95
But on the reverse:
=round(8985.95*1.6417,2)
Answer 14752.23
14752.24 vs 14752.23 = .01 off
The only way it seems to work is if I round to 4...but I can't do this for my purposes.
Any other suggestion?
Fri 6 Dec 2013, 15:08
Edited on Fri 6 Dec 2013, 15:08Trainer Gary said...
RE: Rounding
Hi Orla
Thanks for your reply. Incidentally, I would make sure Precision As Displayed is turned off unless you require it for a particular workbook.
I'm not sure what else to check. I've attached a workbook with my calculations in. Do they still display correctly at your end?
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
GF Currencies.xlsxFri 6 Dec 2013, 15:53Delegate Orla said...
RE: Rounding
Sorry I am not explaining myself clearly. You are not doing the calculation the way I am.
I don't seem to be able to attach anything otherwise I would attach my excel doc...
Let me try again:
Rate 1.6417
Total $14,752.24/1.6417= £8,985.95
Gross Up £17,460.03*1.6417= $28,664.13
Deduction £7,857.00*1.6417= -$12,898.84
Deduction £617.08*1.6417= -$1,013.06
Total $14,752.23
Difference $14,752.24 - $14,752.23 = $0.01
Basically I get the $14k and convert it into GBP to gross it up for payroll...once I've done that I figure out the deductions, convert them to USD together and sum them...
I'm doing this with a lot of data and a lot of people. The odd person is always a cent off...I thought rounding would do the trick but it doesn't seem to...
Maybe what I am asking for isn't possible...
Thanks Orla
Mon 9 Dec 2013, 10:12Trainer Gary said...
RE: Rounding
Hi Orla
Thanks for clarifying. What you're trying to achieve will create these kind of rounding errors when going back and forth. This is just the maths of what you're trying to do - you're starting with the final figure and converting it back, then trying to convert the component figures the other way. It's highly likely you'll encounter these sort of issues with this two-way conversion.
Sorry I couldn't be of further help.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Creating custom listsIn Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc. |
