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 255 arguments.Each argument can be a single number or a range containing multiple numbers. That means SUM can add up a whole bunch of numbers! The syntax follows:
=SUM(number 1, number 2, …)You can also use SUM with a range, as shown here:
=SUM(A1:A12)You can also use SUM with more than one range, such as this:
=SUM(A1:A12, B1:B12)
AVERAGE
Although technically a statistical function, AVERAGE is used so often that it deserves a place in the top ten functions. Everyone is 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 Americans watch TV?AVERAGE can take up to 255 arguments. Each argument can be a number or a range that contains numbers. The syntax follows:
=AVERAGE(number 1 ,number 2 ,…)You can also use AVERAGE with a range, as shown here:
=AVERAGE(A1:A12)You can also use AVERAGE with more than one range, such as this:
=AVERAGE(A1:A12, B1:B12)
COUNT
COUNT counts the number of cells in a range that contain numbers. It does not provide any sum — just the count. For a list with ten numbers, for example, COUNT returns 10, regardless of what the numbers are.COUNT takes up to 255 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, COUNT returns 2. The syntax follows:
=COUNT(cell reference 1, cell reference 2,…)You can also use COUNT with a range, as shown here:
=COUNT(A1:A12)You can also use COUNT with more than one range, such as this:
=COUNT(A1:A12, B1:B12)
INT and ROUND
The INT and ROUND functions both work by removing or reducing a number's decimal portion. They differ in exactly how they remove it.INT
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. Be aware that INT always truncates to the next lower integer. For example, INT changes 12.05 to 12, but it also changes 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. INT takes a single number argument (as an actual number or a cell reference). The syntax follows:=INT(number or cell reference)
ROUND
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 you 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 5.628 becomes 5.63, not 5.62.ROUND turns 12.95 into either 12.9 or 13, depending on the setting of the second argument. Note that two functions — ROUNDUP and ROUNDDOWN — round in one direction only. The syntax for ROUND follows:
=ROUND(number, number of decimal places to round to)The syntax for ROUNDUP and ROUNDDOWN is the same as ROUND:
=ROUNDUP(number, number of decimal places to round to) =ROUNDDOWN(number, number of decimal places to round to)
IF
IF is a very handy function. It tests a condition and 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, IF returns its second argument. If false, IF returns its third argument.IF is often used as a validation step to prevent unwanted errors. The most common use of this is to test whether 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 when you want to return a result if the test comes out one way but not if the result is otherwise. The syntax follows:
=IF(logical test, value if true, value if false)
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, it 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 12/20/2015 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 may be tracking a project's duration. A cell on the worksheet has the start date. Another cell has 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 for each follows:
=NOW() =TODAY()
HLOOKUP and VLOOKUP
HLOOKUP and VLOOKUP both 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 follows:
=HLOOKUP(lookup value, table area, row, match type) =VLOOKUP(lookup value, table area, column, match type)
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 digit, 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 follows:=ISNUMBER(value)
MIN and MAX
MIN and MAX find the respective lowest or highest numeric value in a range of values. These functions take up to 255 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 follows:=MAX(number1,number2,…) =MIN(number1,number2,…)You can also use MIN and MAX with a range, as shown here:
=MAX(A1:A12)or with more than one range, such as this:
=MAX(A1:A12, B1:B12)
SUMIF and COUNTIF
SUMIF and COUNTIF sum or count values, respectively, if a supplied criterion is met. This makes for some robust calculations. With these functions, it's easy to return answers for a question such as "How many shipments went out in October?" or "How many times did the Dow Jones Industrial Average close over 18,000 last year?"SUMIF takes three arguments:
-
A range in which to apply the criteria
-
The actual criteria
-
The range from which to sum values
=SUMIF(range,criteria,sum_range)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 as the range from which to sum.
COUNTIF counts 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
-
The criteria to apply
COUNTIF(range,criteria)