Excel sports a large number of worksheet functions that can be used to slice and dice data. One of the qualities that makes Excel so fascinating is the endless variety that these functions can be combined into formulas producing powerful, surprising, even elegant solutions. There is much to learn here and the user is easily bewildered, wondering which function or combination to work with to produce the desired results.
Just knowing where to start in a given situation requires experience. There's a science to it, but truly inspired solutions are as much art as science. Think of this series of articles as a treasure map, as I will attempt to lead you to the most productive paths.
Before we delve in, I need to backup. We must understand what a range is and how it is specified. A range is a group of one or more cells. The range is NOT the values in those cells. This distinction is important. Some functions (and hence formulas) return values. Some return ranges. Still others can return either in varying circumstances. The value is just one property of a cell. Other properties include color, type of border, font, etc. Typically just the value property is directly accessed from a formula, but many of these other properties can be manipulated via formulas when using Conditional Formatting. All range properties can be accessed when using VBA.
Ranges can be specified by either R1C1 notation or A1 notation. R1C1 is no longer common and requires an option setting. This article will discuss A1 notation exclusively.
Excel recognizes three reference operators. These three operators combine input ranges and produce an output range.
The first is the Range Operator, which produces a minimum rectangle around the inputs. The Range Operator is the COLON:
You've seen this in action a million times, I'm sure, but you may have never realized what it means. The colon specifies the range defined by the minimum rectangle circumscribing the input ranges. The input ranges can be specified by any method that describes a range, including defined names, functions, and formulas:
A1:K2:N16:W15
A1:lstLastInvoice
INDIRECT("B1"):OFFSET(Z1,,,100,100)
In each case the Range Operator returns the range that circumscribes the input ranges with the tightest rectangle. At first glance, the first example seems redundant, and in fact, it can be simplified:
but by substituting formulas for the input ranges, novel ways of controlling other formulas begin to present themselves.
There are two other reference operators and you may not even know about them. These are the Union and Intersect operators. The Union Operator is the COMMA. The Intersect Operator is the SPACE character (space bar).
While the Range Operator always returns a rectangular range, these two other operators can return ranges of any shape, contiguous or not, as long as all of the input ranges are on the same worksheet:
(A1,B2,C3,D4,E5,F6)
(A3:E3,C1:C5)
(A1,A10,J10,J1)
(CurRow CurCol)
The first of these five examples returns a range in the shape of a diagonal. The second returns a range in the shape of a plus sign. The third returns a range composed solely of C3. The fourth returns a range of four disconnected cells in the shape of the vertices of a square. The last returns the range where the named formulas intersect. This last example is probably the most overlooked lookup method in Excel and can be used to conveniently return information from a list or a table of data. Again, the caveat is that all of the input ranges must be on one worksheet.
Another note is in order for the Union and Intersect Range Operators. In math, a union of two or more sets will only include an element once, but the Excel Union Range Operator does not work this way (pity):
=SUM( (A2:B2,B2:C2) )
The above formula uses the Union Range Operator. It should be the equivalent of:
=SUM(A2:C2)
But it is not. Whatever is in B2, gets summed twice in this example. Also, notice the parentheses around the compound range. This is good practice since functions that require multiple input parameters use commas to separate those inputs. The parentheses ensure that Excel is not confused by ambiguous input.
So the Union Range Operator joins input ranges but is not a true Set Union Operator. On the other hand, the Intersect Range Operator is a true Set Intersect Operator, returning only elements (cells) in common.
There's at least one more twist to range referencing that deserves mention. Ranges can also be 3-D:
Sheet1:Sheet12!A14:Z14
Notice that the Range Operator is used twice in this 3-D range example. This returns a range composed of A14:Z14 for twelve worksheets. This could be useful for adding monthly sheets, for example. The other two reference operators do not work on 3-D ranges. Here is an
article from Microsoft explaining 3-D ranges in greater detail and specifying which functions work with them. There's actually more functions than listed there, but that is a topic for another day.
Now that we know what a range is, we need to realize that the roster of functions that we can use to slice and dice our data, use these ranges as inputs. This is most commonly done with rectangular ranges, but the others can produce interesting results.
There is a lot to go over here and so I am going to break it up into a series of articles. To begin with, I thought I would share a video presentation I made a couple of years ago on the VLOOKUP function, long before I began this blog. I had the idea for the blog at the time, but really did not know the direction I would take with it. Here is the VLOOKUP video:
It's a long video; 27 minutes on a simple function! Probably too long. But I'd like to hear your feedback. I've always wanted to host a masters class in Excel, and after seeing the popularity of Chandoo's foundational
Excel School, I started thinking of this once again. The production is not as polished as I would like, but hey it was a couple of years ago when I was just learning how to make videos.
The idea behind the masters series is to explore each function in detail and then see how they can be combined into elegant formulas. Of course it would have to have advanced charting, excelhero style. So what do you think? Is this something I should invest my time in?
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=aa7638b5-871a-4651-93a5-f057984f4eea)
