Dynamic bins for mulitple data sets

[Stanley99]

Hi Everyone,

I'm not that technical and I'm hoping someone wouldn't mind helping.

I have multiple data sets which vary both in range (lower and upper bounds?). Each data set will also change for each new scenario, for example on any given date Set 1 might have a range of -4 to +4 and the next day -2 to +5. In addition, there are many data sets, each with significantly differing ranges e.g. -4 to +3, 1495 to 1520.

Within each range I am sorting the data into bins, however, I might have empty bins. For example say I have 20 bins, there might only be data in bins 2,3,7,8,9,12,13,11,16,17.

What I would like to do is:

1) For each data set, be able to group the data into consecutive bins. The number of bins could be determined by the range of the data or it could be set manually.
2) For each new scenario (say date) dynamically reset the bins (number of bins and range of bins) to achieve 1) above.

I don't need any histograms.

Hope that makes sense.

I'm not VBA standard, just reasonable with Excel.

Any help greatly appreciated.

 

[MickG]

If you can show an example of your data and the expected result, it would be a great help!!!

 

[Stanley99]

Hi Mick,

Thanks for the prompt reply.

What I have is this.
[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="class: xl119, width: 64"]Bin#[/TD]
[TD="class: xl119, width: 64"]Range[/TD]
[TD="class: xl119, width: 64"]Dist.[/TD]
[/TR]
[TR]
[TD]Bin 1[/TD]
[TD="class: xl146"]7[/TD]
[TD="class: xl137"]71[/TD]
[/TR]
[TR]
[TD]Bin 2[/TD]
[TD="class: xl146"]6[/TD]
[TD="class: xl137"]0[/TD]
[/TR]
[TR]
[TD]Bin 3[/TD]
[TD="class: xl146"]5[/TD]
[TD="class: xl137"]0[/TD]
[/TR]
[TR]
[TD]Bin 4[/TD]
[TD="class: xl146"]4[/TD]
[TD="class: xl137"]29[/TD]
[/TR]
[TR]
[TD]Bin 5[/TD]
[TD="class: xl146"]3[/TD]
[TD="class: xl137"]52[/TD]
[/TR]
[TR]
[TD]Bin 6[/TD]
[TD="class: xl146"]2[/TD]
[TD="class: xl137"]55[/TD]
[/TR]
</tbody>[/TABLE]

What I would like is this

[TABLE="width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="class: xl119, width: 64"]Bin#[/TD]
[TD="class: xl119, width: 64"]Range[/TD]
[TD="class: xl119, width: 64"]Dist.[/TD]
[/TR]
[TR]
[TD]Bin 1[/TD]
[TD="class: xl102"]6[/TD]
[TD="align: right"]71[/TD]
[/TR]
[TR]
[TD]Bin 2[/TD]
[TD="class: xl102"]3[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]Bin 3[/TD]
[TD="class: xl102"]1[/TD]
[TD="align: right"]107
[/TD]
[/TR]
</tbody>[/TABLE]

Ideally I'm trying to find a way of determining the appropriate number of bins required in order for there to be no empty bins. Because I need to do this for multiple data sets, each with varying data, I can't apply a common rule to have, let's say, 5 bins. Each data set may need a different number of bins in order to achieve consecutively populated bins.