[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
jason102
General User

Joined: 18 Jul 2008
Posts: 11

 Posted: Fri Jul 18, 2008 8:29 pm    Post subject: Money register balance cell formula 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
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Sat Jul 19, 2008 12:34 am    Post subject: 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.
jason102
General User

Joined: 18 Jul 2008
Posts: 11

Posted: Sat Jul 19, 2008 2:28 pm    Post subject:

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?
RickRandom
Super User

Joined: 27 Jan 2006
Posts: 1082
Location: UK

 Posted: Sun Jul 20, 2008 7:50 am    Post subject: 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.
jason102
General User

Joined: 18 Jul 2008
Posts: 11

 Posted: Sun Jul 20, 2008 2:23 pm    Post subject: 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!
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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