Offset every # of Rows

J

Javier Diaz

Hello everyone, this is something I could just say = A1 then A=17 then A so
and so. But why, when I could use an offset formula, but jeezz louise, I
cant get it to work. I need a formula in I800to look at I63 bring back the
data, then on I801 to bring back I79 then on I802 to bring back I95. How can
I do this guys and gals.
 
J

JLatham

You could use something like this in I800 :
=OFFSET(I$63,(ROW()-ROW($I$800))*16,0)
and fill it down the sheet.

In row 800, that formula would return 0 for the middle value (rows to offset
from I$63)
in row 801 it would return 16, which would return the value 16 rows below
I$63 (at I79)
in row 802 it would return 32, which would return the value 32 rows below
I$63 (at I95)
 
S

Stan Brown

Thu, 12 Jul 2007 12:12:04 -0700 from Javier Diaz
Hello everyone, this is something I could just say = A1 then A=17 then A so
and so. But why, when I could use an offset formula, but jeezz louise, I
cant get it to work. I need a formula in I800to look at I63 bring back the
data, then on I801 to bring back I79 then on I802 to bring back I95. How can
I do this guys and gals.
Click to expand...

I have read this twice, and I have no idea what you want.

Please try to state clearly (and calmly!) what you are trying to do,
and I'm sure you'll get help.
 
H

Harlan Grove

Javier Diaz said:
. . . I need a formula in I800to look at I63 bring back the
data, then on I801 to bring back I79 then on I802 to bring back I95.
Click to expand...
....

If you mean I800 and subsequent cells would evaluate to the values of every
16th cell beginning with I63, here's an alternative.

I800:
=INDEX($I$63:$I$262,16*ROWS(I$800:I800)-15)

Fill down as needed.
 
J

Javier Diaz

Wow Alan, that was great, I resorted to just putting OFFSET(I$63,$H393,,1)
where $H393 and 394 and 395 increments by 13, but your formula is perfect, I
just changed the 16 to a 13 and the 800 to a 391 where the formula Starts.
You rock!
 
J

Javier Diaz

i'LL JUST TRY TO FIGURE OUT HOW TO CHANGE THE STRICTNESS OF THAT 800, i DONT
KNOW HOW INSERTING A ROW SOMEWHERE COULD CORRUPT THIS FORMULA. i'LL HAVE TO
TEST IT.
 
P

Peo Sjoblom

That is a good reason for not using ROW()

=OFFSET(I$63,16*(ROWS($A$1:A1)-1),)


can be put anywhere and it will not be affected if you delete or insert rows

I would also use INDEX instead of OFFSET since it's not volatile


Finally, don't use caps when posting, it is considered impolite




--
Regards,

Peo Sjoblom
 
Top