[Solved] Cells show formulas, not results

Help with installation and general system troubleshooting questions concerning the office suite LibreOffice.

[Solved] Cells show formulas, not results

Postby ebedllc@gmail.com » Tue Dec 17, 2019 6:20 pm

SOLVED. The product does not show in box, only the formula.
Last edited by ebedllc@gmail.com on Thu Dec 19, 2019 6:40 pm, edited 2 times in total.
OpenOffice 4.1 on Windows 10
ebedllc@gmail.com
 
Posts: 5
Joined: Tue Dec 17, 2019 6:15 pm

Re: Formulas

Postby Zizi64 » Tue Dec 17, 2019 7:25 pm

My tips:
1: The cell is formatted as Text.
2: The "Show Formula" feature is switched on.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9589
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formulas

Postby ebedllc@gmail.com » Wed Dec 18, 2019 7:14 am

Thank you. I changed "text" to "number". It now shows "DIV/0" . ??? There are numbers in the boxes selected in the formula. I'm using formula " =AVERAGE(X:X) ".
OpenOffice 4.1 on Windows 10
ebedllc@gmail.com
 
Posts: 5
Joined: Tue Dec 17, 2019 6:15 pm

Re: Formulas

Postby Zizi64 » Wed Dec 18, 2019 9:57 am

Please upload your .ods type sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9589
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Formulas

Postby keme » Wed Dec 18, 2019 11:17 am

The X column also contains text (or it is empty).
The text may appear numerical, in which case arithmetic formulas (simple +/- etc.) will handle them as such.
Spreadsheet functions (SUM, COUNT, AVERAGE) will handle such "numeric text" as "not a number".

Numeric text will be marked with a leading apostrophe in the edit field when you select the cell.

To rectify, first remove any "text" cell formatting, then select the entire column and use menu command Data - Text to columns.
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Formulas

Postby ebedllc@gmail.com » Wed Dec 18, 2019 6:58 pm

Thank you. I removed all "text" formatting. Still get "#DIV/0!" as the answer to the "AVERAGE" formula. The answer to the "COUNT" formula is a blank cell.
OpenOffice 4.1 on Windows 10
ebedllc@gmail.com
 
Posts: 5
Joined: Tue Dec 17, 2019 6:15 pm

Re: Cells show formulas, not results

Postby Zizi64 » Wed Dec 18, 2019 7:34 pm

P_l_e_a_s_e upload a sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9589
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Cells show formulas, not results

Postby ebedllc@gmail.com » Wed Dec 18, 2019 9:56 pm

Not solved. I removed all "text" formats" . Cell shows "#DIV/0!" . There are numbers in the required cells . I'm using "AVERAGE". My earlier version 3.3 of Openoffice worked fine.
OpenOffice 4.1 on Windows 10
ebedllc@gmail.com
 
Posts: 5
Joined: Tue Dec 17, 2019 6:15 pm

Re: Cells show formulas, not results

Postby RusselB » Wed Dec 18, 2019 10:41 pm

There's no way we can analyze your spreadsheet until you upload a copy of it, as Zizi64 has already requested.
If you're getting #DIV/0! then Calc is seeing 0 numbers for the division portion of the calculation needed to obtain the average.
Since COUNT is giving you a blank cell, which (probably) means that you have the display of 0 value cells disabled, what do you get if you use the same range, but the COUNTA function?
This will tell you how many non-numeric entries Calc is recognizing.
If you have numbers that have been imported or entered like text, then the removal of the formats will not make any difference, with the (possible) exception of the alignment.
See How to attach a document for assistance in uploading/attaching your document.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 6160
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Cells show formulas, not results

Postby keme » Thu Dec 19, 2019 2:23 am

ebedllc@gmail.com wrote:Not solved. I removed all "text" formats" . Cell shows "#DIV/0!" . There are numbers in the required cells . I'm using "AVERAGE". My earlier version 3.3 of Openoffice worked fine.

Just do the "text to columns" routine I suggested above, even when you are seeing numbers.
Last edited by keme on Thu Dec 19, 2019 10:02 am, edited 1 time in total.
User avatar
keme
Volunteer
 
Posts: 3383
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Cells show formulas, not results

Postby Zizi64 » Thu Dec 19, 2019 8:18 am

I suppose that your numeric-like data are text really - with 0 value.

Check it in the data cells:
An apostrophe before the number: '123 - means a "text" but not a "numeric value".

Switch on the Value Highlighting function temporarly (in the View menu). The strings will be highlighted in black color, the numerical values will be appeared in blue color, and the results of the formulas will be green.


If you can not check it, then upload your sample file here.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9589
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Cells show formulas, not results

Postby ebedllc@gmail.com » Thu Dec 19, 2019 6:38 pm

I deleted all the contents in the entry cells. Basically started over. All cells are now numeric. I re-entered all the data & now the cells with formulas are giving the correct answers. Thanks guys.
OpenOffice 4.1 on Windows 10
ebedllc@gmail.com
 
Posts: 5
Joined: Tue Dec 17, 2019 6:15 pm


Return to LibreOffice

Who is online

Users browsing this forum: No registered users and 1 guest