# 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.