Offset formula help.

[CROY1985]

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.

 

[CROY1985]

Sorry gys the link isnt working for the image. Hopefuly i have explained it in enough detail.

**Update** link appears to be working now.

 

[ukmikeb]

Christopher

Your offset formula is incomplete!

Perhaps this will help -

=SUM(OFFSET(Reference cell, No of Columns, No of Rows, Height, Width))

Also regarding the formula you have ROW(B$12) which can be replaced by 12 because you anchor it to that row irrespective of the column you're in.

hth

 

[CROY1985]

Hi Mike

The intial offset formula works as we want it to. Can you offer any help as to a sum that adds all re year 1 (yellow), year 2(green), year 3 (orange) etc. as per below.

Thanks