When I open an historic .wq1 file with LibreOffice CALC, it displays the proper calculations. When I save the CALC file as an .ods and then reopen it, I get only formulas in the key column rather than values. For example, instead of the sum of the relevant column values I get only “#VALUE!” rather than the relevant “=B9+C9-E9-F9” computed to what the formula says to compute. How do I get the reloaded .ods file to present the calculated information that is supposed to be there?DCB2.ods file has been uploaded to paperclip as requested
bgrumbin,
The error code 519 or #VALUE, means No result.
In wich cell are the formula? Not in B9, C9, E9 or F9, true?
Are the values in B9, C9, E9 and F9 numbers? It seems to me that the values are converted to text.
Select the cells from B9 to F9, and in menu Format - Cell - Numbers tab, under Category select Number and in Format select Standard or another number format. If this do not change the formula result, try to retype the four values.
“Addition with the operator + […] returns an error if an operand is not a number.” I keep getting #VALUE!
Wich version of LibreOffice and operating system are you using? You can copy and paste it from menu Help - About LibreOffice.
Thanks LeroyG for providing some insight. The first of the failing formulas is in G9 as “#VALUE!” instead of its proper addition of the referenced cells. Each of a plethora of additionals is in column G. The point “may be” that several of the cells in each such failed computation contain “—” which is not a “number” and even when I try to format it as a number, the formula still doesn’t work. As part of trying to get this to work, I downloaded and installed LibreOffice_6.4.3_Win_x64 and its related helppack_en-US in place of the earlier version 6.3.0_Win_x86 . Am I to gather from your comment that, instead of its natural valuation as the numeric “0”, the “—” entries are being treated as sabotaging the entire computational process? That only if all of those entries were actual “0” would it work. Remaining question: if in fact those were all BLANKS instead of “—” would BLANK be treated as a numeric “0” or would that too sabotage the computation?
Hello,
you got 2 choice to treat text as zero:
1) Use function SUM()
Use =SUM(B9,C9,-E9,-F9) - the function treats text automatically as zero
2) LibreOffice Calc Setting
Tools -> Options -> LibreOffice Calc -> Formula -> Section: Detailed Calculartion Settings -> [o] Custom (conversion of text to number and more, click button Details and set drop down Conversion from text to number: to Treat as zero
Note: This settings are a bit prone to erroneous interpretation of results, since you may lose attenion to the fact that there is some text (which you read as number) and thus not taken into account on calculations.
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
Tried both suggestions. The =SUM notion makes no change in the error report “#VALUE!” for each cell in column G. The Detailed Calculation Settings similarly makes no change in any of the error reports. I have to wonder about the Detailed Calculation Settings whether I’m dealing with the same kind of problem that I experienced when I tried to modify the originally loaded from .wq1 file Times Roman font over to LibreOffice’s Liberation Sans. Even going through the Tools/ Options/ LibreOffice/ Fonts/ Replacement Table settings produced no change in the originally loaded font. On the other hand, when I did an Edit/ Find and Replace for all “—” becomes “0” the formulas suddenly started working exactly as intended. That didn’t work when I said BLANK but became blank when I said “0” the first time I tried it but became “0” the second time, not apparently predictable other than to solving the formula problem., .
I was able to replicate the performance of replacing all “—” with BLANK in an .ods file. First Edit/ Find and Replace was set to “—” becomes which produced no modifications. Then when I run it again as “—” becomes “0” I get the intended i.e BLANK appearance instead of the obnoxious actual “0”. Still doesn’t allow me to maintain line readability with the “—” treated as natural value of 0 but does clean up the problem of failure to calculate.
Sorry - but talking about fonts now is completely mixing up things and I can’t follow your comments, which make no sense for me and I even don’t understand any longer what your problem is. May be some other people could comment on that.
bgrumbin,
Note that the choice 1) that @Opaque proposed is not =SUM(B9+C9-E9-F9) but =SUM(B9,C9,-E9,-F9) with commas (,) or semicolons (
separating each reference.
Yes @LeroyG, I saw that it was a comma separated version of =SUM that @anon73440385 proposed. Not only saw it but tried it as you stated it and with spaces added after each comma and with space prior to the first parenthesis. In NO VARIATION did that =SUM logic correct the problem which is that the formula refuses to calculate when there are any “—” cells referenced. As for my reference to the failed effort to correct the quite different font choice situation, its purpose was exclusively to demonstrate that instructions to the program to make changes in what it has already loaded are regularly ignored hence his suggestion regarding “Treat as Zero” is also DISREGARDED (and in fact disappears if I go back to the underlying spreadsheet which hasn’t changed, then come back to the place where I instructed it to “Treat as Zero” and find that the instruction has been vacated!). The program seems unwilling to RETAIN such modifying instructions.
bgrumbin,
Can you upload the file leaving only the values of file 9? Edit your question and use the paperclip on top of it.
(there are issues with Tools → Options)
bgrumbin,
Use a sequence of two functions SUM instead of one. =SUM(B9,C9)-SUM(E9,F9) with commas (,) or semicolons ( separating each argument.
“-E9” is not a cell name.
This in fact works as a replacement for the original “=B9+C9-E9-F9” that I was trying to get to work in Column G of my spreadsheet prepared by QuattroPro and read into LibreOffice CALC then saved as an .ods file which was failing to display the correct answers but aborting into a computation failure. While I sort of expected the original “=” presentation as copied from the predecessor .wq1 file to function, the only point of my inquiry here is to LEARN HOW to get CALC to do what I want it to do. Marking your latest response @LeroyG as the SOLUTION to the inquiry.
QuattroPro, when a program entered in a diskette. 
I also used Lotus 123.