Hello everyone! I've found a lot of help for this issue over several other threads, but I'm now stuck at pulling it all together.
I've got a data set of widths and depths. I'm trying to maximize the item that will fit in each box, so I want to know the most common box, second most common box,...,Nth most common box. If you take a look at the "Summary" sheet, you'll see where I'm compiling this data and the formulas I've used.
The more important dimension is the width. So:
H20 calculates the modal width. I20 calculates the most frequently occurring depth with that width.
H21 currently calculates the second most frequent width, and I could set I21 as the corresponding depth.
The problem with this method, is that it ignores the possibility that the second most frequent BOX might share the modal width and simply have a different depth. For example, the possibility that the most common dimension was 45x55 and the second most common dimension was 45x50. In my set this isn't the issue, but as my dimensions change I think it might be.
In essence, I need a formula that takes my original arrays, removes what I've identified as the modal width and depth elements from each, and starts the process over again; finding the modal width of this subset and its corresponding depth...that is scaleable.
Thanks!
Size Matrix.xlsx
Bookmarks