In This Chapter
Summing, averaging, and counting
Rounding numbers
Using IF for conditional results
Using the current date and time
Determining if a value is a number
Finding the smallest and largest value
Using conditional summing and counting
This chapter lists the top ten Excel functions. Actually there are fifteen functions here, but in some cases two related functions are treated as a single item. To be fair, there is no absolute top ten list of functions, but over the years (don’t ask how many!) we have worked on hundreds of worksheets and you start to see some of the same functions used all the time.
The functions in this list are of the type that apply to a wide array of needs. You won’t see a financial function or any advanced statistical function. Just the basics. But knowing these is essential to good Excel work. You can always refer here for a quick brush-up on how to use these important functions.
SUM
Adding numbers is one of the most basic mathematical operations. And so there is the SUM function dedicated to doing just that. SUM takes up to 30 arguments. Each argument can be a number, or a range containing multiple numbers. That means SUM can add up a whole bunch of numbers! The syntax is:
You can also use SUM with a range, as shown here:
or with more than one range, such as this:
AVERAGE
Although technically a statistical function, AVERAGE is so often used it deserves a place in the top ten functions. We are all interested in averages. What’s the average score? What’s the average salary? What’s the average height? What’s the average number of hours we watch TV (that’s a sore spot in our households!).
AVERAGE can take up to 30 arguments. Each argument can be a number or a range that contains numbers. The syntax is:
You can also use AVERAGE with a range, as shown here:
or with more than one range, such as this:
COUNT
COUNT counts the number of cells in a range that contain numbers. It does not provide any sum, just the count. So for a list with 10 numbers, COUNT returns 10, regardless of what the numbers actually are.
COUNT takes up to 30 arguments which can be cell references, range references, or numbers themselves. COUNT ignores non-numeric values. If an argument to COUNT is A1:A10, but only two cells contain a number then COUNT returns 2. The syntax is:
You can also use COUNT with a range, as shown here:
or with more than one range, such as this:
INT and ROUND
The INT and ROUND functions both work by removing or reducing the decimal portion of a number. They differ in exactly how they remove it.
INT simply drops the decimal portion without rounding — that is, without regard to whether the number is closer to the next higher integer or the next lower integer. INT always truncates to the next lower integer. For example, INT changes 12.05 to 12, but it will also change 12.95 to 12. Also, INT changes both -5.1 and -5.9 to -6, not to -5, because -6 is the next lower integer. Be aware of this when using INT. INT takes but the single number argument. The syntax is:
On the other hand, the ROUND function lets you control how the decimal portion is handled. ROUND takes two arguments — the number to be manipulated, and the number of decimal places to round to. This gives more control. A number, such as 5.6284 can become 5.628, 5.63, 5.6, or just 6. ROUND always rounds up or down to the nearest number of the next significant digit, so therefore 5.628 becomes 5.63, not 5.62.
In the case of 12.95, ROUND turns this into either 12.9 or 13, depending on the setting of the second argument. Note there are two functions — ROUNDUP and ROUNDDOWN — that round in one direction only. The syntax for ROUND is:
The syntax for ROUNDUP and ROUNDDOWN is the same as ROUND:
IF
IF is a very handy function. It tests a condition and then returns one of two results depending on the outcome of the test. The test must return a true or false answer. For example, a test may be B25 > C30. If true, then IF returns its second argument. If false, IF returns its third argument.
IF is often used as a validation step to avoid unwanted errors. The most common use of this is to test if a denominator is 0 before doing a division operation. By testing for 0 first, you can avoid the #DIV/0! error.
One of the great things about IF is that the result can be a blank. This function is great for when you only want to return a result if the test comes out one way but not if the test comes out the other way. The syntax is:
NOW and TODAY
The NOW function returns the current date and time according to your computer’s internal clock. TODAY returns just the date. If the date or time is wrong, can’t help you with that.
A common use of NOW is to return the date and time for a printed report. You know, so a message such as “Printed on 10/24/2004 10:15″ can be put on the printed paper.
A common use for TODAY is to calculate the elapsed time between a past date and “today.” For example you might be tracking how long a project is taking. A cell on the worksheet has the start date in it. Then in another cell is a formula that subtracts that date from TODAY. The answer is the number of days that have gone by.
NOW and TODAY take no arguments. The syntax is:
Note: You may have to change the formatting of cells containing these functions to have the results appear correctly.
HLOOKUP and VLOOKUP
HLOOKUP and VLOOKUP are both used to find a value in a table. A table is an area of rows and columns that you define. Both of these functions work by using a search value for the first argument, that when found in the table, helps return a different value.
In particular you use HLOOKUP to return a value in a row that is in the same column as the search value. You use VLOOKUP to return a value in a column that is in the same row as the search value. The syntax for these functions are:
ISNUMBER
A rose is a rose and by any other name would smell as sweet, but numbers don’t get off that easy. For example, 15 is a number, but fifteen is a word. The ISNUMBER function tells you flat out true or false if a value in a cell is a number (including the results of formulas). The syntax is:
M1N and MAX
MIN and MAX find the respective lowest or highest numeric value in a range of values. These functions take up to 30 arguments, and an argument can be a range. Therefore you can test a large list of numbers simply by entering the list as a range. The syntax for these functions are:
You can also use MIN and MAX with a range, as shown here:
or with more than one range, such as this:
SUM1F and COUNTIF
SUMIF and COUNTIF respectively sum values or count values if a supplied criterion is met. This makes for some robust calculations. With these functions it’s easy to return answers for questions such as “How many shipments went out in October?” or “How many times did the DJIA go over 10,000 last year?”
SUMIF takes three arguments: a range in which to apply the criteria; the actual criteria; and the range from which to sum values. A key point here is that the first argument may or may not be the same range from which values are summed. Therefore you can use SUMIF for questions such as “How many shipments went out in October?” but also for one such as “What is the sum of the numbers in this list, of just the values in the list that are over 100?” The syntax for SUMIF is:
Note too that the third argument in SUMIF can be left out. When this happens SUMIF uses the first argument as the range in which to apply the criteria, and also the range is from which to sum.
COUNTIF is used to count the number of items in a range that match criteria. This is just a count. The value of the items that match the criteria doesn’t matter past the fact that it matches the criteria. But after a cell’s value matches the criteria, the count of that cell is 1. COUNTIF takes just two arguments: the range from which to count the number of values, and the criteria to apply. The syntax for COUNTIF is:
