Re: [libreoffice-users] Visible currency rounding

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:
>
> =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

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: 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