Array to Group Values

[AndrewKent]

Hi there,

I'm a bit stuck trying to come up with a formula.

I have a column with 20K+ values in it, ranging from +70 to -70. I basically want to put a column next to this and group the value ranges into 10 bandings, or "buckets" if you like.

So in this example, I want to have 10 buckets of 2K values each, ranging from -70 to +70.

I need this to be flexible enough so regardless of the volume of values in the range or the value ranges themselves, it should always dived the total amount of values into 10 equal buckets by range.

Does this make sense?

Andy

 

[Matty]

I'm struggling to visualise what you're trying to do.

Could you post a small example to illustrate what you want the formula to do?

Matty

 

[mole999]

I'm guessing your ten buckets are

70
56
42
28
14
0
-14
-28
-42
-56
-70

but who can you be sure you will have 2000 per bucket ?

 

[AndrewKent]

No,

I'm not looking to have an equal range between each bucket, but for the buckets to be determined by the fact that there will be 10% of the total in each.

 

[Rasm]

Not sure what Gap you want - but lets say you want a Gap of one - that would mean 70+70+1 buckets - now you do a Histogram distribution calculation with a 141 buckets (granted they are now evenly spaced) - but now you have an array of 141 frequencies. Now you sum up the value from each frequency until it reaches the closest to total count (all frequencies summed up)/10 (in this case 2K obvs per bucket)- that will be bucket one. Now you compute bucket two and so on.

If this makes sense - I can send you code to compute the array frequency with 141 bins or buckets - Just want to confirm if this concept will work.

The Histogram calculation is not charted - it is simply to devide the data into BinLabels - in this case a BinLabel of one. So let me know if all your values are whole numbers - meaning the Gap.

 

[Rasm]

PS - If your gap is 0.1 then the number Bins will be 141 * 10 - and so on. But you do realize that the Gap will determine how close each buckets gets to 10% of total count - the buckets will not be perfect in terms of frequency - i.e what if you have 2021 observations that equals 56 - how do you want to deal with that - or will that never happen.

 

[xenou]

Seems like all you need to do is sort the data in order and set the "bucket" boundary on each (n/10) value, where n is the number of items in the list.

It's not clear what you want to do with this. Do you want a histogram? A count? An average of values in each bucket? 10 lists? One ordered list?

 

[Rasm]

Xenou - you are so right - geeze - did I mange to complicate this or what - - thanks for sorting me out - lol

 

[AndrewKent]

Hi guys, thanks for your help. I managed this by using a combination of the PERCENTILE function and IFs for >= and <.

It seems to work really well.

Cheers,

Andy