Problems with zero values and previous data

[bluefrog]

I'm trying to get a type of balance sheet together for a larger worksheet I'm working on but I am having difficulty with zero values.

On some months there won't be any data, so the line total will be zero, not a problem. That is until we want to continue with the next month (or several months in the future) where we include the data and the line total doesn't add to the previous known total.

i.e.

last input data in month 3 totalled £1000, no data until month 7 so all are zero. Then on month 7 there is a £1 loss, but this is just showing -£1 not £999. Now I have tried to do this many ways which just throw out circular reference problems, like doing a lookup for the last value in the column, works fine until you enter the next line of data and everything updates, lol.

Pulling my hair out right now so if anybody has any idea's I'll willingly try them out.

I have attached the file with demo data so you can see what I'm doing completely wrong. The first line of data will be drawn from another sheet so I have just added 1,000 for test purposes.

Line K7 should reflect the previous balance at K4, but after several attampts I'm giving up.

All help is appreciated, and you never know I may even end up with some hair left.

Bluefrog

[oldchippy]

Hi,

Is this what you want, K1 would need to say "Running Total", if you put this in K3 and drag down the column

=K2+C3+G3-E3-I3

[bluefrog]

Works OK but still shows values when there is no data present in the two columns. Ideally this should be left blank, and carried over until there is user input in either of the profit or loss columns. This eliminates the entire column having values when the user month is May, Jun-Dec should be blank or zero values, but when data is entered they should become positive (or negative) values again and show in the column, in relation to the last previous value visible.

Any idea's?

BTW, thanks for the input so far.

[oldchippy]

Hi,

May be this in K3

=IF(AND(C3="",E3="",G3="",I3=""),"",$K$2+SUM($C$3:C3)+SUM($G$3:G3)-SUM($E$3:E3)-SUM($I$3:I3))

and drag down

[bluefrog]

That seems to work perfectly, thanks 1,000,000 times.