Dragging Down a Formula

[JBoyer]

Now my actually case is more complicated than this, but I think if someone
explains this to me, I'll be fine.

say i have a formula in cell b1 that is =a1*2
now in b2 i would have =a6*2
and in b3 i would have =a11*2

and continue to increase the cell value by five.

Can i do this be just dragging down the corner rather than entering them
manually?

 

[Bernie Deitrick]

In Cell B1, enter a formula like

=INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)*2

Note that the B1 and $B$1 are strictly needed - they should be the address
of the first cell that this is entered in, so that you don't have to limit
where you put the formula, and so that you can insert rows above and still
have the formula work.

This is the part that steps through a range by 5 rows everytime the formula
is copied down by one row

INDEX($A$1:$A$1000,(ROW(B1)-ROW($B$1))*5+1,1)
That becomes
INDEX($A$1:$A$1000,(1-1)*5+1,1)
INDEX($A$1:$A$1000,1,1)
or just A1

Copied down, you get

INDEX($A$1:$A$1000,(ROW(B2)-ROW($B$1))*5+1,1)
That becomes
INDEX($A$1:$A$1000,(2-1)*5+1,1)
INDEX($A$1:$A$1000,1*5+1,1)
INDEX($A$1:$A$1000,6,1)
or A6

etc.


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

"Note that the B1 and $B$1 are strictly needed "

What I meant was that they AREN'T strictly needed....

Boy, my fingers just don't obey my mind sometime....

Bernie

 

[JBoyer]

I should have been more clear with my example, here is the formula I'm using
=IF(ISBLANK(F3:L3),"",SUM(IF(F3:L3<0,F3:L3,FALSE))). This formula is in cell
Z2.

Now in cell Z3 I want =IF(ISBLANK(F8:L8),"",SUM(IF(F8:L8<0,F8:L8,FALSE))).

 

[Bernie Deitrick]

J,

In Z2, enter the formula

=IF(OFFSET($F$3,(ROW()-ROW())*5,0)="","",SUMIF(OFFSET($F$3:$L$3,(ROW()-ROW($Z$2))*5,0),"<0"))

and copy down.

HTH,
Bernie
MS Excel MVP