March 2011 Archives

Excel Hero LinkedIn Group!

| 1 Comment | 0 TrackBacks


Excel Hero now has its own group on LinkedIn. You are cordially invited to join us.

We are making it THE PLACE for Excel answers, sharing, and networking - a super forum if you like.

Just click on the LinkedIn logo to join the group!





The Imposing INDEX

| 258 Comments | 0 TrackBacks


From my perspective, the Excel INDEX function is the single most important in the roster of Microsoft Excel functions.

Now that might be surprising considering the function's humdrum name, but please pay close attention, because INDEX is one of the magical secrets of how to use Excel! So what's so great about the INDEX function? It's nonvolatile, sprightly, agile, and versatile. Excel INDEX can return one value or an array of values; it can return a reference to one cell or to a range of cells. INDEX works well on either side of the three Reference Operators - the colon, the space, and the comma.


index_function_parameters_excelhero.png

 

The idea with INDEX is that you give it a range (or an array) and then specify an element to return. 

So,

=INDEX(A1:A5,2)

...returns a reference to cell A2.

And,

=INDEX({95,96,97,98,99},5)

...returns the value of 99.



Note:  If you use an international version of Excel, some of the array constants may not work as presented.  Please see the instructions at the end of this article.



Nothing extraordinary thus far. But it is crucial to understand and so I'll reiterate that the first example above returns a REFERENCE, while the second returns a VALUE. 

Also, note that both of these examples demonstrate how the Excel INDEX function behaves when passed a vector. A vector is a one-dimensional array or range. When passed a vector, INDEX does not care whether that vector is vertical or horizontal. The second parameter of INDEX identifies itself as row number. But this is not correct, when a vector is passed in. Instead of being the row number, the second input becomes the element number for a vector - and as such, horizontal vectors work just fine with this notation. In the second example above, five is not the row number, it is the element number!


But we can force INDEX to behave in its normal two-dimensional fashion:


=INDEX({95,96,97,98,99},1,5)


...also returns the value of 99.


And it is with this two-dimensional behavior that INDEX begins to really shine. Obviously,

=INDEX(A1:C5,1,3)

...returns a reference to C1. But what is surprising to most users is that both of the following do as well:

{=INDEX(A1:C5,0,3)}
{=INDEX(A1:C5,,3)}



excel-hero-academy-web-banner1.jpg


Return Whole Rows or Columns

In fact, until the user understands what is happening here, they might think that the function has a bug because the overriding theme with INDEX is that unlike OFFSET for example, it must return an element from within the range or array passed to it - and row zero (or column zero) is outside. So what does ZERO or a null value mean in this setting?

If the row_num parameter is zero or missing, this directs INDEX to return the entire column specified by the column_num parameter!

{=INDEX(A1:C5,,2)}

...returns a reference to the range, B1:B5.

And the converse is also true - specifying a zero or missing column_num will return an entire row. The catch here is that the missing column_num MUST include the comma, like so:

{=INDEX(A1:C5,2,)}

...which returns a reference to the range, A2:C2.


But remember that when a particular cell is given a reference to a multi-cell range or that cell is set equal to an array, just the first element shows in the output cell - so most of the row or column returned by the two above INDEX formulas will be hidden. But you can array-enter one of these Microsoft Excel formulas over a range to see the entire output. You do this by selecting the output range first, typing the formula in the formula bar, and entering it by Control-Shift-Enter.

So yes the technique works with returning entire rows or columns of a two-dimensional array as well:

=SUM(INDEX({1,2,3,4,5;6,7,8,9,10;11,12,13,14,15},3,))

...results in 65, which is the summation of the entire third row of the input array.


Lookups

This ability to return entire columns or rows from a larger range or array is extraordinarily useful.  

Suppose we have a table or list of metrics for countries in the range of A1:M200. We can create a named formula that refers to this range - let's call it simply, d, for data. Then we can name individual columns of the data painlessly. If the country names are in the first column, we can create a named formula:

Country:   =INDEX(d,,1)

And if population is in the 4th column, we can create a named formula:

Population:   =INDEX(d,,4)

Now suppose we wanted to lookup the population of Scotland, all we need to do is:

=INDEX(Population,MATCH("Scotland",Country,0))

The Excel MATCH function simply produces a row number for INDEX.

This method of lookup is on par in terms of speed as doing a VLOOKUP, if you are doing just one lookup. But it has advantages over VLOOKUP. It is not restricted to looking up a column to the right like VLOOKUP is. Excel INDEX MATCH is significantly quicker than VLOOKUP if we are doing lookups for a list of countries, and we array-enter the one formula over the entire output column.

If we need to return multiple columns of metrics for a list of countries, the speed benefit is even greater. Suppose that GDP is the 2nd column of the data and that Capital was the 11th column.

If on a new worksheet, we wanted to output three columns of data for each country, Population, GDP, and Capital, the most efficient way to do so is to dedicate one column to create a common index, and then array-enter the INDEX formula over the entire three columns of output.

In this scenario, column B would be the list of countries. Column A would be dedicated to the common index. In A2, we would enter:

=MATCH(B2,Country,0)

...and then copy this formula down as far as the countries are listed in column B.

And then in columns, C, D, and E we would array-enter over the entire range that extends as far down as the countries:

{=INDEX(d,A2:A100,{4,2,11})}

To be clear, this means that if we had 99 countries in column B, we would select C2:E100, and then in the formula bar we would type the above formula, and then enter it by Control-Shift-Enter.

The end result is that the one formula is executed just once (in array fashion) and is brutally fast leaving a very light, non-volatile footprint in your model.


Sums

This usage of INDEX is a great way to power SUMPROPDUCT as well. For example:

=SUMPRODUCT( (Left(Country,1)="U")*Population )

...which returns the total population for all countries that start with the letter, U.

And while named formulas are self-documenting, this works just as well:

=SUMPRODUCT( (Left(INDEX(d,,1),1)="U")*INDEX(d,,4) )



Excel School Online Training Program



The Dynamic Range

Building on this idea, we can alter the named formula, d, so that it results in a dynamic range instead of a fixed range. And here INDEX reigns supreme. 

A Dynamic range is often constructed with OFFSET or INDIRECT. Unfortunately, both of these Microsoft Excel functions are volatile, which simply means that all formulas that include these functions will recalculate every single time anything on the worksheet changes. For example, adding a value to any blank cell will cause all volatile formulas (and any other formulas that depend on them) to recalculate. Depending on your model design and size, this can have a major impact on the fluidity and responsiveness of the model.

INDEX can be used to create a dynamic range, and not only is it nonvolatile, it is way faster than either OFFSET or INDIRECT. In fact, the improvement in performance is so great that INDEX should be the foundation of all dynamic ranges in professional models.

The approach to creating the dynamic range is different than the approach used for OFFSET or INDIRECT. For those functions, the range is created within the function. With INDEX on the other hand, the dynamic range is produced by using INDEX on one side (or sometimes both) of the Range Operator, which is the colon.

For example, consider this normal looking range reference:

=A2:A100

By itself, this reference could be a hard reference to the country names in our list of countries. Assuming no blanks in our list and that there is a column header in A1, perhaps "Country", the following formula would return the name of the last country in the list:

=INDEX(A:A,COUNTA(A:A))

But the INDEX function here is really returning the reference to the last cell in column A with a country name, and then the equal sign forces the value of that cell to be returned. In the scenario that we have painted, this INDEX formula is really returning the reference, A100.

So the following two formulas point to the exact same range:

=A2:A100
=A2:INDEX(A:A,COUNTA(A:A))

But there is a significant difference. The first is a hard coded, static reference. The second is a nonvolatile, dynamic range that will expand or contract as the number of countries in the list changes. Please note that before we replace the Refers To value of our named formula, Country, with this INDEX based dynamic range, we need to make the references absolute. It would then look like this:

Country:   =$A$2:INDEX($A:$A,COUNTA($A:$A))

We can use the same techniques to create a two-dimensional dynamic range so that the number of columns is also dynamic:

d:   =$A$2:INDEX($1:$65535,COUNTA($A:$A),COUNTA($1:$1))

And with d defined in this dynamic manner, we can still do all of the wonderful row and column referencing from above, such as:

{=INDEX(d,$A2,{4,2,11})}


As a footnote to this section on dynamic ranges, I want to point out that when you use a function on either side (or both sides) of any of the three Reference Operators, the resulting formula always recalculates when the workbook is opened. So while INDEX is nonvolatile, it becomes what I term quasi-volatile when used for dynamic ranges - but this is orders of magnitude better than volatile, and so it remains the best dynamic range foundation by far.

And as a post script to this section on the dynamic range, you may very well ask should I not just use the new Structured Table References available since Excel 2007? While STR is a robust option, it is heavy. If you need speed, nothing beats the alacrity of INDEX.



Noncontiguous Areas

When working with ranges, INDEX offers a fourth parameter to select the AREA to work with from a noncontiguous input range. The areas are referenced by integer in the order that they appear in the input range. For example,

=INDEX((data1,data2,data3),,,2)

...returns a reference to data2. And

{=INDEX((data1,data2,data3),,1,3)}

...returns the entire first column of data3. With a little ingenuity, this can be extraordinarily useful for charting... and many other activities. And you should note that the number of areas specified has no hard limit, and those noncontiguous areas need not be of the same size!


More

All of this just scratches the surface of what is possible with this essential member of the Microsoft Excel functions. It can be used in countless situations, even to compare Excel worksheets, etc.

Some of the synonyms for the word, "imposing", in my thesaurus are impressive, august, commanding, effective, exciting, magnificent, and mind-blowing. The INDEX worksheet function is truly imposing!



International Versions of Excel

Throughout this article, I have used array constants within formulas.  This is a powerful technique.  In the English Language version of Excel, the symbol used to separate the columns in an array constant is the comma.  The symbol used to separate rows is the semicolon.

Other language versions of Excel use different symbols for column and row separation.  However it is easy to discover what symbols are used in your version of Excel.

Open the VBEditor (ALT-F11), and place the cursor in the Immediate Pane (CTRL-G).

Enter the following two lines in the the Immediate Pane (it's OK to use the clipboard):


?Application.International(xlColumnSeparator)

?Application.International(xlRowSeparator)


Click on each in turn and press ENTER on the keyboard.

Uses the printed characters to separate columns and rows in array constants.





And here are some other reference articles at Excel Hero:

The Imposing INDEX
The Venerable SUMPRODUCT
- I Heart IF
5 And 3 Is 1
Live Calendar Musings
Which Function To Use
Excel VBA Shortcut Range References
Excel Partial Match Database Lookup
Excel High Precision Engineering Chart #1
Excel Zip Code Lookup
Excel Dynamic Checkmark
Animated Business Chart
Excel Acrobat PDF Form Filler


Learn Advanced Excel!

Advanced and Creative Formulas is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning. 

I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.

In a few weeks I will be offering the course again. There's already over 250 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!



 

Excel Dynamic Checkmark

| 6 Comments | 0 TrackBacks
Sometimes you just need an interactive checkmark. Unfortunately, Excel does not come with a really cool solution for this. 

excelhero_check.gif

Oh, there is the little trick about inserting a checkmark symbol - but this is not dynamic and provides zero sizzle. 

 
Then there are the form controls. While they have always had potential, I think they have not been enhanced in over a decade. They are cumbersome and create potential problems when rows are resized or moved, even though they come with options that help a little in this regard.

But perhaps the biggest shortcoming of form control checkboxes is the fact that they do not automatically clone themselves in a table as the table grows. It's astounding to me that after all these years, Excel does not have a Check Column as an option for a table.




I needed this recently and so I created my own. Excel 2007 and 2010 come with a new sub-feature of Conditional Formatting called Icon Sets. With a little VBA coding to create a generic round-robin array-element selector, I came up with a nice addition to the Excel interface. The animated GIF above shows an inkling of what can be done with this technique.

I think it is pretty cool, what do you think?

Here is the file:


The round-robin functionality will work in older versions of Excel (which can be very useful in its own right - examine the Alpha Round Robin Demo in the workbook for an example), but the Icons Sets will display simply as numbers in the old versions.



Learn Advanced Excel!

Advanced Interface Design is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning. 

I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.

This Spring I will be offering the course again. There's already over 200 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!






Animated Business Chart #4 - Body Mass Index

| 3 Comments | 0 TrackBacks
Recently I stumbled across a very interesting Flash based Body Mass Index chart at The Washington Post. I thought it would be an interesting exercise to emulate the chart in Excel. I think it really captures the essence of the original.


excelhero_body_mass_index.gif

 

This Animated GIF cannot display all the colors properly and shrinking it to fit this blog, destroys the sharpness - but the animation still looks decent.

The Excel workbook demonstrates advanced Excel techniques to dynamically slice through gobs of data. The data sheet has nearly 6,000 records and watching the animation, I find myself wishing there were more years of data so that the animation would last longer!

Not only is the world getting fatter and this includes virtually every country, but women are leading the way. That's not comforting!

With the slider you can choose which country to highlight. You can choose to watch the animation at this normal speed or in slow motion.

All in all, this Excel animated business chart works well, and the Excel implementation is very instructive.

Here is the workbook:







Get Trained!

Sophisticated chart animation is just one of literally dozens of advanced methods that my students in the inaugural class of the Excel Hero Academy are learning. 

I've received dozens of messages from students telling me that the academy is the best Excel training they have ever found at any price and that it is possibly the best investment they've ever made. Wow.

This Spring I will be offering the course again. There's already 200 people on the interest list for the next class. Why not join us? Just fill the form below and I'll send you more information as we get closer!









Here's a list of other animated charts on Excel Hero:

 - Excel, A Presentation Platform (Number Spiral)
 - Lilac Chaser (Optical Illusion)
 - Stereokinetic (Optical Illusion)
 - Illusory Contours (Optical Illusion)
 - Breathing Square (Optical Illusion)
 - Enigma (Optical Illusion)
 - Two Sinusoids (Optical Illusion)
 - Perpetual Collisions (Optical Illusion)
 - Freezing Rotation (Optical Illusion)
 - Reverse Spoke Illusion (Optical Illusion)
 - Stepping Feet Radial Illusion (Optical Illusion)
 - Swimming Fish (Optical Illusion)
 - Mutually Interfering Shapes (Optical Illusion)

About this Archive

This page is an archive of entries from March 2011 listed from newest to oldest.

February 2011 is the previous archive.

May 2011 is the next archive.

Find recent content on the main index or look in the archives to find all content.