Unbinning binned data
Posted by Barry Ward on August 01, 2001 8:08 AM
I have a lot of binned data that I want to perform a number of statistical tests on but it seems the only way is to unbin it somehow............
Any suggestions will be very welcome as I'm on the verge of PC Rage :@
Thanks
Barry
Posted by Mark W. on August 01, 2001 8:14 AM
Provide some sample data.
Posted by Barry on August 01, 2001 8:22 AM
I have length categories and frequencies at length eg
22 1
23 2
24 2
25 8
26 6
27 3
28 7
29 3
30 5
31 2
32 1
33 2
34 2
35 2
36 1
where column 2 is the number of observations at length in column 1
Posted by Mark W. on August 01, 2001 8:33 AM
How many observations are included in your
binned data? I have length categories and frequencies at length eg
Posted by Barry on August 01, 2001 8:42 AM
It varies the problem is I have loads of smaller samples that generally include ~200 observations over a range of about 20 size classes which themselves fall within about 60 size classes eg I may have 200 observations within a category called small ranging from 20 - 40 and another 200 within a category called medium ranging from 30-50 etc
Posted by Mark W. on August 01, 2001 1:11 PM
Okay, here's what I'd do using your previously
supplied sample data... without VBA.
1. With cells A2:B16 containing...
{22,1
;23,2
;24,2
;25,8
;26,6
;27,3
;28,7
;29,3
;30,5
;31,2
;32,1
;33,2
;34,2
;35,2
;36,1}
...Copy cells A2:A16, select cell A17 and Paste.
2. Enter the formula, =OFFSET(A17,-1,1)-1, into
cell B17 and double click the fill handle (the
black square in the lower right-hand corner of the
active cell) to copy down to B31.
3. Calculate the last row where additional copies
of data will be placed using...
('max tally'-1)*'# of bins' + 'last row of orig. data'
...or...
(8-1)*15+16=121
4. Copy cells A17:B31, select cells A32:B121, and
Paste.
5. Sort on column A
6. Apply an AutoFilter on column B using the
customer criteria "is less than or equal to" 0.
7. Delete all rows whose row number is colored
blue.
8. Remove the AutoFilter and Delete column B.
There's your original data!
