OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED] Total cell shows formula, not result

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
boboof
Power User
Power User


Joined: 23 Jun 2010
Posts: 58
Location: New Jersey

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

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
View user's profile Send private message
stevesaunders
OOo Advocate
OOo Advocate


Joined: 26 Dec 2009
Posts: 408

PostPosted: Sat Oct 30, 2010 6:48 pm    Post subject: use colon or semi-colon Reply with quote

Some other spreadsheets require you to use a colon in a formula
Open Office is different; it uses the semi-colon
=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
View user's profile Send private message
therabi
Super User
Super User


Joined: 01 Sep 2010
Posts: 562

PostPosted: Sat Oct 30, 2010 6:50 pm    Post subject: Re: Total cell shows formula, not result Reply with quote

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
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Sun Oct 31, 2010 4:28 am    Post subject: Re: use colon or semi-colon Reply with quote

stevesaunders wrote:
Some other spreadsheets require you to use a colon in a formula
Open Office is different; it uses the semi-colon
=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
View user's profile Send private message
stevesaunders
OOo Advocate
OOo Advocate


Joined: 26 Dec 2009
Posts: 408

PostPosted: Sun Oct 31, 2010 7:18 am    Post subject: oops Reply with quote

sorry about that!
_________________
Steve ~ Tennessee USA
Mac OS X 10.6.2 Open Office 3.1
Back to top
View user's profile Send private message
boboof
Power User
Power User


Joined: 23 Jun 2010
Posts: 58
Location: New Jersey

PostPosted: Sun Oct 31, 2010 12:01 pm    Post subject: No Answer yet Reply with quote

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
View user's profile Send private message
Ed
Super User
Super User


Joined: 28 May 2003
Posts: 1041

PostPosted: Sun Oct 31, 2010 12:17 pm    Post subject: Reply with quote

Is Tools>Options>Calc>View>Display>Formulas checked?
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Oct 31, 2010 1:14 pm    Post subject: Reply with quote

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 re-entered.

When only a few cells need to be corrected then for each cell, the simplest steps to follow are...

1. Select
2. Press F2, double-click 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
View user's profile Send private message
boboof
Power User
Power User


Joined: 23 Jun 2010
Posts: 58
Location: New Jersey

PostPosted: Tue Nov 02, 2010 4:09 pm    Post subject: Reply with quote

Thank you, Ken... that seemed to work, although I had to re-format 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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Nov 02, 2010 5:19 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group