I've just switched from Numbers 09 to Excel 2011 because I want to use pivot tables to evaluate my personal financial/budget data and I want to continue having a "cleared balance" column as well as a "running total" column.
I used a single "amount" column for the amount of the transaction in Numbers.
Cleared for me means that the amount has cleared my bank, so that column's total always matches my bank balance.
Running total is simply a list of all our expenses and that column's total represents the amount of money we would have if every bill was paid and ever check cleared on the spot. I use it for making sure we aren't over spending.
In Numbers 09, I use a check mark column, which I check when an item clears the bank and a formula then populates the cleared balance cell. The formula I'm using is:
=IF(I4,$L$2+(SUMIF($I$3:I$4,TRUE,$J$3:J$4)),"")
where:
I is the check mark column
L is the cleared balance column
J is the amount column
I copied the formula from a discussion group years ago and don't remember where I found it, but it worked perfectly.
However, in Excel I can't make the check mark function serve the same purpose.
Also, I've tried adding two new columns for the transaction amount (Debit & Credit) as well as a new Cleared column that I'm putting a "x" in if the item has cleared. I've created a new formula, which works ONLY if the items clear in date succession otherwise I get a #VALUE! error (i.e., the formula only returns an amount if the item above it has cleared as well.). The formula I'm using is:
=IF(L4="","",J3+D4-E4)
where:
L is the cleared column
J is the cleared balance column
D is the debit column
E is the credit column
Any help anyone can provide will be very much appreciated.
pam
I used a single "amount" column for the amount of the transaction in Numbers.
Cleared for me means that the amount has cleared my bank, so that column's total always matches my bank balance.
Running total is simply a list of all our expenses and that column's total represents the amount of money we would have if every bill was paid and ever check cleared on the spot. I use it for making sure we aren't over spending.
In Numbers 09, I use a check mark column, which I check when an item clears the bank and a formula then populates the cleared balance cell. The formula I'm using is:
=IF(I4,$L$2+(SUMIF($I$3:I$4,TRUE,$J$3:J$4)),"")
where:
I is the check mark column
L is the cleared balance column
J is the amount column
I copied the formula from a discussion group years ago and don't remember where I found it, but it worked perfectly.
However, in Excel I can't make the check mark function serve the same purpose.
Also, I've tried adding two new columns for the transaction amount (Debit & Credit) as well as a new Cleared column that I'm putting a "x" in if the item has cleared. I've created a new formula, which works ONLY if the items clear in date succession otherwise I get a #VALUE! error (i.e., the formula only returns an amount if the item above it has cleared as well.). The formula I'm using is:
=IF(L4="","",J3+D4-E4)
where:
L is the cleared column
J is the cleared balance column
D is the debit column
E is the credit column
Any help anyone can provide will be very much appreciated.
pam
