1st, 2nd & 3rd most common ranges within a single column of values

Hi. Thanks for stopping by my question. Would you know how I can analyze data to give you the most common range, for a more meaningful analysis? I guess it would require that we define some parameters, but what are those parameters, and would this be a formula or array? Essentially, it would be, conceptually, like a scatterplot, but without the plot itself. I just want the data to be automatically analyzed to output the most common ranges. You probably already got what I'm asking, but here's a quick, unsophisticated example, assume 1 column like this:

3
91
95
96
96.5
89
157
177
175
181
250
299
301
793

So, I'd want the output for the above example to be:

1st most common range of values: 89-96.5
2nd most common range of values: 177-181
3rd most common range of values: 299-301


I'm not sure how/where I'd tell Excel what constitutes a "range", or how this even works.

Thanks for showing me the right direction/formula/array, since I think this might be more meaningful than just looking for 1 value that represents "mode", when often, there is no single number that occurs more than once in a data set and more meaningful data appears in clusters that are probably just ranges. Thanks for helping me out and guiding me down the right path.

Exactly. Those are the key questions I'm basically also asking. In a way, what I'm looking to do is find a mathematical solution to what you and I normally do intuitively when we scan and automatically "make sense of" a scatterplot and summarize, "ok, so basically, values between here & here are important". I'm no mathematician, that's for sure, but I think, to answer these questions, it seems like you'd almost have to analyze each data set (my 1 column) uniquely first, in order to come up with statistical things like "variance", etc ... but I'm not trained in that, so I can't quite think how a statistician would think. So we'd want to find the most common ranges within any data set, where, indeed, the span between clusters or bins or ranges may be highly variable based on the data set. That's all I can think of for now. Hope it helps some. Please let me know if you have additional thoughts. Thanks for your post.

Originally Posted by MrShorty

Mathematics is the native language of the natural world.

MrShorty,

Thanks for your thoughts there re frequency and bins. Yeah, the bins thing is really the key, but I need a mathematical solution (your quote is perfect for this post) to what our brains do intuitively/second nature when we visually scan, say, a scatterplot and are able to quickly come up with a solution. This question kind of feels like a "Brain Games meets Excel" challenge. Thanks for your post and hopefully we can all figure it out. My post to Xor LX has some related thoughts. Feels like someone with a statistics type of background could really lend some valuable insights here for us as well. I'm really interested to learn more and find a solution to this puzzle. Thanks

newdoverman

let me have a looksee, and get back to you. thank you

sourabh, will review your earlier .xlsx and respond shortly, thank you.

sourabh & marvinp, can you explain the .xlsx, and the logic as we go from A thru I (sourabh), or A thru N (marvinp)?

I'd appreciate it. That will help me follow along.

To all, Please don't just post excel files. Please include an explanation of your logic so I can follow along. Thanks a lot.

In the meantime, I'm trying to go thru the 3 .xlsx posted so far, to see if I can make sense of them. Right now I'm looking at MarvinP's since it looks like an updated version of sourabh's.

Mr Shorty, thank you for that. Good. Yeah, it looks like we're making progress right now with sourab & marvinp, and I'm reviewing newdoverman's last post. Have an awesome day, and thanks again.

newdoverman, I see where you're going for sure, thanks! So I can see you've tightened the ranges from 100s down to 20s. But the prob with that, respectfully, is that they're still arbitrary (you make them up), instead of the perhaps preferred approach in marvinp et al, which is (almost) fun 'cuz you can try and walk thru the logic one column at a time (ie helper columns...which is the premie stage I'm at, lol). I'm still awaiting a response to my last question to them (re logic in Column D). newdoverman, I really appreciate you.

sourabh & marvinp,

I've figured out what the "60" is in your .xlsx in $D$1.

It's an Average difference from 1 value to the next (an average of the column C differences).

Since all the bins are ultimately based on Column D, and Column D2:D14 are relative to the "60" value, I'm trying to determine how you got the right Upper & Lower limits of those ranges (Column E & F) based on the yes/no comparison to D$1$. Still reviewing that.

sourabh & marvinp,

I'm presently evaluating D & E ... pretty thoughtful approach. I'll need to think more on it. First, thanks. Second, I think I see an error:

Why is the value "89" from column A missed? Shouldn't that be the first lower limit of the first range, instead of 91, esp since the very first value is only 3?

Thanks for your thoughts.

I'm stepping out for a quick bite. Will check back later. Kind regards and thanks for the help so far...

Marvin,

Thanks for the follow up.

Here's the problem imho. In this more recent CountIF version, you're falling into a similar trap encountered earlier in this thread, which is that you're assigning arbitrary bin cutoffs. That's exactly what I don't want. I want the opposite. I want the bins (ranges) to be automatically detected as shown below. I followed the counts on your previous version (array in M, etc). The primary challenge is not so much the counting, but rather first finding those Lower & Upper limits, not unlike you had previously.

In a sense, the "smart" bins, or ranges, will automatically detect "clusters" of data, and then report that back. Using my initial example from my first post, that would be like:

3
91
95
96
96.5
89
157
177
175
181
250
299
301
793

where I'd want the output for the above example to be:

1st most common range of values: 89-96.5
2nd most common range of values: 177-181
3rd most common range of values: 299-301

Where to go from here...

I think the previous version, which focused on the main challenge, namely determining the ranges, is the best place to continue from. However, since you've already shown a lot of sophistication with that, I'm wondering (dare I ask?) if you or others might be able to make it more sophisticated (more accurate), as follows: It's good but not great in one respect: It's basically saying "as soon as you take a leap that's greater than the average leap, then mark that as the lower limit" (and related logic for upper limit). But, the fault (or area for improvement) is if we can figure out a smarter way (statistically speaking), a logic that's more accurate than "average leap", since, if there's a HUGE gap somewhere in the data, then that gap will ultimately be responsible for missing (not calling) some ranges of values.

Also, a quick side note: I'm learning that thinking in terms of "bins" is the wrong way to go, since we don't want predetermined, arbitrary bins with arbitrary cutoffs since assigning arbitrary cutoffs (funny or not) misses the point completely; rather we want the formulas/arrays to report back the ranges (lower & upper limit) of the actual data "clusters", if you will, that is, where values are most common, using an approach like your initial one. So just wondering if we can improve from "average leap" to something more accurate, and avoid the notion of "bins" altogether, continuing from Frequency with strange Bin Ranges.xlsx, and simply modifying/improving the logic from D thru F. Thanks for your help so far.

How many ranges/buckets/bins are we supposed to come up with? That might help!!

marvin? Thanks for your post. You'll notice that that's already been discussed at length in this thread. Also please don't be angry or pretend to be angry at me. From your eyes, emoji and extra punctuation, it looks like you are bugging out. Please keep focused and professional thanks. Nah, I didn't want "the code". That's weird cuz I never even used that term and I'm not like that; and for anyone reading the whole thread, they can see that's an unfair mischaracterization. I've been very open-minded, inspired, and have been just as interested in the theory and the math as much as any solution. Actually, you're asking me to provide the

ranges/buckets/bins

but that's actually the point of the post is to come up with the ranges, very much like I described in post#24 which is the same as I posted originally. Appointing arbitrary "buckets" or "bins" is like trying to push a square peg through a circular hole, pigeon holing really. And it means you (theoretically) didn't understand the initial post or other posts where I talk about "scatterplots" and "intuitive" and math, etc.

Marvin, what's strange is that you specifically did not use bins in your first submission and had a completely different solution that didn't involve bins in your attachment Frequency with strange Bin Ranges.xlsx, so I thought you already understood what we're trying to do here? I thought I was being very genuine and kind and helpful to others in explaining the .xlsx you submitted...but simply attached but without an explanation or any discussion. I think you misread my entire last post #24 or are acting like you took it personally because your last post was very defensive. You misread or want to be perceived as having misread my previous post before this one.

When you say

I'm starting to get a feeling for the problem but this might be one of those without enough information to give an exact answer.

, please don't speak on behalf of others. Let others make their own decisions and help and come up with their own methods. No, there is enough information. You may just not have the answer or want to say that. This is not a simple black and white question as you apparently already knew from your Frequency with strange Bin Ranges.xlsx, Columns D-F. So you knew all this already. This is quite strange.

I thought you would be more gratious since the xlsx you submitted was a carbon copy of the one initially posted by another user (sourabh), buit just added a count? That part confused me. Who came up with that non-bin logic? Like I stated in my post before yours, or post #24, that seemed to show an understanding of the challenge initially posted, quite clearly I might add. No need to post back if you have additional negative comments, sarcasm, or insults. Those were inappropriate.

In my last paragraph there I provided additional clarity in response to others' (not mine) question about "bins":

I'm learning that thinking in terms of "bins" is the wrong way to go, since we don't want predetermined, arbitrary bins with arbitrary cutoffs since assigning arbitrary cutoffs (funny or not) misses the point completely; rather we want the formulas/arrays to report back the ranges (lower & upper limit) of the actual data "clusters", if you will, that is, where values are most common, using an approach like your initial one. So just wondering if we can improve from "average leap" to something more accurate, and avoid the notion of "bins" altogether, continuing from Frequency with strange Bin Ranges.xlsx, and simply modifying/improving the logic from D thru F. Thanks for your help so far.

For others, post#24, and really all my posts, summarizes the issue well, and solutions are welcomed.

MrShorty,

Your kungfu is strong. First, I apologize because, for some reason, I didn't see your post #20. Second, huge thanks for providing a sense of direction. Ok, it's quite late here and I want to study up on these most recent 2 posts from you when I get back to the office tomorrow morning. I'm looking forward to checking that out for sure. and following up here of course with you.

Have an awesome evening.

Sourabh,

Yes, I was able to figure all that out on my own back on posts #15 & #21. thanks anyway.

And I even summarized it in post #24, which is the current direction of this thread:

It's good but not great in one respect: It's basically saying "as soon as you take a leap that's greater than the average leap, then mark that as the lower limit" (and related logic for upper limit). But, the fault (or area for improvement) is if we can figure out a smarter way (statistically speaking), a logic that's more accurate than "average leap", since, if there's a HUGE gap somewhere in the data, then that gap will ultimately be responsible for missing (not calling) some ranges of values.

You wrote,

Actually there is no specific way to extract ranges from a large array

However, that's actually not true. I think you also may have missed MrShorty's post #27. Damn, I feel like a moderator, lol. Remember, if you're gonna set a fire, stick around.

OK, thanks.

MrShorty, Just a quick note here to let you know I've fallen a little behind, and haven't forgotten, but mostly to express thanks, and let you know I promise to follow up here as soon as I can...thanks/thanks all. We had some wild weather here today with the first snow in decades, perhaps like most of the country & probably world, so had to get out with the fam for scenic photos and that whole thing. I hope you're doing great and sending my respects along.

MrShorty,

Right on! k means cluster analysis would definitely serve as that more sophisticated method and is pretty much what I was getting at. Reading thru that made me feel probably how a caveman felt when he did his first cave drawing, or when he first said "mama"- haha. I like that idea, esp as described:

“In statistics and data mining, k-means clustering is a method of cluster analysis which
aims to partition n observations into k clusters in which each observation belongs to the
cluster with the nearest mean.

That seems like a step up from what I was calling the "average leap". I was really surprised to see how much coding, that Sheldon Neilson (kudos), eg, put into this concept, and the k means cluster analysis really seems like a good answer to my original question in this thread. I like that it runs thru all the iterations (why I originally thought an array might be best) to find these clusters

The k-Means algorithm is an iteration of the following steps until stability is achieved i.e. the cluster assignments of individual records are no longer changing

this quote from the http://www.neilson.co.za/k-means-clu...crosoft-excel/ previously provided. And I also like that data observations belong to the cluster with the nearest mean...which is a variant, albeit, of "average"; only it doesn't compare it to the average of the entire data set, making it a more accurate approach I believe.

Well, I still have to go thru your latest table, and will follow up thereafter for sure. And, this week I'll definitely want to do some more independent research on k means, as you wisely suggested. Thanks for providing the guidance you did thru this clusterf. It's cool if you think about all these posts as individual observations in a data set, and how it all kind of came together (like a cluster) that wasn't necessarily that obvious; just a neat notion I think. Equally cool was that, while I'd mentioned ranges and "scatterplots" early on, your post #20 was first to mention the term clusters, and I hadn't actually seen it (my bad) til after I'd mentioned "clusters" in post#24. This thread has been a pretty cool experience and I learned a lot, thanks to the collective enterprise. I'll follow up as promised. Thanks for your patience in the meantime.

Here's a recent thread that you might find useful:

http://www.excelforum.com/excel-prog...ml#post3998817

Hope this helps.

Pete