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] Copy/Pasted Formula doesn't compute

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


Joined: 12 Aug 2010
Posts: 4

PostPosted: Thu Aug 12, 2010 5:38 pm    Post subject: [SOLVED] Copy/Pasted Formula doesn't compute Reply with quote

I recently switched over to OpenOffice from Excel. I have a simple check register in .xls that I've been using for some time (from Vertex42 I believe). I opened the file in OpenOffice, and copied the last line and pasted it to extend the page down (about 25 rows pasted). In Excel after I did this, when I would make an entry into either the Deposit or Withdrawl columns, the Balance column would auto-compute. I can see that the formula has been pasted in the Balance column, as it should be. But when I make entries into the Deposit or Withdrawl columns, the Balance cell stays blank.

Obviously, I'm uber-noob to OpenOffice. This is my first attempt at anything after making the transition.

Here is the formula:

Code:
=IF(AND(ISBLANK(F722),ISBLANK(G722)),"",OFFSET(H722,-1,0,1,1)+G722-F722)


Last edited by Truckman on Sun Aug 29, 2010 5:50 am; edited 1 time in total
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: Thu Aug 12, 2010 6:42 pm    Post subject: Reply with quote

Code:
=IF(AND(ISBLANK(F722);ISBLANK(G722));"";OFFSET(H722;-1;0;1;1)+G722-F722)
ie the commas should be semicolons.

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
Truckman
Newbie
Newbie


Joined: 12 Aug 2010
Posts: 4

PostPosted: Tue Aug 17, 2010 4:09 pm    Post subject: Reply with quote

Sorry it took so long to get back. That did not work. The semicolons automatically changed themselves back to commas.

When I opened the original Excel sheet, I was able to make a few entries and it computed them properly. It is only when I tried to copy the formula and paste it down the page at once that it stopped working.


Now a very strange thing just happened as I was typing this post. I hit the "Undo" arrow all the way back to before I copy/pasted the above new formula with semicolons and now the formula computes for that one line only. If I make an entry in the next line, it does not compute. As silly as this sounds, is there a switch or something I need to do to "enable" the formula?

ETA: Can someone explain what exactly that formula does? It's from a check/bank register spreadsheet. That formula is in the "balance" column (H). The Withdrawl is column F and the Deposits are column G. I'm trying to make heads or tails of it using the Formula Wiki. It seems as if it's checking for any entries in the F and G cells of that particular row, then if there are not any it is entering a blank (the " "). I can't quite understand the Offset function's purpose in the formula though.

For a check register, does it need to be that complicated?
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: Tue Aug 17, 2010 5:48 pm    Post subject: Reply with quote

Truckman wrote:
The semicolons automatically changed themselves back to commas.
Sounds like you are using a version that uses comma delimiter instead of semicolon.

Truckman wrote:
It is only when I tried to copy the formula and paste it down the page at once that it stopped working.


Now a very strange thing just happened as I was typing this post. I hit the "Undo" arrow all the way back to before I copy/pasted the above new formula with semicolons and now the formula computes for that one line only. If I make an entry in the next line, it does not compute. As silly as this sounds, is there a switch or something I need to do to "enable" the formula?
Check Tools|Cell Contents|AutoCalculate should have a tick to its left.

Truckman wrote:
I can't quite understand the Offset function's purpose in the formula though.
Code:
OFFSET(H722;-1;0;1;1)
Returns the value that is in the cell that is one row above H722 (the -1 means go up the sheet 1 row) and in the same column as H722 (0 means don't go anywhere across the sheet). The two 1s after the 0 could have been left off. The OFFSET function can return more then one cell's value but then it has to be entered as an array function using Ctrl+Shift+Enter.
Code:
OFFSET(H722;-1;0;1;1)+G722-F722
therefore adds the value in H721 to the difference between G722 and F722.

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
Truckman
Newbie
Newbie


Joined: 12 Aug 2010
Posts: 4

PostPosted: Wed Aug 18, 2010 4:03 pm    Post subject: Reply with quote

ken johnson wrote:
Truckman wrote:
The semicolons automatically changed themselves back to commas.
Sounds like you are using a version that uses comma delimiter instead of semicolon.

I'm using Version 3.2.0 in Ubuntu 10.04.

ken johnson wrote:
Check Tools|Cell Contents|AutoCalculate should have a tick to its left.

AutoCalculate is enabled

ken johnson wrote:
Truckman wrote:
I can't quite understand the Offset function's purpose in the formula though.
Code:
OFFSET(H722;-1;0;1;1)
Returns the value that is in the cell that is one row above H722 (the -1 means go up the sheet 1 row) and in the same column as H722 (0 means don't go anywhere across the sheet). The two 1s after the 0 could have been left off. The OFFSET function can return more then one cell's value but then it has to be entered as an array function using Ctrl+Shift+Enter.
Code:
OFFSET(H722;-1;0;1;1)+G722-F722
therefore adds the value in H721 to the difference between G722 and F722.

Ken Johnson
Thanks. That helps me understand what the formula does.

I'm at a loss. I can't figure out any reason why the formula on one line and above will work, but the line below it on down will not.
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: Wed Aug 18, 2010 6:01 pm    Post subject: Reply with quote

I can see similarities with your problem here...
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=33200&p=152317&hilit=Ubuntu#p152317
Note the bit about the Ubuntu version and commas.
Perhaps you should consider installing the Sun version as suggested by Hagar de l'Est.

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
Truckman
Newbie
Newbie


Joined: 12 Aug 2010
Posts: 4

PostPosted: Sun Aug 29, 2010 5:50 am    Post subject: Reply with quote

Just finally got around to messing with it. That seems to have fixed it. Thanks again for hte help!
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