Help with a function

  • Thread starter Thread starter Elliott Alterman
  • Start date Start date
E

Elliott Alterman

I have a table that has a column of values (range from 50 to 100000). I would
like to summarize the range of values in that column. For example, I would like
to know how many are between 50 and 100, between 51 and 200, etc.

The values for the ranges (e.g., 50 to 100, 101 to 200, etc.) will have to be
changed frequently so I don't want them to be embedded in the function - I'd
rather just type them in specific cells that the functions can refer to:

For example

range 100 300 850
counts 4 5 3

In the above, I'd like to know how many cells have values less than 100, from
101 to 300, from 301 to 850. The values 100, 300, 850, etc are the ones that
change frequently.

Suggestions are appreciated.

"Z"
 
range 100       300     850    
counts  4       5       3

In the above, I'd like to  know how many cells have values less than 100, from
101 to 300, from 301 to 850. The values 100, 300, 850, etc are the ones that
change frequently.
Click to expand...

Suppose your data are in A1:A10000. And suppose your range limits
(100 etc) are in C1, D1 etc.

The simplest approach is to make a special case of the first range
limit:

=COUNTIF(A1:A10000,"<=" & C1)

Note that that counts cells less than __or_equal__ to 100, which I
think is what you meant.

Then put of one of the following formulas into D1 and copy across:

XL2003 and earlier:

=SUMPRODUCT((C1<$A$1:$A$10000)*($A$1:$A$10000<=D1))

The multiplication (*) acts like AND in this context. We cannot use
the AND function in this context.

XL2007 and later:

=COUNTIFS($A$1:$A$10000,">" & C1,$A$1:$A$10000,"<=" & D1)

Note: You could also use the SUMPRODUCT formula in XL2007 and later.
But COUNTIFS should be more efficient.
 
Elliott Alterman said:
I have a table that has a column of values (range from 50 to 100000). I
would like to summarize the range of values in that column. For example, I
would like to know how many are between 50 and 100, between 51 and 200,
etc.

The values for the ranges (e.g., 50 to 100, 101 to 200, etc.) will have to
be changed frequently so I don't want them to be embedded in the
function - I'd rather just type them in specific cells that the functions
can refer to:

For example

range 100 300 850 counts 4 5 3

In the above, I'd like to know how many cells have values less than 100,
from 101 to 300, from 301 to 850. The values 100, 300, 850, etc are the
ones that change frequently.

Suggestions are appreciated.

"Z"
Click to expand...

this is undoubtedly not the best way, but it's the first thing that came to
mind
so while you wait for an expert to give you a "good" solution, try this
open a blank worksheet
paste the code to ThisWorkbook module
first run Test() sub to set up the page with some sample values and run the
first count

afterwards, you can change the values in cells C1, D1, E1 like your example
above with 100 300 850 etc
then run the sub RunCount()

i think this is roughly what you were asking for
hth
mark

<code>
Option Explicit
Sub Test()
FillSampleData
RunCount
End Sub
Sub FillSampleData()
Dim rngSource As Range
Dim wks As Worksheet
Set wks = ThisWorkbook.ActiveSheet
Set rngSource = wks.Range("A1:A33")
Dim iVal As Integer
iVal = 50

Dim oCell As Range
'fill sample data
For Each oCell In rngSource.Cells
oCell.Value = iVal
iVal = iVal + 10
Next

'sample number ranges
With wks
.Range("C1").Value = "100"
.Range("D1").Value = "200"
.Range("E1").Value = "300"
End With

End Sub

Sub RunCount()
Dim rngSource As Range
Dim wks As Worksheet
Dim oCell As Range

Set wks = ThisWorkbook.ActiveSheet
Set rngSource = wks.Range("A1:A33")


'clear last count
With wks
.Range("C2").Value = ""
.Range("D2").Value = ""
.Range("E2").Value = ""

'parse list
For Each oCell In rngSource.Cells
Select Case oCell.Value
Case Is <= .Range("C1").Value
With .Range("C2")
.Value = .Value + 1
End With
Case Is <= .Range("D1").Value
With .Range("D2")
.Value = .Value + 1
End With
Case Is <= .Range("E1").Value
With .Range("E2")
.Value = .Value + 1
End With

End Select
Next
End With

End Sub
 
Thanks for the input!

Elliott


this is undoubtedly not the best way, but it's the first thing that came to
mind
so while you wait for an expert to give you a "good" solution, try this
open a blank worksheet
paste the code to ThisWorkbook module
first run Test() sub to set up the page with some sample values and run the
first count

afterwards, you can change the values in cells C1, D1, E1 like your example
above with 100 300 850 etc
then run the sub RunCount()

i think this is roughly what you were asking for
hth
mark

<code>
Option Explicit
Sub Test()
FillSampleData
RunCount
End Sub
Sub FillSampleData()
Dim rngSource As Range
Dim wks As Worksheet
Set wks = ThisWorkbook.ActiveSheet
Set rngSource = wks.Range("A1:A33")
Dim iVal As Integer
iVal = 50

Dim oCell As Range
'fill sample data
For Each oCell In rngSource.Cells
oCell.Value = iVal
iVal = iVal + 10
Next

'sample number ranges
With wks
.Range("C1").Value = "100"
.Range("D1").Value = "200"
.Range("E1").Value = "300"
End With

End Sub

Sub RunCount()
Dim rngSource As Range
Dim wks As Worksheet
Dim oCell As Range

Set wks = ThisWorkbook.ActiveSheet
Set rngSource = wks.Range("A1:A33")


'clear last count
With wks
.Range("C2").Value = ""
.Range("D2").Value = ""
.Range("E2").Value = ""

'parse list
For Each oCell In rngSource.Cells
Select Case oCell.Value
Case Is<= .Range("C1").Value
With .Range("C2")
.Value = .Value + 1
End With
Case Is<= .Range("D1").Value
With .Range("D2")
.Value = .Value + 1
End With
Case Is<= .Range("E1").Value
With .Range("E2")
.Value = .Value + 1
End With

End Select
Next
End With

End Sub
Click to expand...
 
I tried your suggestion and it works beautifully. A very good alternative.

Thanks!

Elliott
 
Back
Top