+ Reply to Thread
Results 1 to 4 of 4

Calculating The Nth Mode on Two Interrelated Data Sets

  1. #1
    Registered User
    Join Date
    04-16-2014
    Location
    Baltimore
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,679

    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
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-16-2014
    Location
    Baltimore
    MS-Off Ver
    Excel 2010
    Posts
    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. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,679

    Re: Calculating The Nth Mode on Two Interrelated Data Sets

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Identifying the MODE using two sets of data
    By R.Baltimore in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-17-2014, 01:57 PM
  2. Replies: 10
    Last Post: 11-01-2012, 06:23 PM
  3. Calculating Variance Between Two Different Data Sets
    By lee2k60 in forum Excel General
    Replies: 3
    Last Post: 06-19-2012, 10:46 AM
  4. Calculating average months based on specific sets of data
    By Poisson in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2011, 05:26 PM
  5. Calculating the Mode
    By RockyStrong in forum Excel General
    Replies: 1
    Last Post: 04-16-2010, 09:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1