Forum Discussion
Not sure how to do a balance sheet on excel
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
đ§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!
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
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
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.
rainbowprickles Thanks rainbow, any chance you could share a copy representative of your current workbook with any private details removed?
