Creating running total of positive and negative numbers, SUM?

English
,
#1

So, I have a column A with positive and negative numbers, and I’m trying to find a formula that’s creates a running total of those figures in column B. So in each new row of column A, it would create a sum in column B, of the previous cell + the latest figure from column A, up to 100 cells.

I could do that semi manually and make a unique formula for each cell 1-100 in column B, I’m just wondering if there’s a simpler way?

I have also a single cell that has the total sum from 1-100 of column A. I’m wondering if this cell can be integrated into the formula somehow?

Thanks for any help! Much appreciated!

#2

use in B1 simply: =A1
and in B2: B1+A2
and then comes the Trick: double-click the right/lower Corner of B2

1 Like
#3

The whole question is unclear. The phrase “up to 100 cells” makes me assume, that you want
B1=A1
B2=A1+A2
B3=A1+A2+A3
B100=A1+A2+...+A100 (max 100 cells reached) and now
B101=A2+A3+...+A101 (sum of last 100 cells)
B102=A3+A4+...+A102 (sum of last 100 cells)
… and so on.

#4

I found a video with the formula, so painfully simple!

It’s =SUM($A$1:A1) then dragged down the whole column B, it calculated the running total of column A. It does mean I have the present ‘total’ running parallel to empty cells, but that’s fine, just pleased I don’t have to manually enter the formula in 100 cells!

#5

Trying to formulate a backtesting spreadsheet for my rookie ventures into testing strategies on spread betting the stock market. This one was the least of my worries! Seems I now have to study Calc to get anywhere near to the fluidity I need.

Next up: formulating percentages in all sorts of weird and wonderful ways, and somehow translating pips, through percentage of account, into currency. Woohoo. If this question was unclear wait until I post that one! :innocent:

#8

If you want to hide the preceding subtotals then modify your formula to test the advancing cell in column a and if it’s empty then do nothing “”.
at B1 if(a2="";"";Sum(A$1:A2))

#9

“Running average” is a well established and rather clear term, but mostly used for a certain part of a sequenmce… “Running total” isn’t as well established, imo. You may mean "Sum (total) taken from the start of the sequence upto/downto the current element.
Your formula filled down as far as needed will actually return this “total up to here” per row, but it does it in an inefficient way. Doing it efficiently you only need one addition per row.
Well, recent “computers” are rather fast, but thinking either of a case where the sequence has a million elements or of a compoter needing a second for a single addition …
Using n for the length of the sequence your formula will need 0 additions in row 1, 1 addition in row 2, … (n-1) additions in row n. That’s n*(n-1)/2` additions. For 1000 rows, 1 s per addition that’s nearly 6 days.
Well, your PC is faster, and assuming 1 µs per addition the time is only 0.5 s,but you will charge the processor with more than the calculations for this single column.
Therefore: Modern hardware is permissive concerning inefficient methods, but O(n^2) time-complexity may result in problems nonetheless now and then.

1 Like
#11

Oh my…! :no_mouth: Ohh I see, it would be the ‘following’ subtotals…

E.g. I currently have 10 cells of data, to test out these formulas. For context, each spreadsheet is to account for 100 tests, so by the end of each test all 100 cells will be completed, so I have dragged the formula throughout all 100 cells of column B. The running total of my 10th cell is 107, so the remaining 90 cells on column B all have the value of 107, until I input the 11th cell on column A, ie, the 11th test.

(Response to WhiteKnight)

#13

Lupp, you’ve blown my brain…! :sweat_smile:

#15

No, the following cells have nothing “” because the if() test defines the A cells as empty “” as there’s no data and does nothing""

#6

Of course this is common knowledge among spreadsheet users. Since you are on the way to extend your experiences, I will attach a little example, not only showing the solution, but also explaining the concepts of absolute addressing vs. relative addressing a bit, and the ways the distinction was obscured by a change in the default notation for references in general.
simpleExampleRelativeAbsolute.ods (19.8 KB)

BTW: Learing about spreadsheets is a good idea. Using them for stock-market gambling is a bad one.
(Yes. I was talking of my opinion.)

#7

It’s very strategic gambling, if gambling at all. If anything, the most rules based gambling I’ve ever come across, high levels of risk management and discipline - starting with this spreadsheet. But yeah, the formulas are quite curious little things, how they can allow for different bits of data to relate with each other, very cool! Thanks for the info.

Until today, all I knew was the basic +,-,/,* and basic formatting of decimals and time etc :laughing:

#10

What’s “strategic gambling”?

#12

The kind where you hope not to lose so much;)

#14

That’s an ingenious concept!

#16

I was using the word gambling rather facetiously. I don’t consider it such, when strategies are refined and tested with historical data, establishing a winning rate of 60% over 100 tests, that’s quite a reasonable sample on which to exercise a little confidence in what I’m ‘gambling’ on. At which point the ‘bet’ is measured within a ratio that is both affordable and profitable. It’s not so dissimilar from these formulae actually. And that’s how people do code formulae to do the same thing, effectively… Gambling tends to be based on luck more so than strategy.

#17

Formulas, formulae :sweat_smile:

#18

Your sample may only randomly produce winning or losing entries. If your sample included every possible change - trillions of them - you would probably end up with just a 50:50 probability. All the large financial institutions employ hundreds of mathematicians with super-computers to try to beat the curve. I’m not convinced a spreadsheet could provide the necessary insight to “predict” beneficial changes in time for you to act upon them. Historical performance is no guarantee of future events.

#19

Well, there is a slight difference in spread betting to investment in shares, but yes. The spreadsheet is just to record the results of tests of particular strategies on particular markets with very specific rules, conditions, etc, and whilst the 60% success rate is hardly a fixed certainty, it does tend to be fairly consistent. If I were to test a strategy and only find a 40% winning rate that is going to be more likely to be a consistently losing strategy rather than a consistently winning one. That’s why the testing is helpful. What’s interesting, is that in the tutorial on this backtesting, the chap originally cited the strategy with a 60% success rate - after his 100 tests. Yet, with only 10 examples, it still resulted in a 60% success rate. I found that a tad compelling. But yet, obviously, if I wanted to, I could test it 200, 300, 500 times, before deciding to apply it to my ‘gambling’.

#20

I wish you all the success you would wish yourself.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.