# Creating running total of positive and negative numbers, SUM?

Aw bless you, thank you. So now, I’m taking a detour studying Calc, because I’m going to be using this spreadsheet multiple times, a couple of strategies, and following tweaks of strategies, for each market, each time scale and so on… that’s a LOT of tests. So, the more fluid and functional the spreadsheet, the better.

Thanks for your help and suggestions so far!

I may have misunderstood how you collect the data. I was assuming the A column would be dynamic data entry as you acquired the number so the formula would not place anything in the B column until more data had been appended to A with B showing simply the current accumulation.
If you want to see the “current balance” or “last” entry in column B there are a couple of potential solutions
One involves the use of autofilters and subtotal() whereas the other would involve counting the non zero cells in your predefined range and using this to identify the source address for an indirect() function.
As an example, I use both scenarios in a sheet with 4000+ rows and 20 columns with virtually instantaneous results, providing source data for charting milestone events.
I could provide some simplified examples if it helps.

Hold on, installing GIMP

Seems I’ve used my comment quota for the day…!

So, this is where I’m up to with the running total, from ‘Position 1’ data (input dynamically) to the running total on ‘Account Pips’. Being the perfectionist that I am, if there was a way that all those '107’s weren’t there, that’d be cool.

Reply: Oooo very nice, thank you!
Some form of penny has just dropped too, and the formula actually makes some form of sense, lovely

That =IF(C5"","",SUM(blah)) is awwwesome! now applying it to every single column hehe

Try This

simple count and hide.ods (13.9 KB)

1 Like

Ooops, I just noticed a mistype. The formula in F5 should read =IF(C5="";"";SUM(C\$4:C5)) …NOT C\$1
then everything is dragged down to replicate the formulae with the correct addresses
Hopefully, you noticed it was adding the count of losses into the running total.
I added that count after filling the column F formulae and never noticed my error. My apologies.
I’ve attached another copy with a further modification for if you want all the intermediate results to disappear and only the “active” total visible - Column E.
You’ll notice I simply “copied” the value of the first entry in column C - I was being lazy but the same syntax can be used to eliminate that value if you wish.
My rationale behind the simple creation of the first value was based upon your initial statement that it is only 100 lines long, so I figured the first value will never need any special testing, therefore, it was simpler to just “copy” the value.
The site suggests that these lengthy correspondences are best conducted as PMs so if you’re comfortable with that, running out of allowable commenting and need any further help then it might be more convenient.

simple count and hide 2.ods (14.6 KB)

So, turns out I cant send PMs (yet?). Just wouldn’t let me for some reason.

The formula worked fine - probably because I had to change the letters anyway, and managed to suss it out.

Here’s a copy of where I’m up to:
Backtesting.ods (17.0 KB)

I dunno whether this ought to be another question, but now I have the challenge of column J interacting with column P in some way, so as the ‘total account’ increases, the value of pip (as per the 2% risk) also increases accordingly. Fun fun!

Susssed
Backtesting.ods (17.3 KB)

I don’t seem to be able to PM you either. I’m no expert on this new discord forum but it may be because you haven’t “allowed” PMs in your profile. I have just returned from a very convivial evening so not really in the right condition to interpret your attachment. On the assumption, you’re in the UK, and I’m in an earlier timezone - Mañana😎

A few suggestions
BacktestingAudit.ods (23.5 KB)