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
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=WIblemrainbowprickles 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
- Thank you so much, it's working out so well now! I was just wondering if you could explain the formulas? I'd like to learn how to do it on my own for future references.