Forum Discussion
Not sure how to do a balance sheet on excel
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?
- I can't seem to take a screengrab so I have made a separate document for you to view.
https://1drv.ms/x/s!Agw6dWGNwj0JnFN6xR320-KHPIZD?e=WIblem
