Hi all
I need help with on offset sum formula.
I have a cascading data flow of customers, sowing how many customers we lose each month. based on the initial inflow. Here is theformula i used to obtain the data flow:
=OFFSET(B$8,0,-(ROW()-ROW(B$12)*$b21
I have uploaded the output to my picassa account:
https://picasaweb.google.com/croy1985/Excel#5647687400779447330
Basically we have 13 periods per year, and I have highlighted the cells accordingly to show which belong to year 1 (yellow), year 2 (green) and year 3 (orange).
What i need is a set of formulas, that will enable me to calculate on any given period, how many customers are from year 1, year 2, year 3 etc.
In the picture ive posted, For Column AI, that would mean Year 1 = 0, Year2 = 72.4 and year 3 = 8.52.
And i need to be able to pull these formulas across a range of columns, s a dynamic formula that doesnt need to be changed for each column is what i am after.
Thank You.
I need help with on offset sum formula.
I have a cascading data flow of customers, sowing how many customers we lose each month. based on the initial inflow. Here is theformula i used to obtain the data flow:
=OFFSET(B$8,0,-(ROW()-ROW(B$12)*$b21
I have uploaded the output to my picassa account:
https://picasaweb.google.com/croy1985/Excel#5647687400779447330
Basically we have 13 periods per year, and I have highlighted the cells accordingly to show which belong to year 1 (yellow), year 2 (green) and year 3 (orange).
What i need is a set of formulas, that will enable me to calculate on any given period, how many customers are from year 1, year 2, year 3 etc.
In the picture ive posted, For Column AI, that would mean Year 1 = 0, Year2 = 72.4 and year 3 = 8.52.
And i need to be able to pull these formulas across a range of columns, s a dynamic formula that doesnt need to be changed for each column is what i am after.
Thank You.
