Nate Oliver's blog

Top-5 Primer Worksheet Functions 

Excel’s a robust and dynamic piece of software offering a variety of capabilities. Today we will discuss the most common use of Excel, an analytical tool with given data sets and 5 must-know, general usage Worksheet Functions to begin to work with Excel. These 5 Worksheet Functions include Sum(), Count(), SumIf(), CountIf() and VLookup().

 Sum()

Sum() allows one to add values in a variety of manners. The most common use would be to sum a given, contiguous range, e.g., =Sum(A1:D10), the result being every cell in A1 through D10 being added together.

Sum() also allows for arguments via the use of a comma, which allows for multiple, non-contiguous ranges to be added. Two differing examples would include both single-cell, non-contiguous ranges, e.g., =Sum(A1,D1,F1) and multi-cell, non-contiguous ranges, e.g., =Sum(A1:A5,C1:C5,E1:E5).

One thing to note with Sum() is that it does not ignore error values in your data, e.g., if you have 1,000 valid cells of data and a single cell in the calculation range contains an error (e.g., DIV/0 – division by 0), Sum() will not return the sum of the range, but the error, DIV/0. This behavior is by design, to alert you that there is a potential issue with your data. However, later we will explore a simple work-around (see SumIf()).

Count()

Count() allows one to gather a count numeric data in their spreadsheet. As with the previously discussed Sum() function, Count() can work with single ranges, non-contiguous ranges and multi-cell ranges - the latter with comma-separated arguments.

SumIf()

Sum() works great if you want to add everything in sight, but what if we want to add values based on a specific criteria?

Enter SumIf(), which will sum a given range of values, if a given criteria is met. In pseudo code, SumIf() reads as =SumIf(Range of Criteria Values, Your Criteria, Range to be Summed). The last argument, separated by commas, is optional – if omitted, the Range of Criteria Values is summed.

Note that SumIf() supports wildcard characters and mathematical operators, as mentioned  here:

http://office.microsoft.com/en-us/excel/HP052092921033.aspx?pid=CH062528291033

As promised, a quick and risk-free work-around to summing ranges that house error values within its cells. It’s not recommended that Excel users allow error values to flow through their spreadsheets; however, it is imaginable that an individual has a pressing engagement, say a tee-time, and needs to provide a colleague a sum of data that houses errors, much sooner than later.

SumIf() allows one to do this by invoking a simple mathematical operator, which forces SumIf() to ignore all non-numeric data, of any sort, including error values. So, the following transaction is risk-free: =SUMIF(A1:A10,"<=9.99999999999999E+307")

The reason that this transaction is risk-free is that 9.99999999999999E+307 is the largest number allowable in Excel (see the help file on “Limitations”), so all functional numbers in Excel must either equal or be less than this number, and only numeric cell entries will be considered at this point.

Be careful with 9.99999999999999E+307, there’s a reason I’m burying it in quotes, Excel can do strange things with numbers, due to its Data Types – a subject for another day.

CountIf()

Like SumIf(), CountIf() provides us with a manner of conditional counting. The slight variance is that it takes two arguments, the range you want to evaluate and the criteria to evaluate that range by, e.g., =CountIf(Range you want to count, Criteria to be met).

CountIf() also supports wildcard characters and mathematical operators, as should be noted, here:

http://office.microsoft.com/en-us/excel/HP052090291033.aspx?pid=CH062528311033

VLookup()

VLookup() is an extremely useful worksheet function, in Excel, and chances are if you’re interviewing for a job that requires somewhat advanced Excel experience, you will be asked about it. While we won’t cover the several underlying algorithms, let’s have a look at really understanding the implementation.

Most of Excel’s worksheet functions are designed with names that are memorable, in an English-sense, and VLookup() is no exception, in layman’s terms, it is short for “Vertical Lookup”. This function performs a search on a column of values, a column being vertical, and if said value is found, offsets a number of specified columns, and returns a corresponding value.

Before using, I recommend reviewing the help file for this function, as well:

http://office.microsoft.com/en-us/excel/HP052093351033.aspx?pid=CH062528281033

So what does this mean? First of all, pretend you don’t know the last argument, for now, let’s stick with False. Here in pseudo code is what this means:

=VLOOKUP(Value to Find, Range of Values – Including the Offset Range, Number of Columns to Offset from first Column, False - for now)

The left-most column of the 2^nd argument refers to the column you’re actually searching, and the right-most isn’t what this is limited to, but is generally recommended to be - the farthest column you want to offset to, to return your value. So, if you want to return a value from column B, that is dependant on a match from column A, the example could look something like this:

=VLOOKUP("August",A1:B12,2,FALSE)

Where 2 indicates to return a value in the 2^nd column of the specified Range of values.

Returning to the 4^th argument of VLookup() and the True argument. This can be more efficient due to the manner in which the underlying algorithm processes. But, as noted in the help file, your first column has to be sorted in ascending order for it to be trustworthy, due to a difference in the way it searches.

Conclusion

There are quite a few of differing, very useful worksheet functions in Excel, and most of them are well documented. This is a quick-hit list of some that might prove to be more frequently used and useful for general purposes.