Hi Tom!
Thanks for your answer. I have tried to chance to your example, but it donĀ“t
work. A small explanation of the Swedish words (you are right; this is
Swedish :-) ), maybe this will make it easier:
OM = IF
HELTAL = to make it round up/down to a sum without any decimal.
AVKORTA = SHORTENING
Any new idea?
Kind regards,
Marino
-----Ursprungligt meddelande-----
From: Tom Davies
Sent: Sunday, July 14, 2013 11:02 AM
To: Marino / WellnessWebshop.se ; users@global.libreoffice.org
Subject: Re: [libreoffice-users] Visible currency rounding
Hi :)
Is AVKORTA = Truncate, ie just chopping the last digits off?
What does HELTAL do? Is it some sort of "Average" or a division such as x/3
or x/2?
Usually computer calculations work well if you avoid doing any rounding-off
until the final figure. Even then avoid rounding off. Just use formatting
to limit the number of figures displayed. The problem with that is if you
are doing "VAT Returns" or something else where
you just truncate the number rather than rounding properly.
In proper "rounding" the number is rounded to the nearest figure. So ,005
or ,006 or above goes up to ,01. While ,006 is obviously closer to ,01 than
it is to ,00 it is not so obvious with ,005. With ,006 we see that
,01 - ,006 = ,004
but with ,005
,01 - ,005 =,005
I think it's an example of a potential "fence-post error"
,000 ,001 ,002 ,003 ,004 all round down to ,00 but
,005 ,006 ,007 ,008 ,009 should all round up to ,01
So, i suspect that in some of those calculations. For example;
AVKORTA((HELTAL(K50+K51)-(K50+K51);3) = ,0066 which is getting truncated to
,00 instead of the more correct rounding to ,01
In 1/3 calculations it is wrong. When the result happens to be ,0000 or
,0033 the truncated answer is the right answer. However that rounding might
sometimes be corrected by a problem in the next equation if
AVKORTA(HELTAL((K50+K51)+1)-(K50+K51);2) = ,005 then that too is getting
truncated to ,00 instead of being properly rounded to ,01
So this 2nd equation might be mitigating against the problem of the 1st
equation. Two wrongs do sometimes make a right = quite the contrary of the
'wisdom' of the common phrase.
Unfortunately i don't really know what those equations are doing because
they are in Swedish (i think). So i don't really know what AVKORTA, HELTAL
or OM are doing. However, a ,01 inaccuracy appearing intermittently could
be explained by my above assumptions.
If i am right then maybe this equation might be better?
=OM(HELTAL(K50+K51)-(K50+K51)>-0,5;AVKORTA((HELTAL(K50+K51)-(K50+K51);3);(HELTAL((K50+K51)+1)-(K50+K51);2)))
ie, just move the truncate command outside of the calculation?
Regards from
Tom :)
________________________________
From: Marino / WellnessWebshop.se <marino@wellnesswebshop.se>
To: users@global.libreoffice.org
Sent: Sunday, 14 July 2013, 8:11
Subject: [libreoffice-users] Visible currency rounding
Hi!
I want to have visible currency rounding on an invoice template that I have
made i Libre Office. I have this formula in the cell for currency rounding:
=OM(HELTAL(K50+K51)-(K50+K51)>-0,5;AVKORTA(HELTAL(K50+K51)-(K50+K51);3);AVKORTA(HELTAL((K50+K51)+1)-(K50+K51);2))
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
Best regards,
Marino
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be
deleted
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems?
http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be
deleted
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Context
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.