Creating a running balance sheet

[Graham_Barclay_Barx]

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?

[#2 · Accepted solution]

@Graham_Barclay_Barx 

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.

[#3]

@Graham_Barclay_Barx 

=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.

 

[#4]

@OliverScheurich 

Thank you. Problem solved.

[#5]

Thanks for your help. Problem solved.