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
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
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?
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.xlsxRE: 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
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
