Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

At 09:11 14/07/2013 +0200, Marino Noname wrote:
I want to have visible currency rounding on an invoice template that I have made in Libre Office. I have this formula in the cell for currency rounding:


But something is wrong, sometimes there is a diff of 0,01 in the sum. Perhaps there is a better formula for this?

My invoice looks like this, on the bottom rows:
- Sum for all products on invoice
- Tax
- Currency rounding
- Sum to pay

I'm not sure what you mean by "visible currency rounding" and am having to guess. I fear you have done what many an enquirer does: to give only a vague idea of what you are trying to achieve but add a formula that is the one thing you know is *not* working - so it cannot define your problem! Apart from anything else, what you will see as the result of this formula will depend on the cell formatting: how many fractional places you are choosing to display.

Is the "Sum to pay" simply the "Sum for all products on invoice" plus the "Tax"? (And I imagine that it is only the tax that will need rounding?) How are you calculating the sum to pay: are you simply summing the first two values and rounding the result, or are you using your separate rounding value to correct it? I think you must be doing the former, as using the latter you would get consistent results, even if they were wrong!

Why not do this a different way?

o Calculate your sum to pay directly from the earlier sum and the tax. You are probably doing this already, but you may be trusting the cell formatting to achieve any required rounding. Instead, modify the formula for the sum to carry out the rounding explicitly - so that the value actually stored in the cell (which you could see if you extended the displayed fractional places in the cell formatting) is correctly rounded.

o Now simply calculate the amount of rounding by finding the difference between the true sum of the earlier values and the rounded sum that you have already calculated in the bottom line. (There is no reason why the spreadsheet cannot calculate back up the column, of course.)

I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.