Beijing Olympics

The Beijing Olympics games is all about the shimmer, shine and glamor: the Olympic Medals. If I were interested in comparing the number of Olympic Medals by country using Excel, how would I do this?

Download the Sample File

To make tasks such as this a bit more dynamic, I often like to create user interfaces. When creating an interface, the general rule of thumb is that it should be simple, allow the user to select his/her preferences and produce accurate results (both detailed and executive summaries).

Download the Sample File

To create this user interface, I will use the Excel functions MATCH and OFFSET to fetch data. This data is the basis to populate the graph and perform the simple difference calculation.

MATCH

  • =MATCH(lookup_value, lookup_array,[match_type])
  • MATCH returns the index / element position for the specified lookup value based on an associated array. The optional match type argument is FALSE if a lookup value must match an element within the array. TRUE or blank (default) in combination with a sorted array will return an index based on the range of values.
  • Ex, lookup_array = {1, 3, 5, 7, 9}.
    • MATCH(5, lookup_array, FALSE) = 3 because 5 is the 3rd element within the lookup_array.
    • MATCH(4, lookup_array, FALSE) = #N/A.  Since match_type = FALSE an exact match must occur which means that the lookup value must exist within the lookup array!
    • MATCH(4, lookup_array, TRUE) = 2. lookup_array must be sorted if match type is TRUE.
      • lookup values from 3 to <5 will return an index of 2,
      • lookup values from 5 to <7 will return an index of 3, etc, etc.

OFFSET

  • =OFFSET(reference, rows, columns, [height], [width])
  • Think of OFFSET in terms of Excel key navigation. Excel defaults at cell A1.
    • Want to get to cell B1? It is easy- simply press the right arrow or in other words, shift forward one column.
    • Cell A2? Shift forward one row / press the down arrow.
    • In Excel, OFFSET(A1, 0, 1) = B1 and OFFSET(A1, 1, 0) = A2. Therefore, OFFSET(A1, 2, 1) = B3.
  • OFFSET can also retrieve array / matrices of information when using the optional height and width arguments.

How does it work?  MATCH allows us to determine the position of each country and the medal type.  The MATCH results filter to OFFSET where Excel can retrieve the appropriate data from our table.

We have created this exhibit with just a few tools: Excel’s built in graph formats, conditional formatting, and the MATCH and OFFSET Excel functions. This type of model can have its various uses whether it is to create an exhibit to quickly compare financial ratios of equities, varying benefit plan design costs, and even Olympic medal counts.

Note: OFFSET and MATCH are not the only functions we can utilize to create the output, some of you may prefer MATCH with either VLOOKUP or HLOOKUP.

Download the Sample File

  1. Peter’s avatar

    The next task is probably… how do you download the Olympics stats automatically!

    I suppose this is a very relevant topic 🙂

Comments are now closed.