Debit and Credit Running Balance - display running balances as zero/blank when no new data!

[little_johnny]

Howdy everyone!

I have made a template that I want to use by filling in data as necessary, and showing me a running balance and totals at the bottom.

The running balance is as follows:

Column F = Debit (no. of items gone out on consignment with contractor)
Column H = Credit A (no. of items paid for)
Column J = Credit B (no. of items returned)
Column L = Balance (what is outstanding)

Please refer to picture to get an idea.

The debits and credit columns are all summed for totals into Row 27, and the "Balance Total" is achieved by L27 =SUM($F27,-$H27,-$J27)

I also have a running balance going down each row which is achieved by adding any new debits into the preceding balance, then subtracting any new credits. Eg: L19 =SUM(L18,F19,-H19,-J19). L20 =SUM(L19,F20,-H20,-J20).

The question now is: How do I display zero or a blank entry for remaining balances (in example above - L21:L26), since those rows don't contain any new data filled into the template, rather than a repeat of the last balance calculated into L20?

I have tried a couple of IF functions, but I'm coming up with too many errors and thought it was time to seek some help!

So any ideas of how I could modify the formula to display a running zero balance if no new data has been added would be greatly appreciated!

- Johnny

 

[KNRD87]

Using an if function was a good idea, check wheter there is sth in column C i.e. =if(c21<>"",your formula,"")

 

[little_johnny]

Using an if function was a good idea, check wheter there is sth in column C i.e. =if(c21<>"",your formula,"")

Hi KNRD87,

Thanks for the quick response. Whilst that formula worked in eliminating the repeated value, it didn't actually solve my problem, because if I input a value into Column H or J (which are credits, and would have an effect on the balance), the cell still remains empty as that IF formula is asking it to be empty if c21 is empty.

What I basically need is a formula that says and does the following:

IF F20, H20, or J20 are empty, then L20 is blank - otherwise, if data is entered into ANY one of F20, H20, or J20, then L20 =SUM(L19,F20,-H20,-J20).

 

[jasonb75]

Try

=IF(COUNT(F20,H20,J20),SUM(L19,F20,-N(H20),-N(J20)),"")

Edited to eliminate potential #Value errors.

 

[little_johnny]

Try

=IF(COUNT(F20,H20,J20),SUM(L19,F20,-N(H20),-N(J20)),"")

Edited to eliminate potential #Value errors.

BRILLIANT!!!

Can't thank you enough, worked perfect!

(Although I'm an amateur and I can't seem to understand how it works?)

thanks once again!

 

[jasonb75]

COUNT(F20,H20,J20) counts the number of numeric values in those cells, 0 returns FALSE to the IF statement, any other value returns TRUE, which calculates the result of your original formula

SUM(L19,F20,-N(H20),-N(J20))

The N() function used on H20 and J20 is used to evaluate text strings to a value of 0 which prevents the error that you would get from -"" when there is a text string in the cell.

 

[little_johnny]

COUNT(F20,H20,J20) counts the number of numeric values in those cells, 0 returns FALSE to the IF statement, any other value returns TRUE, which calculates the result of your original formula

SUM(L19,F20,-N(H20),-N(J20))

The N() function used on H20 and J20 is used to evaluate text strings to a value of 0 which prevents the error that you would get from -"" when there is a text string in the cell.

I'd come across Count function much earlier in forums, but didn't actually understand it - now it makes perfect sense when used in the context of my problem. thanks

I didn't even know the N() function existed. I don't understand how it was needed in this case though. I've tried the formula without N() function and it still seems to work the same...