Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to set up a simple variable in Excel, but cell location independent | New Users to Excel | |||
Input Box selects a cell location to use in code | Excel Programming | |||
Way to store cell location to variable? | Excel Programming | |||
VBA - Passing Cell Location To Variable | Excel Programming | |||
using variable as cell location | Excel Programming |