making formula work right?

[Brown]

I am wondering how I can formulate my formula to work correctly. I am trying to get my formula to stop increasing by one in the range each time I copy it to another cell and increase by 7, because the range is 7 cells long. Is there a way to do this or am I going to have to hand correct 11000 cells?
For instance, I am pulling from cells r1:r7 for my range on the first formula, and then from r8:r14 on the second formula, but when I copy the formula in cell 1(r1:r7) and paste it into cell 2 I get the formula (r2:r8) showing and it throws off my data.
I hope that made sense.
Brown

 

[villy]

Use absolute value like $A$1:$A$7

 

[Dan Waterloo]

Hi Brown,

Try something like

=SUM(OFFSET($R$1, 7*(ROW()-1), 0, 7, 1))

Place it in A1 and then copy it down the column. It would be worthwhile to read up on the OFFSET and ROW functions to understand this better. An alternative approach would be to use the ROW function in conjunction with the INDIRECT function.

 

[Dan Waterloo]

Oh, and I just popped in the SUM function because you were doing SOMETHING with the range but I'm not sure what. If I just returned the range I would receive a VALUE error.

 

[Brown]

Thanks I will try it.
Brown

 

[Brown]

=COUNTIF(testing!R3:R9,"<70") is the formula I have in the cell A1
in cell B1 I need =countif(testing!r10:r16,"<70"), and so on adding 7 to each part of the range.
Am I still wanting to know the same thing I asked earlier?
Sorry for the confusion
Thanks for the help
Brown

 

[Dan Waterloo]

I assumed that your formulas would be going down a column. Since they are moving from left to right, you'll need to substitute the COLUMN function for the ROW function.

In cell A1:

=COUNTIF(OFFSET(testing!$R$3, 7*(COLUMN()-1), 0, 7, 1), "<70" )

The same formula will be used in B1, C1, etc.

 

[Andrew Poulsom]

Try:

=COUNTIF(INDEX(testing!$R:$R,((COLUMN(A1)-COLUMN($A1))*7)+3):INDEX(testing!$R:$R,((COLUMN(A1)-COLUMN($A1))*7)+9),"<70")

 

[Brown]

Andrew,
Thank you for the time and the formula. I must be doing something wrong because when I placed it in the cell it returned a 0 and not the number it should have returned.
I apologize for making this more difficult than it needs to be.
Brown

 

[Dan Waterloo]

Hi Brown,

I have tried Andrew's formula and it worked for me. Have you tried my revised formula yet? Also, when a formula doesn't work for you, I suggest running Evaluate Formula on it. It's a button that can be found on the Fomula Auditing toolbar by going to to /View/Toolbars and checking "Formula Auditing".