Increment Forumula Series by 4 each row

[devdave]

I have a set of forumulas that I want to drag down an Excel Sheet and
have the Cells increment by 4 each time rather than 1. I can't find an
automated way to do this in Excel so I'm having to hand edit each
formula line.

Here is an example forumula and the next one that I'd like incremented
by 4 Cells:

=IF('Message Details'!C93 <= 0,"",'Message Details'!C93)

I want the next Cell below this one to be:

=IF('Message Details'!C97 <= 0,"",'Message Details'!C97)

But if I use the Drag AutoFill the formula's only incrment by 1.

Any ideas how to fill a column that increments by 4 on each of the
Cells used for Calculation?

Thanks,
DevDave

 

[Don Guillett]

=whatever*ROW(A1)*4
& drag down

 

[devdave]

That didn't seem to work - but I'm sure there is some way...

 

[Don Guillett]

modify to suit. Obviously, won't work for the FIRST one.

=IF(INDIRECT("sheet22!a"&ROW(A1)*4)<=0,"",INDIRECT("sheet22!a"&ROW(A1)*4))

 

[Bob Umlas]

Don was saying to use this formula (I modified it) & fill down:
=IF(OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0) <= 0,"",OFFSET('Message
Details'!$C$93,ROW(A1)*4-4,0))
Bob Umlas
Excel MVP

 

[orbii]

=OFFSET(A1,ROW(A1)*3-3,0)

bob i think *3-3 be more correct on the skipping part....

aloha n happy new year, orbii

 

[devdave]

Bob -

This: =IF(OFFSET('Message Details'!$C$93,ROW(A1)*4-4,0) <=
0,"",OFFSET('Message
Details'!$C$93,ROW(A1)*4-4,0))

Works Great.

I guess I don't understand how 'offsets' work as I don't know what
"Row(a1)*4-4,0" is doing that makes this work.

But it is producing the correct offset and result from the other
worksheet.

 

[devdave]

So I went and looked at what OFFSET does and what ROW does and this all
makes perfect sense now!

You would think that with Excel - there would be an easier way to
OFFSET based on a Selected Series that increments in a set pattern, but
this worked fine

Thanks for all your input.
DevDave

 

[Ragdyer]

Here's another way ... which is non-volatile.

This procedure also *doesn't* lend itself to a description of being easier,
but it can be perhaps more intuitive, if you just examine the math involved

=IF(INDEX('Message Details'!C:C,4*ROWS($1:23)+1)<= 0,"",INDEX('Message
Details'!C:C,4*ROWS($1:23)+1))

And copy down as needed.