[Solved] LibreOffice shows #VALUE!; OpenOffice shows number

[timlab55]

Okay, I've been advise to start a new topic, so here it goes. I created a spreadsheet in open office. Then when I switched over to Libreoffice I noticed that it does the math correctly up to a line and then puts #Value where there should be a number. See attachment.

test.ods

(75.86 KiB) Downloaded 8 times

As you can see lines 1 through 6 are fine. Lines 7 thorugh 13 are not. When I run this in Open Office, it works just fine.
Thanks

Edit: Changed subject, was Why doesn't it work?  Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

Thank you for the attachment. It shows the problem immediately.

I noticed that it does the math correctly up to a line and then puts #Value where there should be a number.

The #VALUE! is in G7. Remove the space in cell F7.

G7 tries to perform arithmetic with G6, E7, and F7 so those cells should have numbers. But F7 has a space instead of a number. For OpenOffice, the numeric value of a cell containing a space is zero, and it's worked this way for more than 20 years. It seems that LibreOffice has changed the rules, and you must be sure that all of the cells you're using for arithmetic contain numbers. This is a reasonable requirement, though unexpected for OpenOffice users.

When I run this in Open Office, it works just fine.

In either OpenOffice or LibreOffice I would replace G3's formula
=IF(AND(ISBLANK(E3);ISBLANK(F3));" - ";OFFSET(G3;-1;0;1;1)+F3-E3)       with
=IF(COUNT(E3;F3);G2+F3-E3;" - ")
then fill that formula down the G column. For LibreOffice you still need to get rid of the space in F7. Using the OFFSET function suggests that you don't understand relative references and that you need to study these tutorials.

[Tutorial] Ten concepts that every Calc user should know
[Tutorial] How do I specify the formula for a column?

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[cwolan]

G7 tries to perform arithmetic with G6, E7, and F7 so those cells should have numbers. But F7 has a space instead of a number. For OpenOffice, the arithmetic value of a cell containing a space is zero, and it's worked this way for more than 20 years. It seems that LibreOffice has changed the rules, and you must be sure that all of the cells you're using for arithmetic contain numbers. This is a reasonable requirement, though unexpected for OpenOffice users.

Indeed.
Note that LibreOffice won't generate an error if for custom calculation settings (through Tools ⟶ Options ⟶ LibreOffice Calc ⟶ Formula, the Details button) one chooses:

• "Convert only if unambiguous" or "Convert also locale dependent", both with "Treat empty string as zero" checked
• "Treat as zero"

Cf. LibreOffice Help — Detailed Calculation Settings.
⠀

CustomCalculationSettings.png (32.35 KiB) Viewed 413 times

[John_Ha]

This is an excellent demonstration of why a spreadsheet is not a sensible way to build a finances record system as it is all too easy to corrupt the data and cause the spreadsheet to fail. Your input data is not protected from accidental corruption.

There are numerous free and fee finance systems based on dstabases - use one.

[LastUnicorn]

This is an excellent demonstration of why a spreadsheet is not a sensible way to build a finances record system as it is all too easy to corrupt the data and cause the spreadsheet to fail. Your input data is not protected from accidental corruption.

There are numerous free and fee finance systems based on dstabases - use one.

Thank you for these thoughts John. I wasn't aware of this: I thought the whole point of a spreadsheet for most users was to manage financial data. Have spent the morning quickly testing some free and portable financial databases and decided that GnuCash Portable will cover my needs for my Windows system. I note that it is also available for Mac and Linux systems — and as a 'flatpack' for Linux. More details on GnuCash can be seen here: GnuCash Home Page.

[keme]

I thought the whole point of a spreadsheet for most users was to manage financial data.

And you may be correct in thinking so. Many users choose the spreadsheet because "it is there" since they already have a computer with a preinstalled Office suite. However, being the more common choice does not necessarily make it the right choice.

The point is, you need to know the strengths and weaknesses of the tool you are using. One of the strengths of a spreadsheet is flexibility. An important weakness is when it comes to data integrity (e.g. avoiding trouble with rounding errors, ensure that numbers are really numbers). The two concepts - flexibility in use vs. enforcement of structural integrity - are somewhat connected, and frequently conflicting.

For a quick mockup of possible scenarios involving money, a spreadsheet can be exactly the right tool. For large volume structured data (like accounting/budgeting; the "finances record system" mentioned by John_Ha) it is very often the wrong tool.

[timlab55]

There is another point as well. I feel the person should have control over their stuff and not some cloud base. For example (from a programmer himself (in basic)), those FREE programs you can download. They are great sometimes, but who know if the programmer put either back door into the program or has all your info go to their personal place. I still believe in either making my own programs.

[keme]

There is another point as well. I feel the person should have control over their stuff and not some cloud base. For example (from a programmer himself (in basic)), those FREE programs you can download. They are great sometimes, but who know if the programmer put either back door into the program or has all your info go to their personal place. I still believe in either making my own programs.

Certainly, there is a danger in that. When something appears free, it is not always so. There may be different reasons for offering your product at no cost, and not all reasons will be benevolent/openly stated. I recognize your point.

Herein lies much of the beauty of open source. Everyone is free to install from source (self build, as it were). A back door built into the app will surely be called out in a timely manner. Even "easy install" binaries, when they "extend the functionality" compared to what is in the source, will be called out and warned against.

Anyway, as far as I consider, the point you are making does not create any distinction in the choice between general and special purpose tools. Both special purpose tools (e.g. GNUcash) and general purpose (e.g. OpenOffice/LibreOffice) have at times been distributed in "rogue" versions by third parties.