Google Spreadsheet Cumulative Addition

Question:

( by papawa )

I've spent an hour now trying to figure this out.

I just need a simple running total of expenses and dont want to retype the sum function for every cell.

Thanks,

Warren

Solution:

If you want to have solution without using script (that is formula based solution) then try the following formulas in Cell F1:

=ArrayFormula( if( row(E:E)=1 ; "Balance" ;if(E:E="","",mmult((row(E:E)>=transpose(row(E:E)))*transpose(E:E),row(E:E)^0))))

OR

If you will have dates in Column A then you can also try the following:

=ArrayFormula( IF( ROW( B:B ) = 1 ; "Balance" ; IF( LEN( E:E ) ; SUMIF( A:A ; "<=" & A:A ; E:E ) ; IFERROR( 1/0 ) ) ) )

========

Following is the solution using script..

Have a look at the following screenshot:

I have the following formula in Cell F2:

=total(E2:E10)

the above formula is a custom function total() that I have written and inserted in Script.

Have a look at the following code:

///////////////////////////////////////

function total() {
  if(arguments.length < 1 || arguments.length > 1) return "Please input 1xN";
  if( arguments[0].constructor != Array ) return arguments[0];
  var values = arguments[0],totalArray=[],sum=parseInt(values[0],10);
  totalArray.push([ sum ]);
  for(var i=1;i<values.length;i++) {
    if( !isNaN(parseInt(values[i],10)) ) {
      sum+=parseInt(values[i],10);
      totalArray.push([ sum ]);
    } else totalArray.push([ '' ]);
  }
  return totalArray;
}

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive