# Calculating The Nth Mode on Two Interrelated Data Sets

1. ## Calculating The Nth Mode on Two Interrelated Data Sets

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

2. ## Re: Calculating The Nth Mode on Two Interrelated Data Sets

You could generate a histogram of sizes:

 A B C D E F 1 Width Depth Helper Count 2 55 70 55|70 2 C2: =A2 & "|" & B2 3 50 70 50|70 2 D2: =COUNTIF(\$C\$2:\$C\$196, C2) 4 50 62 50|62 1 5 50 60 50|60 3 6 50 55 50|55 23 7 50 55 50|55 23 8 50 55 50|55 23 9 50 55 50|55 23 10 50 80 50|80 1 11 55 70 55|70 2 12 50 55 50|55 23 13 50 55 50|55 23 14 50 55 50|55 23 15 50 55 50|55 23 16 50 55 50|55 23 17 50 55 50|55 23 18 45 60 45|60 6 19 50 65 50|65 12 20 50 65 50|65 12 21 50 65 50|65 12 22 50 65 50|65 12 23 50 65 50|65 12

Then copy the formula and replace with values, sort descending by col C, and remove duplicates:

 A B C D 1 Width Depth Helper Count 2 45 55 45|55 43 3 45 65 45|65 40 4 50 55 50|55 23 5 50 65 50|65 12 6 45 64 45|64 8 7 45 58 45|58 7 8 45 60 45|60 6 9 45 57 45|57 5 10 45 59 45|59 4 11 50 60 50|60 3 12 50 64 50|64 3 13 55 70 55|70 2 14 50 70 50|70 2 15 45 63 45|63 2 16 55 55 55|55 2 17 60 55 60|55 2 18 45 62 45|62 2 19 50 75 50|75 2 20 50 62 50|62 1 21 50 80 50|80 1 22 50 67 50|67 1 23 50 63 50|63 1 24 50 61 50|61 1 25 50 59 50|59 1 26 50 57 50|57 1 27 55 51 55|51 1 28 55 65 55|65 1 29 45 78 45|78 1 30 45 77 45|77 1 31 55 45 55|45 1 32 65 55 65|55 1 33 50 54 50|54 1 34 45 73 45|73 1 35 60 65 60|65 1 36 55 58 55|58 1 37 45 70 45|70 1 38 50 72 50|72 1 39 45 79 45|79 1 40 45 67 45|67 1 41 40 55 40|55 1 42 45 72 45|72 1 43 45 61 45|61 1 44 58 55 58|55 1 45 55 48 55|48 1 46 50 53 50|53 1

3. ## Re: Calculating The Nth Mode on Two Interrelated Data Sets

shg - thanks for the help! I hadn't considered a combined column to make counting easier. This will also help in other areas of data manipulation where the count is necessary. Thanks again!

4. ## Re: Calculating The Nth Mode on Two Interrelated Data Sets

You're welcome.

There are currently 1 users browsing this thread. (0 members and 1 guests)