0823bars2_8

Another thing that happened to me since 2009 was home ownership: bought a place and put my name on a serious mortgage contract.  As I go about doing my daily business, I often wonder: how much of this home do we actually own, and how much do we still owe??

What came immediately to mind is the fundraiser tracker.  You’ve probably seen something like this in conjunction with your alma mater, local animal shelter, or kid’s day care asking you for money:

0823bars2_0

 

I wanted to create a visualization like that, in Excel, next to my family’s finances.  Like a true engineer, I absolutely hate manually updating anything, so this had to be automatable.  How do I do it?

 

 

Let me spare you of the gruesome details of my experimental process, and just say that you can draw pictures with bar charts.  Wanna draw a circle with an absolutely rectangular bar chart?  Let’s do it visually:

Step 1 – Create a stacked bar chart.  The number of stacks that you need depends… and trust me for now that you need 4 for a circle:

0823bars2_1

Step 2 – Make sure you have enough bars.  100 is always a good starting point in my opinion.

0823bars2_2

Step 3 – Set the gap between bars to 0%.  Now this chart looks like some European country’s flag.  Still quite rectangular:

0823bars2_3

Step 4 – Adjust the height of these 100 bars so they look like this.  Note how some stacks are much taller than others:

0823bars2_4

Step 5 – Resize your chart into a square.  Clean up the colors and update the axes ranges, and…

0823bars2_5

Ta-da!  Here’s the circle!  Don’t like how pixelated it is?  Add more bars!

0823bars2_6

 

The key here is to think of a picture as a rectangle with w-by-h pixels.  Then you can replicate it with w stacked bars each having a maximum height of h.  How many stacks you need depends on how complicated your graph is.  Is it possible in theory to draw a Mickey Mouse?  Sure, but I intend to stick with simpler images that can be abstracted into a few layers of colors.  You’d need to either set up formulas or a lookup table for the height of these stacks on each bar.  For a circle centered on (a,b), remember the formula is (x-a)^2 + (y-b)^2 = 0.

 

Back to my original goal of drawing a target tracking graphic.  I decided to go with a house complete with a chimney, even though our home looks nothing like it.  It involves a handful of vertical, horizontal, and sloped lines:

0823bars2_7

 

The shape of my house, including where the chimney is, is entirely parameter-ized.  So by updating a few cells I can get to alternate versions like these:

0823bars2_8

 

Lastly, I needed to fill it in with the progress tracker, as follows.  Note that my blue bars and white bars within the house are two separate data series, with heights adjusted dynamically to the input value:

0823bars2_9

0823bars2_10

 

Just to illustrate how flexible this setup is, if you don’t like my vertical split, the complete/remaining portions can be divided horizontally as well:

0823bars2_11

 

This way, I’ve got a bar-based graphics that updates automatically.  The only thing left to do is actually paying off that mortgage!

0823bars1_4

Only because my soon-to-be-former coworker Monica asked about this topic, I decided to dust off this blog (finding that login was no easy task!).  It’s hard to believe but the date stamp on the most recent post was… six years ago!  It’s not that I’ve forgotten about this blog, but I’m borderline at a point in life where you can’t teach an old dog new tricks.  And when I don’t learn new tricks, it’s hard to blog about them.

Well, I did learn one thing more recently than 2009: bar charts.  Remember when we all first learned about these fundamental expressions of statistics, like, in fourth grade?  Drawing the axes with a pencil and measuring out the height of each bar with a ruler was among the most painful part of any math class that I can remember… and yet, this two-post series is about those.  A steroids-injected version of those boring bar charts.

In its first part, we will examine how to create waterfall charts with Excel’s native capabilities.  A waterfall chart is one where floating bars are used to illustrate the progression of a number.  For example, I used one in my recent guest blog entry on Hungry for Points, to outline how my points & miles balance changed between two points in time:

0823bars1_4
Added some, subtracted some, and a new balance!

 

On the surface, it seems impossible to draw floating bars with Excel’s charting tool.  That may be true, but the trick is to craft an optical illusion with white bars.  The same idea can be applied to both overlapping clustered bars and stacked bars.  Let’s see how the former works:

Step 1 – Determine the step-wise cumulative value, and the increment/decrement amount:

0823bars1_0

Step 2 – Create a clustered bar chart where each step has two bars: for increments, bar 1 should be the previous cumulative amount and bar 2 should be the new cumulative amount; for decrements, it’s the opposite.  For the first and last steps representing the balances, bar 1 should be 0:

0823bars1_1
“Bar 1” is orange here.

Step 3 – Make sure bar 1 is in front and set the overlap to 100%:

0823bars1_2

Step 4 – Fill bar 1 white, or whatever your background color is:

0823bars1_5

Step 5 – Color the individual bars:

0823bars1_3

Step 6 – Add data labels to explain your chart.  These can easily be formulaic and linked to your numbers:

0823bars1_4

 

Voila! This may look exotic, but it really isn’t!

Stay tuned for the next article in this series where we take this technique to the next level!

 

For those of you in the business of peer reviewing other people’s data entries, the ability to minimize tedious checks is essential.  Today we’ll look at how =SUM(ABS(…)) is a convenient tool to reduce a simple 3-step process to a simpler 1-step process.

Your intern has just copied a large table of numbers from Worksheet1 to Worksheet2, and you want to make sure nothing went wrong in the process.  In best practice, you’d want to check not only the numbers but also the relative position of the numbers… did the intern switch some rows or columns around?  A good system of lookup formulas should be employed to ensure the integrity of this data transfer.  However, for now let’s just focus on the most basic case where all we care is that the two tables match identically.

It’s a huge table of 100 rows and 20 columns, so you really don’t want to look at each number with your bare eyes.  A typical way to check is to set up a third table of the same dimentions, and subtract the corresponding cells in the two tables:

(Worksheet3!A1) =Worksheet1!A1-Worksheet2!A1
(Worksheet3!A2) =Worksheet1!A2-Worksheet2!A2
….

After that, a quick glance for nonzero values will help you find places where discrepancies exist.  This is not difficult or time consuming by any means, but you still have to (1) type in a formula, (2) copy formula to the correct range, and (3) check for nonzero values.  Instead, try this array formula:

{=SUM(ABS(Worksheet1!A1:T100-Worksheet2!A1:T100))}     (Don’t forget to press CTRL+ALT+Enter)

This formula does an element-wise subtraction of the second table from the first table, and totals the absolute differences into a single number.  If the result of this formula is nonzero, then we’ve got a discrepancy problem worth further investigation – otherwise the job is done!  The ABS() function here serves an important role at making sure positive discrepancies don’t happen to offset negative discrepancies.

This article demonstrates a simple VBA method to utilize the Goal Seek tool automatically, similar to a native Excel formula.

Goal Seek is an Excel built-in tool that uses numeric approximation methods to “back solve” for an input value to a system of formulas when given a desired output value.  If you are not familiar with Goal Seek, many tutorials online can guide you to using this tool.  I hate it, and try to avoid it at all costs… but like everything else that I hate, there come times when I just have to face it like a man.

The problem?  For one, it’s not transparent, and we can’t exactly tell what Excel did in the background.  But more importantly, it’s a pain to apply and to udpate.  While all other formulas can automatically re-calculate upon changes to the input values, Goal Seek requires you to click on the menu to bring up the dialog box, then fill out three input boxes manually.

See the spreadsheet above.  Everything in blue are input values to this dynamic model, which can affect the results.  We want to set E14 to a value specified in D3, so these two cells correspond to the first two parameters of Goal Seek (“Set cell” and “To value“).  As an alternative, we put the difference between the two in F6, and set this cell to 0 (x=y is equivalent to x-y=0).  This approach will slightly simplify the coding later on, and is an easier check for the user.

Download the Sample File

The “Necessary Adjustment” in D6 plays an accomondating role in adjusting each of the values so that the final calculation reaches target.  For Goal Seek, it is the third parameter “By changing cell“.  Initially we have no idea what this adjustment should be, and leave it at 0.  At this time, F6 is showing 0.8%, indicating that the necessary criteria haven’t been fulfilled.

Let’s do a Goal Seek now – set F6 to 0 by changing D6.  If you record this into a macro, say Macro1, you can quickly repeat it by using an assigned shortcut key stroke, or hook it up with a command button for an easy UI.  A lot of people do exactly this as an enhancement to the otherwise awkward model, but it’s not good enough.  Let’s automate it.

Press Ctrl+F11 to bring up the Visual Basic Editor to see the embedding code to Macro1, and you’ll see this line of code:

Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)

Fair enough.  It’s almost in plain English, and shouldn’t take an VBA expert to understand.  The next step is to copy and paste this line out of Macro1, to somewhere more automatically executable.

Browse to “Sheet1” under “Microsoft Excel Objects“.  Either type or use the drop-down box to create the following VBA event:

 Private Sub Worksheet_Calculate()
   
 End Sub

This subroutine will be called every time the “calculate” event is triggered on this worksheet.  Depending on whether you set the calculation to Automatic or Manual in Tools->Options, this happens when any formula-based cell changes value to a new input value.  The idea is to paste the Goal Seek code here so it will be called whenever F6 may change; however, let’s keep it in a separate function for the sake of good programming practice:

Private Sub Worksheet_Calculate()
   CheckGoalSeek
End Sub

Private Sub CheckGoalSeek()
   Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
End Sub

In the Visual Basic editor, it should look like this:

Now give it a try!  Change any of the blue input values and you’ll see Goal Seek working by itself to come up with the new adjustment values.  Awesome!  We’re done now in terms of automating a tedious task.

Note: Always save your work before you test out the macro.  depending on the version of your Excel, the repetitive call to Goal Seek may result in a run-time error.  However, the next part of this code will solve the problem.

Have you noticed how slow this enhancement has made your model?  Maybe not, if you have a relatively new computer or a small workbook like the sample file.  But if you add some temporary debugging code to the function, you’ll notice how many times your function is (unnecessarily) being triggered when you change any of the input values:

Private Sub CheckGoalSeek()
   Range(“F3”).Value = Range(“F3”).Value + 1
   Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
End Sub

Give it another run and, hey:

You just changed one input, and Goal Seek ran close to 2,000 times!  Why???

The subroutine Worksheet_Calculate is triggered every time any function calculates a new value.  There are six cells (in column E as well as cell F6) that depend on the changing cell D6.  As Goal Seek cycles through possible values as the input, all these six cells have to update – and in turn call up more instances of Goal Seek.

Imagine you want to paint a wall red.  Every time you see that the wall isn’t entirely red, you call up a painter to do the job.  As soon as the first painter gets to work, you realize that it’s still not entirely red, so you call up another one.  Even when you’ve got 30 painters to simultaneously working on the wall (if that’s physically possible), there might still be a white spot remaining and you make yet another phone call… chances are, the job would be complete before the last five painters arrive on site.  This algorithm gets the job done, but heck, is super inefficient.  Add a little check to the process and avoid ever calling more than one painter!

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“F6”).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“F3”).Value = Range(“F3”).Value + 1
      Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
      isWorking = False
   End If
End Sub

Adding in the static variable helps flagging whether the Goal Seek function has been employed.  The first time this function is called, it raises the flag and will not release it until the full iteration of Goal Seek completes.  During this time, this subroutine would still be called 2,000 instances, but it would simply bypass all the code and exit.  Try again and F3 increments only once per change of input values, indicating that the Goal Seek isn’t running wild to waste your computer’s resources.  That’s what we wanted, and now we can remove that debugging code.

The first part of the If statement checks whether the solution already exists – i.e. if Worksheet_Calculate is being triggered by some formula updates that are completely irrelevant to this system.  If so, there’s no need to call Goal Seek at all.

Here’s another catch: if you want a robust model, you’ve gotta consider any potential for error.  How about an invalid input value?  Keeping the target overall raise at 5% and changing the max raise to 0% is for sure an impossible scenario, and Goal Seek goes crazy as follows:

We realize that wasn’t very smart and change the max back to 6%… and geez it doesn’t work!  The adjustment factor had been thrown too far off anything reasonable, and now Goal Seek doesn’t know what to do with such an awkward initial guess.  Our model is stuck, and that ain’t cool.  A quick way to fix this is to reset the initial guess every time we call Goal Seek:

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“F6”).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“D6”).Value = 0
      Range(“F6”).GoalSeek Goal:=0, ChangingCell:=Range(“D6”)
      isWorking = False
   End If
End Sub

It’s a slight waste of resources if educated and reasonable input values can be expected at all times, but definitely a worthy investment if you want to prevent ever getting “stuck”.

Lastly, it’s dangerous to use absolute cell references in macros.  If columns or rows are added or removed, the key cells would have changed locations (from D6 to D7, for example) and the macro will get confused!  The best practice is to name the cells and refer to them by name, like the following:

Private Sub CheckGoalSeek()
   Static isWorking As Boolean

   If Round(Range(“GoalSeekCell“).Value, 6) <> 0 And Not isWorking Then
      isWorking = True
      Range(“ByChangingCell“).Value = 0
      Range(“GoalSeekCell“).GoalSeek Goal:=0, ChangingCell:=.Range(“ByChangingCell“)
      isWorking = False
   End If
End Sub

Now we have a robust macro that automatically updates Goal Seek!  Enjoy!

Download the Sample File

 

With the debates heating up more than ever, we step closer to the General Election each day.  The question of the day is – will you vote?

I love democracy.  I’m also a statistician.  Mixing those two and you get the following conclusion: allowing every single citizen to vote is an unnecessary waste of the nation’s resources, which is therefore, unpatriotic.

Let’s make some simplifying assumptions regarding the election process:

  • Popular vote is what determines the outcome – let’s assume no electoral college here.  It’s how election should be done, anyway.
  • There are only two candidates, and every voter will vote for one or the other.  This is almost true in the United States, with less than 1% of the popular votes going toward a non-Republican non-Democrat in 2004.

Now, let’s see the following scenario.  121 million citizens voted in the 2004 presidential election and mattered (no offense to those voting for Nader, Badnarik, etc… but they really didn’t count for much).  51.2% of them voted for Bush, who continued to become our president for the next four years.  The remaining 48.8%, of course, wishfully voted for Kerry.  Without going far into the statistical details, we know that if we drew a random sample of 2.6 million voters, we can be 99.9% confident that their voting outcome will be within 0.1% of the entire country’s voting results.

Download the Sample File

I made a simple Sample Size Calculator based on the formulas from this website.  It takes four parameters regarding the population size, estimate of the result, and the required confidence level, and gives you the necessary sample size.  The noteworthy formula used is the inverse normal function:

=NORMINV(probability, mean, standard_dev)

It gives you the value below which the distribution is expected p% of the time.  Use 0 for mean and 1 for standard deviation to get the normalized z value.  Do take caution to adjust the two-tailed confidence level to one-tailed.

The variables and formulas for this problem include:

N = size of the population (number of eligible voters)
p = expected % outcome (% of the population voting for Democrat or Republican); assume 50% for the “worse case” scenario
c = confidence interval expressed as a %
CL = confidence level, as a %
Z = NORMINV(1-(1-CL)/2, 0, 1) = the number of standard deviations that we are CL% confident the result is within
ss = (Z² * (p) * (1-p)) / c² = the sample size needed for an infinite population
ssf = ss / (1 + (ss-1) / N) = the sample size needed for a finite population with size N

Another thing to point out is that the statistical significance heavily depends on the expected outcome p.  The closer p is to the mid-point (50%), the less confidence we have and the larger the sample size we require.  As historically observed, the U.S. voters are split fairly evenly down the middle.  However, that’s frequently not the case when you examine voting results by state or county.  When a locality is as heavily as 80% leaning toward red or blue, we’d only need a very small sample of voters to be very confident about the results.

If you aren’t familiar with the concept of confidence intervals, this article probably isn’t making much sense to you.  Otherwise, it’s proving a practical political point – in an age where everything needs to go green and at a time when everyone is on a tight budget, let’s not waste all the applications, ballots, and time by allowing every citizen to vote!  Instead, we should randomly select 2.2% of the eligible voters in the country and rest assured that their votes are nearly 100% representative of ALL Americans!

 Download the Sample File

 

Monday, September 29, 2008 was an ugly day.  How ugly do you ask?  The Dow Jones Industrial Average (DJIA) plunged nearly 800 points with violent moves all day as the House rejected the bailout plan.  Ouch babe.  This one will unfortunately be one that will be remembered.

Yahoo Finance provides historical data for Equities and Indices such as the DJIA.  What do we do with a bunch of DJIA closing and their associated dates?  Hrmm… graph anyone?

Download the Sample File

This graph is fairly easy to create but it does not tell us much.  After all, I think it is pretty obvious that the US economy has grown since 1928…  I am thinking that it would be a really nice & spiffy if we were able to create an interactive version of this graph.

Download the Sample File

Remember the scrollbars from when I purchased a vehicle?  Yeah, let’s use them.  Let’s also use another cool feature, “Name Manager“.  You can find the Name Manager within the Formula Tab within Excel 2007- it also exists in Excel 2003.

Quick explanation for Excel Names.  For many financial related spreadsheets, the concept of interest rate is used extensively.  Lets say we designate cell B1 as our interest rate. To make our spreadsheet formulas easier to read, let’s Name cell B1, iR.  To do this we have two main options, either go into the Name Manager and create the variable iR (=B1).  Or simply, edit the cell name next to the formula bar.

Now referring to interest rate is easy, instead of =B1, we can utilize =iR.  The main advantage of this approach is that it is cleaner and easier to understandNames can also be dynamic.  As you can see, I have defined three complex names with complex formulas.

Names can also be referenced as series within Graphs.  Once we Select Data > Edit Series, we are able to refer to the Names previously defined.

So here is ultimately how the Excel spreadsheet works.

  1. Scrollbars are created which drive our starting date and zoom features.
  2. Names are created which create arrays based on the scrollbar values.
  3. Graph Series refer to Names.
  4. Graphs will update based on real time changes in the scrollbar!

So with that, let’s analyze a one year return.  I see a sea of red.  Ouch.

Download the Sample File

Please, let’s hope for an Economic recovery soon!

Hours after Ron bought his new favorite car, he applied at the dealership to become a salesperson.  “I built a great car selection tool, now I can use it to make good business.”

But he knows something was not quite enough – a good interactive user interface should further remove the user from the raw data.  And he is right.  As his experienced dealer superior recalls, if you give too many detailed choices to the average customers, they will get too stressed out and freak out.

So Ron decides to build a new UI – one that allows the user to select among the car options and displays the specs (and price) for the car of interest, one at a time.

Download the Sample File

First, he makes a list of cars currently available at the dealership.  With ActiveX Control’s Option Button (Alt L > I > O), the basic selector is created as follows:

In this case, like before, he links each of the Option Buttons to a cell to the right of the car name.  Note that this could be a tedious process once the list gets long.  Consider using VBA to automate the task.

To develop a good UI, it’s important to have an interactive way to distinguish the selection.  Note how your browser changes the color of a link that you click on?  Ron decides to first format this entire table to the de-selected look.  The column of TRUE/FALSE values might confuse the customer, so he hides it by changing the font color to white.  Note that the graying out of fonts could add contrast to enhance your selection, but it’s a matter of personal style.

Now, he adds conditional formatting to the table.  In this case, one set of rules is applied to each of the three columns.  Excel 2007 gives you this really neat Rules Manager for conditional formats, but the same effect can be done in earlier versions of Excel.

This is what the result looks like.  When the customer clicks on a car of desire, the entire selection lights up!

Lastly, he uses a combination of lookup functions to create a detailed table of specs to display on the right:

Download the Sample File

With such an interactive UI, obviously, Ron’s car dealer business is taking off!


 

Not too long ago, I decided it was time to evolve my mode of transportation.  Sure, I had the tools of the trade- shoes, bicycle, subway pass.  But I desired something more, something greater than myself which would weigh over 2 tons.  Yes, my friends- I was looking for a new car.

Being a car buyer is quite stressful especially if you have limited experience.  Although some of you may believe the process of purchasing a vehicle is an artistic notion, I decided I wanted to consider just a few factors: price, engine, miles per gallon, passengers, automatic/manual transmission.

Research is very important for consumers.  The first item that I wanted to determine in my new quest for a new mode of transportation is, what are the current hot cars?  Thanks to my friends at Yahoo Auto, I was able to determine 8 cars that I wanted to analyze.  A few tweaks here and there and I was able to determine basic information which I would need for my analysis.

Great.  Since I want to use Excel for the basis of this analysis, I have decided to build a nifty graphical user interface (GUI).  OK- great.  So what does this mean?  For the model intensive folks, this means no more lame-O blue cells (or whatever color you firm uses) to represent inputs or random highlighted cells where you “input”.  No, sir.  We want to build something that looks prettier and functions more intuitive.  That’s why I have decided to add some fancier items such as scrollbar, checkbox, drop down and radio buttons.  Something like this…

Before I begin, a few little notes:

  • This model contains basic calculations which will not be discussed in this post.
  • Conditional formatting strikes again!  (Remember, use this technique to quickly troubleshoot + dynamically highlight cells instantly)
  • I have officially began my migration to Office 2007!  (the file is saved as 2003 XLS format).

As many of you know, Excel 2007 has a pretty fancy item called the Ribbon.  We are looking for the Developer tab within the ribbon.  Don’t see it there?  By default, many of you may not have this option available.

  • Office Button > Excel Options > Popular > check “Show Developer tab in the Ribbon”.  (Alt F > I > Alt D)

Now that we have our Developer tab sitting nicely on our Ribbon.  Let’s discuss why we added this tab.

  • Now let’s look for the ActiveX controls.  Developer > Insert (Alt L > I).  Here are some pretty nifty inputs we can utilize to make the GUI that we so desired.
  • Each of these ActiveX controls has their own individual properties including Appearance + Characteristics.  There are tons of options, so please take the time to explore each individual option.
    • Before you can modify the properites of each ActiveX control, you must first enter “Design Mode”.  Developer > Design Mode (Alt I > DM).  To view each ActiveX control’s property simply choose an existing item and choose Properties within the Developer tab.
    • Of all the options, there is one which is VITAL.  It is known as LinkedCell.  This is the link between the ActiveX control and Excel.  Make sure you set this property to refer to the Excel Spreadsheet. 
    • There are other useful properties as well, but I will let all of you dig into the spreadsheet to figure them out! I’ll update the post if anything is confusing.

At the very end, we are able to use the GUI + the settings.  Voila.  Here are our filtered results!

_
In case you are wondering- the blue cells mean that there is a match in criteria.  The number of smileys designate the number of total matches.  So while you are at it- download the Excel spreadsheet and like a fancy car… take it out for a spin.  Vroom Vrrrrroom indeed.
_
~ FIN~
_
_
PS.  You might be wondering what car I purchased.  Yes, indeed …1998 Red Toyota Camry.

I’m dedicating this post to the first person to compliment on the Excel Workbook blog…

Some time has passed since Ron‘s Beijing Olympics post.  It’s now 2020 and Laser Tag has become an official Olympics sport.  The globally famed Laser Tag champion Wang Y. blew her way through the games with amazingly acrobatic shooting, docking, and sniping skills.  After the thrilling final round where all athletes gave their 101%, you as the game admin recorded the scores as follows:

Download the Sample File

Having studied Ron‘s medal comparison model for the past three Olympics, you now have full appreciation for the neat user interfaces that Excel allows you to create.  Now you want to use that knowledge to create an entirely new and personalized score reporting system.  Here’s what you have in mind:

  • Scores will be presented in a bar graph
  • Each participating athlete will receive a personalized score card
  • Each athlete will be listed first on her score card with name spelled out on the graph, in honor of her excellent sportsmanship
  • All the other athletes will be listed in alphabetic order by country code; their names will not be shown

For example, the score card for China’s Wang Y. should look like this – her name, country, and score are listed on top, followed by the other countries in order: Australia, Brazil, (skipping China), Germany, and so on.

While this task could be done fairly quickly by hand, it could get very tedious.  You are a busy game admin after all, and don’t have the time to personalize 10 score cards manually.  You already know how to locate the chosen athlete, but how do you re-order the remaining list?

The INDEX() function comes in handy.  It returns the value of an element in a table or an array, selected by the row and column number indexes.  It often complements the MATCH() function, and can be considered as an inverse function to MATCH().  Note how it’s accomplishing the same task as the OFFSET() function used for Beijing Olympics.  Now you write:

=INDEX(
          [score array],
          CurrentItemPosition – (CurrentItemPosition<=ChosenItemPosition)*1
          )

The second argument determines whether to shift the reference by one row.  When China is selected (position 3), the 1st and 2nd countries need to be shfited into positions 2 and 3, but all other countries in the list will stay put for positions > 3.

Now you have a neat user interface that generates a unique score card as easily as a drop-down menu selection.  Many people resort to writing macros for this type of tasks, but they can more often than not be done with a relatively simple set of formulas.

The following is the score card for the USA athlete, who won silver.

Congratulations to all!

Download the Sample File

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

« Older entries