Offset function

[jamalhakem]

Hi
How can I use Offset function to copy formulae, in one column and 4
rows, or every 4 rowas
Thnaks in Advance
Jam

 

[Mike H]

Can we see an example of what you expect to see when the formula is copied
down?

 

[Ron Rosenfeld]

Hi
How can I use Offset function to copy formulae, in one column and 4
rows, or every 4 rowas
Thnaks in Advance
Jam

The OFFSET worksheet function returns a reference to a range. It does not
"copy formulas". This is mentioned in HELP for the OFFSET worksheet function.

If you could be more specific in exactly what you want to do, and present some
concrete examples, we could probably figure something out.
--ron

 

[JLatham]

I am not certain that I understand your request any better than the others,
but perhaps these formulas will assist you.

Condition: You have formulas showing value in column A beginning at row 1.
You wish to obtain a list of the values from column A at row 1, row 1+4 [row
5], row 1+(2*4) [row 9], et cetera.
This formula in another cell on row 1 and filled down would do that:
=OFFSET($A$1,(ROW()-ROW($A$1))*4,0)

If your information was in column G and began at row 2, then this formula in
another column on row 2 and filled down would achieve similar results:
=OFFSET($G$2,(ROW()-ROW($G$2))*4,0)

 

[ShaneDevenshire]

Hi,

If you are trying to sum every 4th row then here is one approach:

=SUM(SUM(INDIRECT("F"&ROW(A1:A20)*4)))

This is array entered: This means press Shift+Ctrl+Enter instead of Enter.
This formula adds the items in F4, F8, F12...

Or you can use the non array version:

=SUMPRODUCT(SUM(INDIRECT("F"&ROW(A1:A20)*4)))

If this helps, please click the Yes button.

 

[jamalhakem]

Hi Mike
I want to copy formulae in one sheet, this formulae is referring to
other sheet, in sheet1 the formulae repeat every 4 rows, ex. A11, A15,
etc..
In the sheet2 this formulae refering to sheet1 is repeating every 3
rows, ex. A10, A13, etc.., so the increament is minus 1, when I drag
or copy and paste, it gives the result of one row higher, and I have
to adjust the cell number every time.
Sheet1
A11 formulae
A15 formulae
in sheet2
A10 should be as A11 in sheet1
A13 shoulkd be as A15 in sheet1
Hope I am clear
Thanks in advance
Jam