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

Money register balance cell formula

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


Joined: 18 Jul 2008
Posts: 11

PostPosted: Fri Jul 18, 2008 8:29 pm    Post subject: Money register balance cell formula Reply with quote

As many people here probably are, I'm new to Calc and need some help on a specific formula used to calculate a financial balance value. Basically imagine a bookkeeping transaction register. You'll have columns such as the Date, Check #, Description, Debit, Credit, and Balance in that order from left to right starting at the beginning. I need a formula/macro that 1) determines whether or not a money amount was entered in either the debit or credit cell for a specific transaction (only one can have a positive value - if there's a number in the debit cell, then the balance cell for that same transaction on the same row should show the total after subtracting the debit value from the previous balance, while adding for credit), and 2) is like a macro in that it will automatically assign the formula to the latest transaction entry's balance cell when a value is either entered in the debit or credit cell. I don't want to have to copy and paste the formula and specify each transaction's debit and credit cells in the formula for every entry if at all possible.

Another good example of this exact behavior is when using a bank account register booklet where the balance is shown for every transaction. I want to duplicate this behavior in Calc yet make it automatic, without the need for excessive copying and pasting/modifying cell reference values. Would a good start be to use the IF conditional function seeing whether or not to subtract or add to the balance based upon which cell (debit or credit) has a positive money amount in it? Would using "> 0" for the conditional statement be used to determine whether or not cell is empty or not?

Thanks,
Jason
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sat Jul 19, 2008 12:34 am    Post subject: Reply with quote

You have almost answered your own question!

Yes, you would use the IF condition, something like

=IF(cell reference that has the credit in it>0;cell with previous balance + cell reference that has the credit in it;cell with previous balance - cell reference that has the debit in it)

and then fill/copy this down.

You might want a more robust method, that would check that you haven't got a credit and a debit in the same row, and that might use the AND(condition1;condition2;...) and ISNUMBER(cell reference) functions.

If you can't work those out, ask here again.

Let us know how you get on.
Back to top
View user's profile Send private message
jason102
General User
General User


Joined: 18 Jul 2008
Posts: 11

PostPosted: Sat Jul 19, 2008 2:28 pm    Post subject: Reply with quote

Thanks for your quick reply! I didn't know the AND and ISNUMBER functions existed! This is what I've come up with:
Code:
=IF(AND(ISNUMBER(D3);D3 > 0);F2-D3;IF(AND(ISNUMBER(E3);E3 > 0);F2+E3;F2))

where d3 is the debit cell of some transaction, e3 the credit cell, and f2 the previous transaction's balance.

I'm this far, but I'd like to make this method even work better! If at all possible, I would like to not have to copy/paste this formula over and over again for each individual transaction. That and having to change the cell reference values each time in the formula would be a pain. There isn't a way to associate this formula with the balance column as a whole? For instance, it would be very convenient if all I had to do was enter in a value for debit or credit, and then Calc would automatically setup the formula in the balance cell for the current transaction by also setting up the correct cell reference values for debit, credit, and the previous balance from the last transaction. I know you can use macros in Microsoft Excel to accomplish processes such as this. Can this be done via a similar fashion in Calc?
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Jul 20, 2008 7:50 am    Post subject: Reply with quote

Just copy the cell, select the required number of cells below, and paste.

All spreadsheets work like this!

Or use the fill feature with the mouse over the bottom right corner of the cell when you get the crosshairs, drag down.
Back to top
View user's profile Send private message
jason102
General User
General User


Joined: 18 Jul 2008
Posts: 11

PostPosted: Sun Jul 20, 2008 2:23 pm    Post subject: Reply with quote

Oh ok I see. Actually I have very little experience with any type of spreadsheet programs, so I didn't know it could do that either! Please don't mind my past ignorance! Thanks RickRandom, everything's working well, and my issue is resolved!
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