Dynamic bins for mulitple data sets

Stanley99

New Member
Joined
May 18, 2017
Messages
2
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
If you can show an example of your data and the expected result, it would be a great help!!!
 

Stanley99

New Member
Joined
May 18, 2017
Messages
2
Hi Mick,

Thanks for the prompt reply.

What I have is this.
Bin#RangeDist.
Bin 1771
Bin 260
Bin 350
Bin 4429
Bin 5352
Bin 6255

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>

What I would like is this

Bin#RangeDist.
Bin 1671
Bin 2329
Bin 31107

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>

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.
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top