Calc error?

classic Classic list List threaded Threaded
14 messages Options
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Calc error?

I'm running LO on OpenSuse and I wonder if anyone alse can confirm this
error on calc.

Multiplying 2048.28*.146 yields correctly 295.54488, but when rounding
down to 2 decimal places, 295.55 is displayed (instead of
295.54)(either using ROUND(2048.28*.146,2) or with field format.

My LO Version:
Version: 6.0.5.2
Build ID: 00m0(Build:2)
CPU threads: 2; OS: Linux 4.12; UI render: default; VCL: gtk3;
Locale: en-GB (en_GB.UTF-8); Calc: group

Cheers
Harvey
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

Hi Harvey,
>
> Multiplying 2048.28*.146 yields correctly 295.54488, but when rounding
> down to 2 decimal places, 295.55 is displayed (instead of
> 295.54)(either using ROUND(2048.28*.146,2) or with field format.

Please have a look at the values you have written. Gives 299.04888 here
on Calc and pocket-calculator ...

Regards

Robert
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Harvey Nimmo
At 18:26 28/12/2018 +0100, Harvey Nimmo wrote:
>I'm running LO on OpenSuse and I wonder if anyone else can confirm
>this error on calc. Multiplying 2048.28*.146 yields correctly 295.54488, ...

Er, that wouldn't be correct with my maths. Do you perhaps mean
2024.28 * 0.146 instead?

>... but when rounding down to 2 decimal places, 295.55 is displayed
>(instead of 295.54) (either using ROUND(2048.28*.146,2) or with field format.

That would indeed be odd. It's what you would get if you repeatedly
rounded the value to fewer digits one by one
295.54488 -> 295.5449 -> 295.545 -> 295.55
- instead of doing it in one go. So that's not the way to do it, of course.

There is one way that you can create this result, using options at
Tools | Options... | LibreOffice Calc | Calculate. If you have "Limit
decimals for general number format" ticked and set to "3 Decimal
places", your original result will be displayed - correctly - as
295.545. If you then round *that* value to two fractional places, you
will properly get 295.55. The situation can be complicated if you
also have "Precision as shown" ticked, which causes subsequent
calculations to be based on what you see in a cell instead of the
underlying, unrounded value.

What happens if you ensure that both of these options are *not* ticked?

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Robert Großkopf
On Fri, 2018-12-28 at 19:22 +0100, Robert Großkopf wrote:

> Hi Harvey,
> >
> > Multiplying 2048.28*.146 yields correctly 295.54488, but when
> > rounding
> > down to 2 decimal places, 295.55 is displayed (instead of
> > 295.54)(either using ROUND(2048.28*.146,2) or with field format.
>
> Please have a look at the values you have written. Gives 299.04888
> here
> on Calc and pocket-calculator ...
>
> Regards
>
> Robert
> --

Oh, sorry. My haste. Should have been 2024.28*.146

Cheers
Harvey
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Harvey Nimmo
Your numbers don't seem to match up on a bigger scale.
Unfortunately, ths list reformats text, so headings will not directly match with
the columns.


Using the values in message gave in message the result is 299.04888 as
shown, and the 3 digit of 8 causes it to round the 4 up to a 5, which is
correct.
Num 1Num 2Num 1 * Num 2Rounded
2048.280.146299.04888299.05


Here I recalculated the 2nd number to get the result you
show instead of the 0.146 value. Again Rounding is
correct.


Num 1Num 2 CalcYour Value??Rounded
2048.280.144291210186107295.5488295.55


Same as above, but recalculated the 1st number??


Num 1 CalcNum 2Your Value??Rounded
2024.306849315070.146295.5488295.55




On 28 Dec 2018 at 18:26, Harvey Nimmo wrote:


Subject:[libreoffice-users] Calc error?
From:Harvey [hidden email]
To:Users [hidden email]
Date sent:Fri, 28 Dec 2018 18:26:17 +0100


I'm running LO on OpenSuse and I wonder if anyone alse can confirm this
error on calc.


Multiplying 2048.28*.146 yields correctly 295.54488, but when rounding
down to 2 decimal places, 295.55 is displayed (instead of
295.54)(either using ROUND(2048.28*.146,2) or with field format.


My LO Version:
Version: 6.0.5.2
Build ID: 00m0(Build:2)
CPU threads: 2; OS: Linux 4.12; UI render: default; VCL: gtk3;
Locale: en-GB (en_GB.UTF-8); Calc: group


Cheers
Harvey
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

Dear Michael, I have to apologise for providing the wrong example. It
should have been 2024.28*.146(I was distracted while writing, and
forgot to check!!)
CheersHarvey

On Sat, 2018-12-29 at 04:54 +1000, Michael D. Setzer II wrote:

> Your numbers don't seem to match up on a bigger scale.
>
>  Unfortunately, ths list reformats text, so headings will not
> directly match with
> the columns.
>
>
>
>
>
> Using the values in message gave in message the result is 299.04888
> as
> shown, and the 3 digit of 8 causes it to round the 4 up to a 5, which
> is
> correct.
>
> Num 1    Num 2    Num 1 * Num 2 Rounded
>
> 2048.28  0.146    299.04888299.05
>
>
>
>
>
> Here I recalculated the 2nd number to get the result you
> show instead of the 0.146 value. Again Rounding is
> correct.
>
>
>
>
>
> Num 1    Num 2 Calc    Your Value??  Rounded
>
> 2048.28  0.144291210186107  295.5488 295.55
>
>
>
>
>
> Same as above, but recalculated the 1st number??
>
>
>
>
>
> Num 1 Calc    Num 2    Your Value??  Rounded
>
> 2024.30684931507   0.146    295.5488 295.55
>
>
>
>
>
>
>
>
>
> On 28 Dec 2018 at 18:26, Harvey Nimmo wrote:
>
>
>
>
>
> Subject:             [libreoffice-users] Calc error?
>
> From:                Harvey Nimmo <[hidden email]>
>
> To:                     Users LibreOffice <[hidden email]
> rg>
>
> Date sent:         Fri, 28 Dec 2018 18:26:17 +0100
>
>
>
>
>
> > I'm running LO on OpenSuse and I wonder if anyone alse can confirm
> this
>
> > error on calc.
>
> >
>
> > Multiplying 2048.28*.146 yields correctly 295.54488, but when
> rounding
>
> > down to 2 decimal places, 295.55 is displayed (instead of
>
> > 295.54)(either using ROUND(2048.28*.146,2) or with field format.
>
> >
>
> > My LO Version:
>
> > Version: 6.0.5.2
>
> > Build ID: 00m0(Build:2)
>
> > CPU threads: 2; OS: Linux 4.12; UI render: default; VCL: gtk3;
>
> > Locale: en-GB (en_GB.UTF-8); Calc: group
>
> >
>
> > Cheers
>
> > Harvey
>
> > --
>
> > To unsubscribe e-mail to: [hidden email]
>
> > Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to
> -unsubscribe/
>
> > Posting guidelines + more: https://wiki.documentfoundation.org/Neti
> quette
>
> > List archive: https://listarchives.libreoffice.org/global/users/
>
> > Privacy Policy: https://www.documentfoundation.org/privacy
>
>
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Brian Barker
On Fri, 2018-12-28 at 18:41 +0000, Brian Barker wrote:

> At 18:26 28/12/2018 +0100, Harvey Nimmo wrote:
> > I'm running LO on OpenSuse and I wonder if anyone else can confirm
> > this error on calc. Multiplying 2048.28*.146 yields correctly
> > 295.54488, ...
>
> Er, that wouldn't be correct with my maths. Do you perhaps mean
> 2024.28 * 0.146 instead?
>
> > ... but when rounding down to 2 decimal places, 295.55 is
> > displayed
> > (instead of 295.54) (either using ROUND(2048.28*.146,2) or with
> > field format.
>
> That would indeed be odd. It's what you would get if you repeatedly
> rounded the value to fewer digits one by one
> 295.54488 -> 295.5449 -> 295.545 -> 295.55
> - instead of doing it in one go. So that's not the way to do it, of
> course.
>
> There is one way that you can create this result, using options at
> Tools | Options... | LibreOffice Calc | Calculate. If you have
> "Limit
> decimals for general number format" ticked and set to "3 Decimal
> places", your original result will be displayed - correctly - as
> 295.545. If you then round *that* value to two fractional places,
> you
> will properly get 295.55. The situation can be complicated if you
> also have "Precision as shown" ticked, which causes subsequent
> calculations to be based on what you see in a cell instead of the
> underlying, unrounded value.
>
> What happens if you ensure that both of these options are *not*
> ticked?
>
> I trust this helps.
>
> Brian Barker
>
You are right the example should have been 2024.28 * 0.146. Sorry about
that and for the trouble caused!

However, I do not have 'Limit decimals for general number format' set
to anything. (i.e. No tick)
Cheers
Harvey
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Harvey Nimmo
=With the updated first number I get the 295.54 as it should be.</span>
Third position is a 4, it doesn't change the 2nd position.

Num 1
Num 2
Num 1 * Num 2
Rounded
2024.28
0.146
295.54488
295.54


Are the values you show for the Num 1 and Num 2 entered link that or are
that what is displayed on screen.The actual numbers could throw off the
calcs.


If the 0.146 number is display like that, but actually contains
0.146001936490999 it would change the value.




On 28 Dec 2018 at 19:47, Harvey Nimmo wrote:


Subject:;Re: [libreoffice-users] Calc error?From:60;Harvey [hidden email];
To:;Robert [hidden email],
&#
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Harvey Nimmo
At 20:06 28/12/2018 +0100, Harvey Nimmo wrote:
>However, I do not have 'Limit decimals for general number format'
>set to anything. (i.e. No tick)

Good-oh! And what about "Precision as shown"?

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

pietvo pietvo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Harvey Nimmo
Harvey Nimmo <[hidden email]> writes:

> I'm running LO on OpenSuse and I wonder if anyone alse can confirm this
> error on calc.
>
> Multiplying 2048.28*.146 yields correctly 295.54488, but when rounding
> down to 2 decimal places, 295.55 is displayed (instead of
> 295.54)(either using ROUND(2048.28*.146,2) or with field format.
>

2048.28*.146 = 299.04888
not 295.54488
--
Piet van Oostrum <[hidden email]>
WWW: http://piet.vanoostrum.org/
PGP key: [8DAE142BE17999C4]

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error? SOLVED

In reply to this post by Michael D. Setzer II
I think you have put your finger on it, Michael.

The 2024.28 figure is calculated but displayed to only 2 decimal
places. It is, to 4 decimal places, 2024.2829 which results in
295.5452888. So, thanks for the enlightenment!!

Cheers
Harvey


On Sat, 2018-12-29 at 05:11 +1000, Michael D. Setzer II wrote:

> =With the updated first number I get the 295.54 as it should
> be.</span>
> Third position is a 4, it doesn't change the 2nd position.
>
> Num 1
> Num 2
> Num 1 * Num 2
> Rounded
> 2024.28
> 0.146
> 295.54488
> 295.54
>
>
> Are the values you show for the Num 1 and Num 2 entered link that or
> are
> that what is displayed on screen.The actual numbers could throw off
> the
> calcs.
>
>
> If the 0.146 number is display like that, but actually contains
> 0.146001936490999 it would change the value.
>
>
>
>
> On 28 Dec 2018 at 19:47, Harvey Nimmo wrote:
>
>
> Subject:;Re: [libreoffice-users] Calc error?From:60;Harvey
> [hidden email];
> To:;Robert [hidden email],
> &#
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Brian Barker
On Fri, 2018-12-28 at 19:12 +0000, Brian Barker wrote:
> At 20:06 28/12/2018 +0100, Harvey Nimmo wrote:
> > However, I do not have 'Limit decimals for general number format'
> > set to anything. (i.e. No tick)
>
> Good-oh! And what about "Precision as shown"?
>
> Brian Barker

'Precision as shown' is also not set (No tick)

Harvey
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by pietvo
On Fri, 2018-12-28 at 20:12 +0100, Piet van Oostrum wrote:

> Harvey Nimmo <[hidden email]> writes:
>
> > I'm running LO on OpenSuse and I wonder if anyone alse can confirm
> > this
> > error on calc.
> >
> > Multiplying 2048.28*.146 yields correctly 295.54488, but when
> > rounding
> > down to 2 decimal places, 295.55 is displayed (instead of
> > 295.54)(either using ROUND(2048.28*.146,2) or with field format.
> >
>
> 2048.28*.146 = 299.04888
> not 295.54488
> --
> Piet van Oostrum <[hidden email]>
> WWW: http://piet.vanoostrum.org/
> PGP key: [8DAE142BE17999C4]

Sorry Piet, for causing you trouble. My mistake. See my
previous replies.

Cheers
Harvey
--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

Re: Calc error?

In reply to this post by Robert Großkopf
Den fre 28 dec. 2018 kl 19:24 skrev Robert Großkopf <
[hidden email]>:

> Hi Harvey,
> >
> > Multiplying 2048.28*.146 yields correctly 295.54488, but when rounding
> > down to 2 decimal places, 295.55 is displayed (instead of
> > 295.54)(either using ROUND(2048.28*.146,2) or with field format.
>
> Sorry, I can't reproduce this at all (using the correct values given in
another mail):
=2024.28*0.146
Cell format set to 2 decimals ⇨ 295,54
=ROUND(2024.28*0.146;2 ⇨ The same result.

Manjaro Linux, LibreOffice 6.1.3.2


Kind regards

Johnny Rosenberg



> Please have a look at the values you have written. Gives 299.04888 here
> on Calc and pocket-calculator ...
>
> Regards
>
> Robert
> --
> Homepage: http://robert.familiegrosskopf.de
> LibreOffice Community: http://robert.familiegrosskopf.de/map_3
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> Problems?
> https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> List archive: https://listarchives.libreoffice.org/global/users/
> Privacy Policy: https://www.documentfoundation.org/privacy
>

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy