Tuesday, October 30, 2012

Google Spreadsheet Formula for cummulative sum



Question:


I have a google doc with a very simple form and I want it to calculate data live based on responses to the form.

My headings are:

DATE
AMOUNT
DESCRIPTION
TOTAL SPENT TO DATE
MONEY REMAINING

The form fills out the Date, amount, and description categories, but I would like the spreadsheet to calculate the Total Spent to Date based on a sum of the Amount column, and the Money Remaining Column should subtract the Total Spent to Date from a Given total.  Can you help me?

I know how to do this in excel, but I'm having a hard time with it recognizing the Form.  I tried using the Arrayforumula() and it does recognize the form, but I am unsure how to populate the formula correctly.

Thanks for any help you can give!



Solution:


Suppose you have following data in your spreadsheet:




Then put the following formula in Cell E1:
=ArrayFormula(IF(LEN(C:C);IF(ROW(C:C)=1;"TOTAL SPENT TO DATE";SUMIF(ROW(C:C);"<="&ROW(C:C);C:C));IFERROR(1/0)))



Then put the following formula in Cell F1:

=ArrayFormula(IF(LEN(C:C);IF(ROW(C:C)=1;"MONEY REMAINING";H4-E:E);IFERROR(1/0)))


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 or confidential projects:


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,

2 comments:

  1. I have a question, not sure where to post it....
    I need to create a master sheet that has 14 other sperate sheets feed to it. Both the master and the separate sheets need to be live and update-able. Is this possible? If so, how do I do this? I have been playing with =importrange but am not able to add more then one other sheet. Help Please!

    ReplyDelete
  2. Hi! You may be able to help me.

    I'd like to make onEdit(e) {} detect when a background cell has changed its colour...


    actually, i'm not sure if onEdit is able to handdle this event (background Color change)

    do you have a solution?

    ReplyDelete