Is this number format issue when using ROUND a bug?

English
,
#1

Hi there,

I came across this oddity today, and not sure if I should report as a bug - anyone know?

I have a table formatted with a custom number format #“kg”. The final column is a rounded sum like ROUND(SUM(a1:d1)).

The units “kg” do not appear in the main table until numbers are entered, but in the totals column, they appear all the time. I had to create a new number format for the final column #“kg”;;""

Does this sound right - should it have been expected behaviour?

I am on Win10
https://git.libreoffice.org/core/+log/e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1

#2

Your question skips details. Indeed, there’s a difference between an empty cell and a cell with a formula giving zero. The final format string shows that indeed you needed to define a special case for zero. But why did you expect zero to not use the original format string? Did you not tell that maybe you also set a “don’t show zero” setting for the spreadsheet?

A sample file is better than a thousand words.

And a full copy from Help - About is better than a link to the commit, because there are more details there.

#3

Thanks Mike,

Sanitised file attached
test.ods (11.1 KB)

You’re right of course - I could have simplified the question to ask why is the SUM of NULL or empty cells equal to ‘0’, and not itself NULL or empty?

Finally, apologies - I thought the link to the build would be the most accurate - I didn’t notice the copy details button yesterday - You can’t select text from the about box, so I copied the link. Here are the details:

Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: e19e193f88cd6c0525a17fb7a176ed8e6a3e2aa1
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: CL threaded

#4

The Sum function returns a number (here is its description).
In Calc, the value of any formula can be a number, text, or an error value. The formula value cannot be empty.
In order to not show 0 in a cell, you can use a number format, for example:

#"kg";-#"kg";
#5

Thank you - I did have the number format solution worked out.

I would suggest that the description of SUM you linked to isn’t entirely clear on this topic though. The main description is

“Adds a set of numbers.”

and in a note it says

“This function ignores any text or empty cell within a data range”

which turns out to not be strictly true, as my entire range was empty, but it was not ignored - it was SUMmed to zero…

Many thanks for responding though!

I will know for next time.

#6

It is ignored and the sum of nothing is 0.

1 Like
#7

@tad8x8, our conclusions are based on experience, @erAck 's and @mikekaganski’s conclusions are based on knowledge of the source code. Feel the difference. :slightly_smiling_face:

1 Like
#8

Too true! Amazing how we can argue about something we all agree on!

I used to write software manuals for end users, and translating technical reality to user reality is always hard. In situations like this there is no overlap between the two, made harder as the words being used are the same, but the meaning of those words in the different domains is different. I have no knowledge of the source code, I only have my expectations as a user based on the descriptions in the manual, and in this case my user expectation and the technical reality did not meet! :slight_smile:

That’s called ‘experience’!

1 Like
#9

That’s “true” in mathematics, and rightfully it shoud also be true in Calc.
However, the “product of nothing” should then also be treated as the neutral element under multiplication.
Now we are back to a fundamental law of IT: A mistake introduced once by an apprentice (or probably by MS/Lotus/…?) can’t be corrected even if a dozen “senior scientists” try.
For details see:
disask99487accumulatingEmptySetsOfNumbers_SUM_PRODUCT.ods (25.8 KB)

#10

Fwiw, the results in J3 =SUM("anystring") and J9 =PRODUCT("anystring") depend on the detailed calculation settings (Tools → Options → Calc → Formula) and best are #VALUE!, all because Excel even tries to interpret a text value as number if given as a direct single scalar value; insane? yes.
The J19 =SUM({"anystring"}) is 0 because a string in an array is ignored like a string in a cell range (I don’t remember what Excel does there).

#11

What Do You Care What Other People Think? (Last book by Richard P. Feynman)
Although this was written by a genius, you can still find it arrogant.
Anyway, there’s no reason other than consideration of power to care what Lady Microsoft or Mr. Lotus thought - if it’s junk.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.