-Intermediate

You are currently browsing the archive for the -Intermediate category.

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!

 

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

Lets revisit Summing Element-Wise Minimum once again. To recap:

“Suppose you plan to buy several computer parts from either BestBuy or Circuit City. The two stores have different prices for each product. If you buy from one store exclusively, you won’t get the best deals possible. To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.”

Lets suppose you are not convinced you have truly optimized your hard-earned dollar. Therefore, you decide to add prices for Office Depot, Duane Reade and Walmart.

Download the Sample File

An easy way to solve this problem would be to create a new column which utilizes the MIN() function. But, since you believe in optimization you want it ALL in one single cell. What are your options? I guess you can start creating a crazy array that will be difficult to understand such as the following example which determines the cheapest total price between 3 stores (arrays):

{=SUM(IF([array 1]<[array 2],IF([array 1]<[array 3],[array 1],[array 3]),IF([array 2]<[array 3],[array 2],[array 3])))}

Looks… um… um… right. So outside of adding a column and creating a crazy array formula, what other option is available? Enter Microsoft Visual Basic! It seems that many Excel based users may go pretty far in their careers without ever understanding the super hyper intimidating but useful tool. (Excel 2003 keyboard shortcuts provided)

  • Tools > Macro > Macro… (Alt + F8) and
  • Tools > Macro > Visual Basic Editor (Alt + F11).

So now, wouldn’t it be nice if we could just write a function and refer to a bunch of cells and get exactly what we want? Guess what? With Visual Basic, you can… With the Visual Basic code below, you can add as many stores as you like while solving for the optimal price.  It is important to note that everyone has their own programming style, you can feel a glimpse of my style with the code below.

Download the Sample File

I certainly encourage you to download the sample file because I have provided comments within the Visual Basic Module. That said, I do want to make a few quick notes.

  1. A Visual Basic function (if non-Private) can be called from the Excel Spreadsheet directly. The spreadsheet contains the formula, =minSumItNow([Array 1 … Array N]).
  2. Visual Basic Function differs from Sub because Function must return a value. The value is set by assigning the Function name.
  3. [Array Name].Rows.Count and [Array Name].Columns.Count are very useful. This allows the user to understand the size (vertically and horizontally) of arrays. This is useful because users do like to include a range of cells as an input. (For Each is useful as well and will be discussed in a later post)
  4. Visual Basic might seem like more work to learn, but the rewards are plentiful. Learn Visual Basic to:
    • Impress your colleagues.
    • Work more efficiently.
    • Automate tedious tasks.
    • Unlock the true power of Excel.

Download the Sample File

How do you use an array formula to sum up element-wise minimum values?

Suppose you plan to buy several computer parts from either BestBuy or Circuit City.  The two stores have different prices for each product.  If you buy from one store exclusively, you won’t get the best deals possible.  To optimize the value of your hard-earned dollar, you want to find the least amount of money necessary to buy one of each part.

99.5% of the time, you’d set up a spreadsheet similar to the one shown below.  By using a MIN() function, you create a third column listing the lower price of each item.  Then it’s a no-brainer to add up the column for the total.


Download the Sample File

Occassionally, you may want/need to calculate this number in one single step.  Array formulas came to mind and it seems intuitive to write {=SUM(MIN([array 1], [array 2]))}.  However, that doesn’t work because MIN([array 1], [array 2]) does not give you an array of element-wise minimum values… instead, it gives you a single minimum value between the two arrays.

To fix this problem, you can employ the IF() function within your array formula.  This is short, clean, and easy to understand:

{=SUM(IF([array 1]>[array 2], [array 2], [array 1]))}

Or if you prefer to use the mask/filter approach, your formula could look like this:

{=SUM([array 1]*([array 1]<[array 2]) + [array 2]*([array 1]>=[array 2]))}

The ([array 1]<[array 2]) part creates a mask of 0’s and 1’s, which is used in a similar manner to the typical SUMIF-type of array formulas.  The two approaches use the same logic, and the only difference is the appearance.

A problem is always simple when you already have the solution, but believe it or not, I came up with the most convoluted approach and a super long formula when this problem was first presented to me.  I’ve included that formula in the sample spreadsheet – see if you can figure out what I was trying to do!

Download the Sample File