functions/formulas

A

AccountantPB

How do I reference the formula above and get the result off 101 rows below
that formula? I need to reference every 101 st row but put the information
one row on top of each other.

Thanks
 
G

Gary Brown

If the formula above is in A1 and your forumla is in B1, try...
=OFFSET($A$1,101*ROW()-1,0)

How to read this formula.

'OFFSET(A1' = Get the value in the cell that is offset from cell A1 by...

'101*ROW()' = 101 rows down times the row # that this forumla is on. ie. if
this formula is on row 1 multiply 101 * 1 to look at row 101, if on row 2,
multiply 101 * 2 to look at row 202, 303, 404, etc.

',0)' = Get the value in the cell that is offset from cell A1 by...
-0- columns from Column A.

HTH,
 
B

Bernie Deitrick

If your formulas start in cell A3, for example, and you want to pull from
row 10, 111, 212, etc of column B, then in cell A3 use the formula

=INDIRECT("B" & (ROW()-ROW($A$3))*101+10)

and copy down as far as you need.

HTH,
Bernie
MS Excel MVP
 
Top