Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

For this question, I've created a Google Apps Script function, that calculates the cumulative sum of a range. Martin Hawksey on Google+ made a comment about a more efficient way of calculating the cumulative sum:

function cumulativeSum(array){
  var output = [];
  for (i in array){
    if(i==0) {
      if (array[i].length > 1) throw ("Only single column of data");
      output.push([array[i][0]]);
    } else {
      output.push([output[i-1][0] + array[i][0]]);
    }
  }
  return output;
}

My question is: can this be achieved with the usage of formula's?

share|improve this question
    
Apologies for a very late answer, I just saw he thread in "Related" on the right. –  AdamL Jun 30 '13 at 10:22
add comment

2 Answers

up vote 7 down vote accepted
+100

Yes:

=ArrayFormula(SUMIF(ROW(A1:A10);"<="&ROW(A1:A10);A1:A10))

Example of Cumulative Sum

The numbers in the green circle are the ones being summed, the ArrayFormula is populating all the others based on the cell it was pasted into, in this example I pasted in A11, B1, and C1 to show its effect.

This spreadsheet that I made a while ago attempts to explain how MMULT can be used for these sort of problems, and it also demonstrates the SUMIF solution on one of the sheets, which can (and probably should) be used for special cases like the one in this question.

share|improve this answer
    
I took this formula once (I think it was yours) on the Google Forum: SUMIF(ROW(A:A);"<"&ROW(A:A)), that didn't work. This one does. –  Jacob Jan Tuinstra Jun 30 '13 at 11:56
1  
Is there a program that you used to create that fancy screenshot with the drop shadow? –  Fogest Jul 3 '13 at 22:38
    
The image in the answer was edited in by another member: +Xavier Casto –  AdamL Jul 4 '13 at 0:10
    
@Fogest, I used a product called "Snagit" find out more about at techsmith.com/download/snagit –  Xavier Casto Jul 5 '13 at 15:49
    
@XavierCasto Oh, I've used this a LONG time ago, didn't remember it having this, though it was a long time ago. Thanks for the reply though, I like products by techsmith. –  Fogest Jul 5 '13 at 15:50
add comment

Assuming you want:

  Row | A | B | B's formula
  --------------------------------
   1  | 1 | 1 | =1     (or =A1)
   2  | 2 | 3 | =1+2   (or =A2+B1)
   3  | 3 | 6 | =1+2+3 (or =A3+B2)

etc...

Then you can use:=sum($A$1:A1) where A1 is the start of numbers to sum. Put that formula in B1 (or where ever you want) and fill it down, and it will sum all cells up to and including the row you're on. Alternately, you can use the =Ax+Bx-1 (x = current row, data in A, cumulative sum in B) format from the "B's formula" section above.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.