View previous topic :: View next topic 
Author 
Message 
boboof Power User
Joined: 23 Jun 2010 Posts: 58 Location: New Jersey

Posted: Sat Oct 30, 2010 6:21 pm Post subject: [SOLVED] Total cell shows formula, not result 


Just added two new columns to an existing spreadsheet, and entered a formula to Sum the cells in the column above the formula cell. In a column of 49 cells, there are 3 data entries; the balance of cells in the column are currently blank.
The bottom cell shows the formula, and not the resulting sum. Yet the prior year columns in the same spreadsheet calculate and show the sum correctly.
Where did I go wrong. Formula is entered correctly =Sum(Cell1:Cell2)
Thanks
Bob
Last edited by boboof on Tue Nov 02, 2010 4:10 pm; edited 1 time in total 

Back to top 


stevesaunders OOo Advocate
Joined: 26 Dec 2009 Posts: 408

Posted: Sat Oct 30, 2010 6:48 pm Post subject: use colon or semicolon 


Some other spreadsheets require you to use a colon in a formula
Open Office is different; it uses the semicolon
=SUM(cell1;cell2)
try it: youl'll like it! _________________ Steve ~ Tennessee USA
Mac OS X 10.6.2 Open Office 3.1 

Back to top 


therabi Super User
Joined: 01 Sep 2010 Posts: 562

Posted: Sat Oct 30, 2010 6:50 pm Post subject: Re: Total cell shows formula, not result 


boboof wrote:  Just added two new columns to an existing spreadsheet, and entered a formula to Sum the cells in the column above the formula cell. In a column of 49 cells, there are 3 data entries; the balance of cells in the column are currently blank.
The bottom cell shows the formula, and not the resulting sum. Yet the prior year columns in the same spreadsheet calculate and show the sum correctly.
Where did I go wrong. Formula is entered correctly =Sum(Cell1:Cell2)
Thanks
Bob 
Check Tools> Options> OpenOffice.org> View on the right hand side under Display, make sure that Formulas is not checked.
HTH _________________ OOO v3.3.0 & LO v3.4beta on Ubuntu 10.10 and Win7
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


keme Moderator
Joined: 30 Aug 2004 Posts: 2910 Location: Egersund, Norway

Posted: Sun Oct 31, 2010 4:28 am Post subject: Re: use colon or semicolon 


stevesaunders wrote:  Some other spreadsheets require you to use a colon in a formula
Open Office is different; it uses the semicolon
=SUM(cell1;cell2)
try it: youl'll like it!  That is misleading, and may cause grave errors!
The colon delimiter is for range limits. SUM(A1:B2) will return A1+A2+B1+B2
The semicolon delimiter is for separating distinct items. SUM(A1;B2) will return A1+B2. 

Back to top 


stevesaunders OOo Advocate
Joined: 26 Dec 2009 Posts: 408

Posted: Sun Oct 31, 2010 7:18 am Post subject: oops 


sorry about that! _________________ Steve ~ Tennessee USA
Mac OS X 10.6.2 Open Office 3.1 

Back to top 


boboof Power User
Joined: 23 Jun 2010 Posts: 58 Location: New Jersey

Posted: Sun Oct 31, 2010 12:01 pm Post subject: No Answer yet 


Sorry guys, and I do appreciate the help, but none of the above suggestions worked.
I am still getting the formula, not the result of the addition.
Just to be clear, my formula is =Sum(H2:H50) placed in cell H51, and I'm using OOo 3.2 on Linux.
I know this is simple and I'm being dense, especially since other columns in this spreadsheet total with no problem at all.
Bob 

Back to top 


Ed Super User
Joined: 28 May 2003 Posts: 1041

Posted: Sun Oct 31, 2010 12:17 pm Post subject: 


Is Tools>Options>Calc>View>Display>Formulas checked? 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 Location: Sydney, Australia

Posted: Sun Oct 31, 2010 1:14 pm Post subject: 


One possibility is that when the column was inserted some or all of the cells in the inserted column were formatted as Text. Inserted columns adopt the formatting of the cells in the column immediately to the left of the selected column(s) before the column(s) is(are) inserted.
Then when you typed your formula in to a Text formatted cell it was interpretted by Calc to be text, not a formula.
Changing the cell's formatting from Text to Number General is only part of the solution to such a problem because cell formatting has no effect on the type of data entered into a cell.
After changing the cell formatting to Number General, the formula, that Calc had initially and correctly interpretted to be text, has to be editted then reentered.
When only a few cells need to be corrected then for each cell, the simplest steps to follow are...
1. Select
2. Press F2, doubleclick or click in the Input line of the Formula bar to enter Edit mode
3. Press any character key to add an extra character to the formula text
4. Press the Backspace key to remove the added character
5. Press Enter.
When a large number of cells need to be corrected the "Find & Replace" dialog can be used with "Search for" and "Replace with" both being ")" (without the speech marks).
Ken Johnson _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


boboof Power User
Joined: 23 Jun 2010 Posts: 58 Location: New Jersey

Posted: Tue Nov 02, 2010 4:09 pm Post subject: 


Thank you, Ken... that seemed to work, although I had to reformat a couple of times to get the two adjacent new columns and their totals to show the calculation, not the formula.
I'll mark this SOLVED at the top.
It's a shame OO0 requires all this help... there's no way it could exist without the wonderful information available on this forum.
Bob 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Tue Nov 02, 2010 5:19 pm Post subject: 


Same with ANY other spreadsheet program.
Since 20+ years people do not understand the difference between text and number, valuie and formatting. _________________ Rest in peace, oooforum.org
Get help on https://forum.openoffice.org 

Back to top 


