Forum Discussion
Creating a running balance sheet
Hi,
I'm new to this so bear with me!
I can create a running total or balance where either an income or expenditure amount has been added.
C1 = the balance
B1 = Income
A1 = Expenditure
=(C1+(B1-A1))
However, when no amounts have been added I want the balance amount to remain empty and cant find a solution?
Can anyone help?
To create a running balance sheet where the balance amount remains empty when no amounts have been added, you can use an IF statement to check if there are income or expenditure amounts before calculating the balance.
Here is an example formula that you can use:
=IF(OR(ISNUMBER(B1),ISNUMBER(A1)),C1+(B1-A1),"")
This formula checks if either the cell B1 (income) or A1 (expenditure) contains a number. If either cell has a number, it calculates the balance by subtracting the expenditure from the income and adds it to the previous balance in cell C1. If neither cell has a number, it returns an empty string, which will keep the balance cell empty.
You can copy this formula to other cells in the balance sheet column to calculate the running balance for subsequent rows.
Note: Make sure to adjust the cell references (B1, A1, C1) based on your specific worksheet layout.
To create a running balance sheet where the balance amount remains empty when no amounts have been added, you can use an IF statement to check if there are income or expenditure amounts before calculating the balance.
Here is an example formula that you can use:
=IF(OR(ISNUMBER(B1),ISNUMBER(A1)),C1+(B1-A1),"")
This formula checks if either the cell B1 (income) or A1 (expenditure) contains a number. If either cell has a number, it calculates the balance by subtracting the expenditure from the income and adds it to the previous balance in cell C1. If neither cell has a number, it returns an empty string, which will keep the balance cell empty.
You can copy this formula to other cells in the balance sheet column to calculate the running balance for subsequent rows.
Note: Make sure to adjust the cell references (B1, A1, C1) based on your specific worksheet layout.
- Thanks for your help. Problem solved.
=IF(AND(ISBLANK(A2),ISBLANK(B2)),"",INDEX($C$1:C1,LARGE(IF($C$1:C1<>"",ROW($C$1:C1)),1))+B2-A2)An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
Thank you. Problem solved.
