Create a Formula That Skips Every n Cells

the auto increment wont work for this situation, so you need to do it in formula, maybe
(I'm going to assume row 4 is where the c data starts)

Formula:

Please Login or Register  to view this content.

Drag down

Hope this helps

Try this

=INDIRECT("C"&ROW(4:4)*4-12)+ROW($A2)*INDIRECT("C"&ROW(6:6)*4-18)/INDIRECT("C"&ROW(7:7)*4-21)

See revised formula in the next post

My bad, forgot to lock the row() reference, sorry about that :

Formula:

Please Login or Register  to view this content.

EDIT -
As to a circular reference, you would know if it was, because excel will give a warning and ask if you want to continue
Should not be any, as the data being used is in no way affected by the formula, and the value of the cell the formula is in does not rely on the value of the references to the same cell...

Originally Posted by tj2434

Thank for the replies,

AlKey, your method also has potential. It returns values higher than what they should be though right now. Can you explain what the bolded part does "C"&ROW(4:4)*4-12 as well as why you chose to use ROW($A2) instead of another indirect string?

"C"&ROW(4:4)*4-12 creates a cell reference for use with indirect function. So if you try =ROW(4:4)*4-12 and drag it down you will see that it change from 4 to 8 and to 12, etc. ROW(4:4)*=16 -12 = 4) The "C" part and number 4 will make C4 and INDIRECT function converts this text into cell reference.

Hope this help.


And I think I found my mistake

It should be this

=(INDIRECT("C"&ROW(4:4)*4-12)+2*INDIRECT("C"&ROW(5:5)*4-15)+3*INDIRECT("C"&ROW(6:6)*4-18))/INDIRECT("C"&ROW(7:7)*4-21)

@ AlKey your missing an argument, and the +/* should be in parentheses' so the division happens to the whole value

N3=(C4+2*C5+3*C6)/C7

Originally Posted by dredwolf

@ AlKey your missing an argument, and the +/* should be in parentheses' so the division happens to the whole value

Thank you, this was already corrected. The formula now gives the same result as OPs.

Or

It is better to go for Non Volatile Approach....

=(INDEX(C:C,4+(ROW(A1)-1)*4)+2*INDEX(C:C,5+(ROW(A1)-1)*4)+3*INDEX(C:C,6+(ROW(A1)-1)*4))/INDEX(C:C,7+(ROW(A1)-1)*4)

@ AlKey, yes you did, and good for you !
but please do not get angry with me for pointing out a deficiency that you corrected AFTER I pointed it out, I make many mistakes, and TRY not get upset when someone points them out ...

Originally Posted by dredwolf

@ AlKey, yes you did, and good for you !
but please do not get angry with me for pointing out a deficiency that you corrected AFTER I pointed it out, I make many mistakes, and TRY not get upset when someone points them out ...

Dear dredwolf,

It was never my intention to display any anger towards you and I am very sorry that you took that way. I am very thankful to you and to anyone who helps me to become better in what I love to do. One thing that I learned in life is that no matter how much you know, there is still more left to learn. So, once again please accept my sincere apology.

Alex

Just to echo Sixthsense's comment, this type of 'every nth row' problem can almost always be achieved by either an INDEX or INDIRECT approach, though I agree that, given this choice, it's preferable to take the non-volatile (i.e. INDEX) option.

Regards

You are welcome

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).