Rolling sum cell variable location input
 

I'm constructing a monthly cash flow statement and would like my accounts
receivable value to be a function of two arguments; sales and days sales
outstanding (DSO). DSO would drive the column offset divided by 30.

Current period sales (cell x,y) would be copied to accounts receivable (cell
x+1,y+column offset)) = ar1.

The value of ar1 would be determined by not only the preceding period sales
but sales from several periods (columns) prior.

Any help is greatly appreciated. I thank you in advance.

Rolling sum cell variable location input
 

I've done it like this. You asked!

E48:
=SUM(OFFSET(E38,0,MAX(COLUMN(Begin)-COLUMN(),1-MONTH(E$33)),1,1-MAX(COLUMN(Begin)-COLUMN(),1-MONTH(E$33))))

The cell I'm offsetting from is E38
Row offset = 0
Column offset = MAX(COLUMN(Begin)-COLUMN(),1-MONTH(E$33))
Either counts the number of columns from Begin (B33) to the current
column or
calculates the "MONTH" of cell E33. (I have dates in row 33 that are
incremented by month using a formula like this
C33: =Date(YEAR(B33),MONTH(B33)+1,DAY(B33))
Rows in range = 1
Columns in range:
1-MAX(COLUMN(Begin)-COLUMN(),1-MONTH(E$33))
Maximum of B33 (column - Current COlumn) or 1-Month(serial date in E33)

I'd suggest you wrap your brain around the OFFSET function and it will help
you a lot.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"ADP" wrote:

I'm constructing a monthly cash flow statement and would like my accounts
receivable value to be a function of two arguments; sales and days sales
outstanding (DSO). DSO would drive the column offset divided by 30.

Current period sales (cell x,y) would be copied to accounts receivable (cell
x+1,y+column offset)) = ar1.

The value of ar1 would be determined by not only the preceding period sales
but sales from several periods (columns) prior.

Any help is greatly appreciated. I thank you in advance.