The Hermit Hermit's Free Library  Brain Teasers

One in a series of exercises that demonstrate how to accomplish real-world computing tasks using time- and work-saving techniques.

Brain Teaser #15: Analyzing Research Data

Solution

Use MS-Excel

  1. Copy and paste the accompanying data into an Excel worksheet beginning in cell b1
  2. In cell A50 enter the text label "First Mode"
  3. In cell B50 enter the formula: =mode(B1:B49)
    (Finds mode (most commonly-occurring number) in column B)
  4. To find the second most-commonly occurring number:
    1. In cell C1 enter the formula: =IF(C1=B$50,"",C1)
    2. Copy the formula into cells C1 through C49
    3. In cell C50 enter the formula: =mode(C1:C49)
      (Finds second most commonly-occurring number)
    4. Copy Column C's formulas through Column I

Bonus solution:

  1. In cell B51 enter the formula: =abs(count(C1:C49)-count(B1:B49)) (Counts number of Modes in each column)
  2. Copy the formula in B51 through Cell I51