Home |
Search |
Today's Posts |
#1
|
|||
|
|||
dynamic defined ranges
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 |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dependent drop down boxes and dynamic ranges | Excel Worksheet Functions | |||
Solver and dynamic ranges | Excel Worksheet Functions | |||
Indirect and dynamic ranges | Excel Worksheet Functions | |||
dynamic ranges | Excel Worksheet Functions | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |