Offset Formula Help

[CROY1985]

Hi all

I need an offset formula that will allow me to quickly copy and paste (without changing references) and in the following mannor: (I have converted the column letter into its number form)

in sheet 1, Column 28 will be reffering to column 3 on sheet 2, 36 will refer to 6, and 44 will refer to 9.

I.E for every 8 columns across on sheet 1, i need the cell its refernecing on sheet 2 to move across by 3 columns.

Thanks

 

[francomusso]

This seesm to work for me. Please let me know how you go on. If successful, I'll explain what's going on in the formula...

=OFFSET(Sheet1!$A$2,-1,(COLUMN(A2)/(8/3)))

Important - Remember to replace $A$2 with whichever is your starting cell on sheet 1

All the best,
Franco

 

[CROY1985]

Hi mate thanks for that. I had already found a sollutions but yours seems to be better (as its shorter),

Here is mine anyway:

...starting at AB8

=IF(OFFSET('Bradford'!$C$5,0,(((COLUMN()-COLUMN($AB8))/8)*3)+1)=0,OFFSET('Bradford'!$C$5,0,(((COLUMN()-COLUMN($AB8))/8)*3)),OFFSET('Bradford'!$C$5,0,(((COLUMN()-COLUMN($AB8))/8)*3)+1))

Ive incorporated it into an if statement where if it returns a 0, it will display the number to its left (the one with +1 is a revised budget, the other is an original budget).

I'm interested how the column part of your formula works though - like how does it know how far back to count?

Thanks

 

[Peter_SSs]

Hi mate thanks for that. I had already found a sollutions but yours seems to be better (as its shorter),

Here is mine anyway:

...starting at AB8

=IF(OFFSET('Bradford'!$C$5,0,(((COLUMN()-COLUMN($AB8))/8)*3)+1)=0,OFFSET('Bradford'!$C$5,0,(((COLUMN()-COLUMN($AB8))/8)*3)),OFFSET('Bradford'!$C$5,0,(((COLUMN()-COLUMN($AB8))/8)*3)+1))

Ive incorporated it into an if statement where if it returns a 0, it will display the number to its left (the one with +1 is a revised budget, the other is an original budget).

I'm interested how the column part of your formula works though - like how does it know how far back to count?

Thanks

The formula you have quoted here does not seem to refer to the cells described in your first post. However, you seem to be saying this does what you want so I have worked with it.

I don't know how many of these OFFSET formulas you have on your sheet or if you are aware that OFFSET is a volatile function so can slow your sheet if used a lot.

Here is a non-volatile alternative that I think does the same as your OFFSET formula. It can also be copied across every 8 cells from column AB. Before copying across, you may have to change the two $AZ column references in my formula since I don't know how far across the 'Bradford' sheet your formula may need to reference.

=INDEX(Bradford!$C$5:$AZ$5,COLUMNS($AB8:AB8)/8*3+2-(INDEX(Bradford!$C$5:$AZ$5,COLUMNS($AB8:AB8)/8*3+2)=0))

 

[CROY1985]

Thanks for that, its really useful and im going to incorporate it into this work.

I'm going to try and use it on another project ive been working on that is ****REALLY**** slow because i have offset formulas numbering well into the 100,000s, if not millions lol.

Out of interest how would this work as an index formula: (this is the one i have in the 100,000s)

=IF(ISERROR(OFFSET(B$8,0,-(ROW()-ROW(B$12)))*'Flow Inputs'!$B33),0,(OFFSET(B$8,0,-(ROW()-ROW(B$12)))*'Flow Inputs'!$B33))

What its doing is basically calculating a drop out rate. B8 is the intake for week 1, and flow inputs b33 is the drop out percentage for week 1, so what we end up with is a cascade that puts the drop outs from week 1s intake into the column that corresponds with the week 1 they drop out. Flow inputs b34 is the week 2 drop out %, 35 is week 3 etc.

Hope this is enough info...

Thanks

 

[Peter_SSs]

Hope this is enough info...

It's not, though without knowing your sheets and requiremnents it is difficult for me to be sure what I need to know.

However, I would want to know at least ..

- Which cell is this formula in?

- Why are you using OFFSET in the first place? Is it to do with copying to non-adjacent cells like the other one? If so, give details about where it would be copied to.

- Which Excel version you are using?

 

[CROY1985]

Yeah its a tricky one to explain in words. Il have another shot:

We can forget about the multiply bit for now. Il just concentrate on the offset.

My figures are calculated weekly, and column B corresponds to Week 1, Column C is week 2, etc.

Row 8 contains our intake of clients.

Row 12 contains number of clients who leave in week 1 of their course
Row 13 is clients leaving week 2, Row 14 is clients leaving week3, all the way down to row 55 which is clients leaving in week 44. (it stops at 44 as this is the length of the course, and if a client hasnt dropped out at this point, it meants they have completed.)

Now, the aim of the offset, is to copy the figure from B8 into B12, C13, D14, E15, etc, at which point the percentage rate can then be applied.

I hope that is more clear.

Thanks

 

[Peter_SSs]

I hope that is more clear.

That description may help in due course but as far as I can tell, you didn't directly answer any of the three specific questions I asked.

 

[CROY1985]

Sorry, i have a habbit of going off on a tangent.

1) the formula is in cell B12, and Needs to be replicated in the entire range B12:GG55.

2) Using offset as for every row i copy the formula down, the column reference needs to move one place to the left. So in the columns section of the offset formula i have this; -(Row()-Row(b$12))
Eg. in Row 14, it moves -(14-12) or -2

3) Excel 2007.

 

[Peter_SSs]

1) the formula is in cell B12, and Needs to be replicated in the entire range B12:GG55.

2) Using offset as for every row i copy the formula down, the column reference needs to move one place to the left. So in the columns section of the offset formula i have this; -(Row()-Row(b$12))
Eg. in Row 14, it moves -(14-12) or -2

3) Excel 2007.

Thank you.

See if this formula in B12, copied across and down, does the same job.

=IF(COLUMNS($A12:B12)-ROWS(B$12:B12)<0,0,INDEX($A$8:$GG$8,COLUMNS($A12:B12)-ROWS(B$12:B12)+1)*'Flow Inputs'!$B33)