dynamic defined ranges

[Thomas Pike]

Dear ALL,

I have a spreadsheet with a single column of numbers. There are 1000
numbers. I need to perform a count if formula on these numbers if they
are above .5. Now sometimes I need to group these numbers in groups of
10 or 20, or 50.

Right now I am doing this by changing the ranges manually in my COUNTIF
statements. Is there a way that I can select a cell, say L1 that isn't
used and place my offset number there?

So my current range in one summary cell is =COUNTIF(J1:J10,">.5") then
the next summary cell has =COUNTIF(J11:J21,">.5") for groups of 10. Now
if I were to change the value in cell L1 to 20, I would like to see my
statements change to =COUNTIF(J1:J20,">.5"), =COUNTIF(J21:J40,">.5"),
etc.

Any ideas?

T

 

[Harlan Grove]

Thomas Pike wrote...
....

So my current range in one summary cell is =COUNTIF(J1:J10,">.5") then
the next summary cell has =COUNTIF(J11:J21,">.5") for groups of 10. Now
if I were to change the value in cell L1 to 20, I would like to see my
statements change to =COUNTIF(J1:J20,">.5"), =COUNTIF(J21:J40,">.5"),
etc.

If the first formula were in cell X99 and the second in cell X100, you
could try the following formula in cell X99

X99:
=COUNTIF(OFFSET($J$1,(ROWS(X$99:X99)-1)*$L$1,0,$L$1,1),">.5")

then fill X99 down into X100.