Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel countif based on size of numbers in one column in Access
I have 230.000 lines in MS Acces file and would like to count numbers in one
column which are in certain range. Number can vary from -999.999 to +999.999 and need to count number of those in range of -999.999 to -10.000 next will be -9.999 to -1000 then -999 to -100 and so on up to +999.999 If anyone can give hints of how to do this, either using Excel on top of Access or brake the file down to several excel sheets in order to count range there I would be most grateful. Regards Kristjan_Thor |
#2
|
|||
|
|||
I would say, considering the large number of lines of data, that
creating queries in Access would be more appropriate than trying to break this down in Excel. Please respond back if you can rationalize the use of Excel in this particular case. |
#3
|
|||
|
|||
Create a new table (called Bounds, for instance) in Access with three
columns: Range (a text description of your range), Upper and Lower. Put your values for the upper & lower bounds of each range in the table, and a description in the Range column Then you'll need a query, something along the lines of SELECT b.Range, Count(b.Range) AS CountOfRange FROM Bounds AS b INNER JOIN ( SELECT d.DValue, b.Range FROM Bounds AS b, DataTable AS d WHERE ([lower]<=[dvalue] And [upper]=[dvalue]) ) as q ON b.Range = q.Range GROUP BY b.Range; Replace "DataTable" with the name of your table and "dvalue" the name of the column you want to summarize Duke "Kristjan_Thor" wrote: I have 230.000 lines in MS Acces file and would like to count numbers in one column which are in certain range. Number can vary from -999.999 to +999.999 and need to count number of those in range of -999.999 to -10.000 next will be -9.999 to -1000 then -999 to -100 and so on up to +999.999 If anyone can give hints of how to do this, either using Excel on top of Access or brake the file down to several excel sheets in order to count range there I would be most grateful. Regards Kristjan_Thor |
#4
|
|||
|
|||
Even easier query is
SELECT b.Range, count(b.range) as Freq FROM Bounds AS b, DataTable AS d WHERE ([lower]<=[dvalue] And [upper]=[dvalue]) group by b.range again - replace "DataTable" with the name of your table and "dvalue" with the name of the column that contains the data you want to test Duke "Duke Carey" wrote: Create a new table (called Bounds, for instance) in Access with three columns: Range (a text description of your range), Upper and Lower. Put your values for the upper & lower bounds of each range in the table, and a description in the Range column Then you'll need a query, something along the lines of SELECT b.Range, Count(b.Range) AS CountOfRange FROM Bounds AS b INNER JOIN ( SELECT d.DValue, b.Range FROM Bounds AS b, DataTable AS d WHERE ([lower]<=[dvalue] And [upper]=[dvalue]) ) as q ON b.Range = q.Range GROUP BY b.Range; Replace "DataTable" with the name of your table and "dvalue" the name of the column you want to summarize Duke "Kristjan_Thor" wrote: I have 230.000 lines in MS Acces file and would like to count numbers in one column which are in certain range. Number can vary from -999.999 to +999.999 and need to count number of those in range of -999.999 to -10.000 next will be -9.999 to -1000 then -999 to -100 and so on up to +999.999 If anyone can give hints of how to do this, either using Excel on top of Access or brake the file down to several excel sheets in order to count range there I would be most grateful. Regards Kristjan_Thor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) | |||
A4 size Excel 2000 files wont fit | Excel Discussion (Misc queries) | |||
Columns in Excel are numbers instead of letters, how do I change . | Excel Discussion (Misc queries) | |||
Fonts get size 1 and can't be changed after a procedure - even by excel GUI!! | Charts and Charting in Excel | |||
Excel should be able to compute the MOD of large numbers. | Excel Worksheet Functions |