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

 

[Brown]

When I place the formula in the cell it returns a zero for me. I may just be having one of those days.
Should I place Andrew's formula in Cell A1 or place my formula in A1 and his in A2?
Sorry for the confusion
Brown

 

[Andrew Poulsom]

It doesn't matter which cell you place the formula in. The reference to A1 is only for offsetting purposes.

 

[amtrad]

Hi I posted another query and saw your post, this works for me if you want to sum the 7 cell range. In D1 you enter 0, D2 enter 7 and then copy down as far as you need, this gives the offset the incermental 7 sevens you want to sum.

=SUM(OFFSET($R$1,D1,0,7,1))

Hope that helps.

 

[Brown]

I am not sure what I am doing wrong guys.
Here is what I have, again.
Testing (sheet 1)
I need a range of cells from this sheet, r3:r9
Compiled (sheet 2)
In cell A2 I am asking to check the range r3:r9 (from the sheet testing) for a certain criteria (grades "<70").

When I create the formula =countif(testing!r3:r9,"<70") it works, but then when I copy it to A3 it changes the formula to r4:r10, and I need it to change it by 7.

I am sorry I am not understanding, or that I cannot copy the formulas you have given me correctly.
Brown

 

[Dan Waterloo]

Stop trying to use your formula. Instead, copy my formula

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

into cells A1, B1, and C1 (the exact same formula each time) and tell me what results you see.

Have you read up about the OFFSET function yet? Do you understand that it returns a range?

 

[Brown]

I have not had much time to read about that but I will do it, and hopefully understand it.

At this point I am jjust learning fro you guys.
I was going through the data retreived from the database and every range is not 7, there are a few 6s in there which will throw off what I was trying to do.
Sorry for the time we have seemingly wasted today.
My appologies
Brown

 

[Dan Waterloo]

Of course.