Sunday, June 9, 2013

yogi_Running Total In Column AF Starting With Row 9 Down Of Specified Number of Entries In Corresponding Rows Of Column AE

                                          Google Spreadsheet   Post  #1235
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 10, 2013
user Alvinator (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/g6gHzBZaBfk)
Sum of a counted amount of cells
I have a finance management spreadsheet/sheets and I need to know how to achieve a "X day total" column.  

I mean that for example, in Column A, I have the sums of each day's earnings, then in column B I would like to have a formula that would sum up the adjacent A cell along with the preceding X number of cells.  X would be defined at the top of the B column (B2 for example, as titles would be in the 1 row).


Syntax-wise I think it would be something along the lines of:
(B2 is the X number of days), for B3 and past:
=SUM([A3-B$2]:A3)
I used different types of brackets to show different levels.

I understand that if B2 is any number, say 7, the first 6 sums will be inaccurate, but that is fine, I'm well past concerned about the first week of work.

I did find this article (so yes I've done some searching) but it does not list the combination I am seeking nor is it still open to responses, hence why I am enquiring here.

Thank you all in advance for any light you may be able to shed upon the subject in question.
---
My apologies I assumed that to be a sufficient example as it already meets all of your criteria, however I will share the sheet I have anyway, this will only be shared until it is solved though, and only in read-only mode.

Look specifically at column AF, said column has a number in the 2nd row, that number I would like to control how MANY days prior and including that the total shows of the sum of AE values.

For example, if AF2 is "2", then each sum in the AF column would be of the corresponding AE cell and the one above it.
If however AF2 is "10", then each sum in the AF column would be of the corresponding AE cell and the nine above that.

I basically want a "running total of the last X days".

--------------------------------------------------------------------------------------------------------
just a note to user Alvinator ... you mentioned running total of number of entries equal to the number delineated in cell AF2 ... however for your example of AF2=7 your posted result shows you have a running total of 8 ... so i have formulated the solution on the basis that you want running total of number of entries equal to 1 more than in cell AF2.

No comments:

Post a Comment