Sep 20 2022 07:45 AM - edited Sep 20 2022 07:46 AM
Hi everyone! Thanks for helping in advance.
I'm trying to create an excel sheet where I am able to do a running balance of my income and expense for my small business.
C: Income / Expense
D:Amount
E: Running Balance
Basically, I want a formula that is able to capture that I have stated that if C is income, then it'll add the amount in D to the running balance, and the opposite to happen where if C is an expense, then it'll minus the amount in D to the running balance.
I also have a "current balance" cell at the top as a summary of my current expenditure.
Please help! I have been trying to find something and also trying out different formulas but they all don't seem to work. Most of the forums show the amounts as 2 separate columns but I'd like them to be in the same column; not sure if this is possible. Please enlighten me!
Sep 20 2022 08:31 AM
This assumes you are entering the formula in cell E5 and the values in D are positive regardless of being income or expense.
=IF(C5="income",IFERROR(E4+D5,D5),E4-D5)
Just copy the above formula down for every entry in your balance sheet.
And for the balance summary shown in D1:
=OFFSET(E4,COUNTA(E:E)-1,0)
If you have Excel 365, dynamic arrays can be used to avoid copying the formula down for each entry but I've stuck with the legacy approach not knowing what version you are using.
Hope this helps,
Dexter
Sep 20 2022 10:13 AM - edited Sep 20 2022 10:15 AM
Seems to be an issue.
I have categorised the Income and Expense as a Data Validation, so when I click on the cell in that column, it comes out as [@[Income / Expense]] instead of the cell number. Does that affect?
Also, here are the exact cells.
C: Income / Expense
G: Amount
H: Running Balance
G3: Current Balance (the summary that I was referring to.)
The first row, 6, is my capital amount which I have the first input of an amount.
Sep 20 2022 10:24 AM
@rainbowprickles Thanks rainbow, any chance you could share a copy representative of your current workbook with any private details removed?
Sep 20 2022 10:01 PM
Sep 20 2022 11:35 PM
Solution@rainbowprickles Thanks for sharing.
I made a minor tweak to the formula entered in H7, because it is in a table you shouldn't have to re-enter it as new rows are added.
=IF(C7="income",IFERROR(H6+G7,G7),IFERROR(H6-G7,G7))
Another minor tweak to the current balance:
=OFFSET(H6,COUNTA(H:H)-1,0)
Here is a screen shot showing the ranges the running balance utilizes:
And here is the formula in action showing both the running balance and the current balance being updated as new records are entered/modified:
Hope this helps.
Let me know of any other questions. Revised workbook is attached.
Dexter
Sep 21 2022 09:19 AM
Sep 21 2022 04:51 PM
@rainbowprickles My pleasure.
The if formula is very commonly used.
=If(
condition to evaluate,
value to return if the condition is true,
value to return if the condition if false
)
In your case if the value in cell C7 is "income" then return the previous balance in the running total and add the amount in G7. Otherwise, return the previous balance in the running total and subtract the amount in G7.
=IF(C7="income",IFERROR(H6+G7,G7),IFERROR(H6-G7,G7))
The IFERROR formula is similar in nature but it's condition is already implied. In this case, if the formula H6+G7 returns an error, then use the value in G7 instead. The only time I expect an error is in the very first row because it is trying to add the text header "Running Balance" to a number which doesn't work too well. So, our starting balance will always be determined by the first row. Perhaps inserting a row above row 7 is appropriate and ensure the value in G7 is, indeed, the starting balance.
The next formula is not as common, but useful in managing references to ranges which are always growing and/or shrinking, particularly if they are not inside a table object. It actually receives up to 5 inputs, although only 3 are used in your example
=OFFSET(H6,COUNTA(H:H)-1,0)
=OFFSET(
)
So in your case, the Starting Cell Reference was H6 or the header "Running Balance". To determine how many rows to offset, we count the number of cells with data in column H using COUNTA(H:H). There are 8 cells containing data and we subtract one for the header row. Therefor the formula starts with H6 and moves 7 rows down which is row 13.
But lets say we wanted to sum the values in the amount column we would use
=OFFSET(
)
This would evaluate to be cells G7:G13. And if we wanted to sum the values in that range we would simply wrap the offset formula inside a SUM() formula. I said particularly for ranges not within a table object because you can apply a formula to a table range and it will always grow or shrink according to how many rows are in the table by referring to Table_Name[Header_Name] instead of normal cell references.
Hope this clarifies and glad to help.
Dexter
Sep 20 2022 11:35 PM
Solution@rainbowprickles Thanks for sharing.
I made a minor tweak to the formula entered in H7, because it is in a table you shouldn't have to re-enter it as new rows are added.
=IF(C7="income",IFERROR(H6+G7,G7),IFERROR(H6-G7,G7))
Another minor tweak to the current balance:
=OFFSET(H6,COUNTA(H:H)-1,0)
Here is a screen shot showing the ranges the running balance utilizes:
And here is the formula in action showing both the running balance and the current balance being updated as new records are entered/modified:
Hope this helps.
Let me know of any other questions. Revised workbook is attached.
Dexter