Excel 2016 All-in-One For Dummies (2016)

Book III

Formulas and Functions

Chapter 5

Math and Statistical Formulas

In This Chapter

Rounding off numbers

Raising numbers to powers and finding square roots

Conditional summing

Using basic statistical functions, such as AVERAGE, MIN, and MAX

Building formulas that count

Using specialized statistical functions

This chapter examines two larger categories of Excel functions: Math & Trig and statistical functions. The Math & Trig functions are found on the Math & Trig command button’s drop-down menu on the Ribbon’s Formulas tab (the button with the θ on the book cover). This category includes all the specialized trigonometric functions such as those that return the sine, cosine, or tangents of various angles and logarithmic functions (for finding the base-10 and natural logarithms of a number), along with the more common math functions for summing numbers, rounding numbers up or down, raising a number to a certain power, and finding the square root of numbers. Foremost among the more recently added Math & Trig functions in Excel 2016 is the Arabic function that converts any Roman numeral text in a worksheet range into Arabic numerals (xxi to 21, for example). This newer function compliments the older Roman function, which, you guessed it, converts Arabic numerals into Roman numeral text (16 into XVI, for instance).

The statistical functions are found on a continuation menu accessed from the More Functions command button’s drop-down menu on the Formulas tab (the button with the ellipsis or three periods). Statistical functions include the more common functions that return the average, highest, and lowest values in a cell range all the way to the very sophisticated and specialized functions that calculate such things as the chi-squared distribution, binomial distribution probability, frequency, standard deviation, variance, and — my personal favorite — the skewness of a distribution in a particular population.

Math & Trig Functions

The mathematical functions are technically known as the Math & Trig category when you encounter them on the Math & Trig command button on the Ribbon’s Formulas tab or in the Insert Function dialog box (opened by clicking the Insert Function button on the Formula bar).

This category groups together all the specialized trigonometric functions with the more common arithmetic functions. Although the trigonometric functions are primarily of use to engineers and scientists, the mathematical functions provide you with the ability to manipulate any type of values. This category of functions includes SUM, the most commonly used of all functions; functions such as INT, EVEN, ODD, ROUND, and TRUNC that round off the values in your worksheet; functions such as PRODUCT, SUMPRODUCT, and SUMSQ that you can use to calculate the products of various values in the worksheet; and the SQRT function that you can use to calculate the square root of a value.

Rounding off numbers

You use the ROUND function found on the Math & Trig command button’s drop-down menu to round up or down fractional values in the worksheet as you might when working with financial spreadsheets that need to show monetary values only to the nearest dollar. Unlike when applying a number format to a cell, which affects only the number’s display, the ROUND function actually changes the way Excel stores the number in the cell that contains the function. ROUND uses the following syntax:

ROUND(number,num_digits)

In this function, the number argument is the value that you want to round off, and num_digits is the number of digits to which you want the number rounded. If you enter 0 (zero) as the num_digits argument, Excel rounds the number to the nearest integer. If you make the num_digitsargument a positive value, Excel rounds the number to the specified number of decimal places. If you enter the num_digits argument as a negative number, Excel rounds the number to the left of the decimal point.

Instead of the ROUND function, you can use the ROUNDUP or ROUNDDOWN function. Both ROUNDUP and ROUNDDOWN take the same number and num_digits arguments as the ROUND function. The difference is that the ROUNDUP function always rounds up the value specified by the number argument, whereas the ROUNDDOWN function always rounds the value down.

Figure 5-1 illustrates the use of the ROUND, ROUNDUP, and ROUNDDOWN functions in rounding off the value of the mathematical constant pi (π). In cell A3, I entered the value of this constant (with just nine places of nonrepeating fraction displayed when the column is widened) into this cell, using Excel’s PI function in the following formula:

=PI()

Figure 5-1: Rounding off the value of pi with the ROUND, ROUNDUP, and ROUNDDOWN functions.

I then used the ROUND, ROUNDUP, and ROUNDDOWN functions in the cell range B3 through B10 to round this number up and down to various decimal places.

Cell B3, the first cell that uses one of the ROUND functions to round off the value of pi, rounds this value to 3 because I used 0 (zero) as the num_digits argument of its ROUND function (causing Excel to round the value to the nearest whole number).

In Figure 5-1, note the difference between using the ROUND and ROUNDUP functions both with 2 as their num_digits arguments in cells B5 and B7, respectively. In cell B5, Excel rounds the value of pi off to 3.14, whereas in cell B7, the program rounds its value up to 3.15. Note that using the ROUNDDOWN function with 2 as its num_digits argument yields the same result, 3.14, as does using the ROUND function with 2 as its second argument.

The whole number and nothing but the whole number

You can also use the INT (for Integer) and TRUNC (for Truncate) functions on the Math & Trig command button’s drop-down menu to round off values in your spreadsheets. You use these functions only when you don’t care about all or part of the fractional portion of the value. When you use the INT function, which requires only a single number argument, Excel rounds the value down to the nearest integer (whole number). For example, cell A3 contains the value of pi, as shown in Figure 5-1, and you enter the following INT function formula in the worksheet:

=INT(A3)

Excel returns the value 3 to the cell, the same as when you use 0 (zero) as the num_digits argument of the ROUND function in cell B3.

The TRUNC function uses the same number and num_digits arguments as the ROUND, ROUNDUP, and ROUNDDOWN functions, except that in the TRUNC function, the num_digits argument is purely optional. This argument is required in the ROUND, ROUNDUP, and ROUNDDOWN functions.

The TRUNC function doesn’t round off the number in question; it simply truncates the number to the nearest integer by removing the fractional part of the number. However, if you specify a num_digits argument, Excel uses that value to determine the precision of the truncation. So, going back to the example illustrated in Figure 5-1, if you enter the following TRUNC function, omitting the optional num_digits argument as in

=TRUNC($A$3)

Excel returns 3 to the cell just like the formula =ROUND($A$3,0) does in cell B3. However, if you modify this TRUNC function by using 2 as its num_digits argument, as in

=TRUNC($A$3,2)

Excel then returns 3.14 (by cutting the rest of the fraction) just as the formula =ROUND($A$3,2) does in cell B5.

The only time you notice a difference between the INT and TRUNC functions is when you use them with negative numbers. For example, if you use the TRUNC function to truncate the value -5.4 in the following formula:

=TRUNC(-5.4)

Excel returns -5 to the cell. If, however, you use the INT function with the same negative value, as in

=INT(-5.4)

Excel returns -6 to the cell. This is because the INT function rounds numbers down to the nearest integer using the fractional part of the number.

Let’s call it even or odd

Excel’s EVEN and ODD functions on the Math & Trig command button’s drop-down menu also round off numbers. The EVEN function rounds the value specified as its number argument up to the nearest even integer. The ODD function, of course, does just the opposite: rounding the value up to the nearest odd integer. So, for example, if cell C18 in a worksheet contains the value 345.25 and you use the EVEN function in the following formula:

=EVEN(C18)

Excel rounds the value up to the next whole even number and returns 346 to the cell. If, however, you use the ODD function on this cell, as in

=ODD(C18)

Excel rounds the value up to the next odd whole number and returns 347 to the cell instead.

Building in a ceiling

The CEILING.MATH function on the Math & Trig command button’s drop-down menu enables you to not only round up a number, but also set the multiple of significance to be used when doing the rounding. This function can be very useful when dealing with figures that need rounding to particular units.

For example, suppose that you’re working on a worksheet that lists the retail prices for the various products that you sell, all based upon a particular markup over wholesale, and that many of these calculations result in many prices with cents below 50. If you don’t want to have any prices in the list that aren’t rounded to the nearest 50 cents or whole dollar, you can use the CEILING function to round up all these calculated retail prices to the nearest half dollar.

The CEILING.MATH function uses the following syntax:

CEILING.MATH(number,[significance],[mode])

The number argument specifies the number you want to round up and the optional significance argument specifies the multiple to which you want to round. (By default, the significance is +1 for positive numbers and -1 for negative numbers.) The optional mode argument comes into play only when dealing with negative numbers where the mode value indicates the direction toward (+1) or away (-1) from 0.

For the half-dollar example, suppose that you have the calculated number $12.35 in cell B3 and you enter the following formula in cell C3:

=CEILING.MATH(B3,0.5)

Excel then returns $12.50 to cell C3. Further, suppose that cell B4 contains the calculated value $13.67, and you copy this formula down to cell C4 so that it contains

=CEILING.MATH(B4,0.5)

Excel then returns $14.00 to that cell.

CEILING.MATH in Excel 2016 replaces the CEILING function supported in older versions of Excel. You can still use the CEILING function to round your values; just be aware that this function is no longer available on the Math & Trig drop-down menu on the FORMULAS tab of the Ribbon or in the Insert Function dialog box. This means that you have to type =cei directly into the cell to have the CEILING function appear in the function drop-down menu immediately below CEILING.MATH.

POWER and SQRT

Although you can use the caret (^) operator to build a formula that raises a number to any power, you also need to be aware that Excel includes a math function called POWER found on the Math & Trig command button’s drop-down menu that accomplishes the same thing. For example, to build a formula that raises 5.9 to the third power (that is, cubes the number), you can use the exponentiation operator, as in

=5.9^3

You can have Excel perform the same calculation with the POWER function by entering this formula:

=POWER(5.9,3)

In either case, Excel returns the same result, 205.379. The only difference between using the exponentiation operator and the POWER function occurs on that rare, rare occasion when you have to raise a number by a fractional power. In that case, you need to use the POWER function instead of the caret (^) operator to get the correct result. For example, suppose that you need to raise 20 by the fraction 3/4; to do this, you build the following formula with the POWER function:

=POWER(20,3/4)

To use the exponentiation operator to calculate the result of raising 20 by the fraction 3/4, you can convert the fraction into decimal form, as in

=20^0.75

The SQRT function on the Math & Trig command button’s drop-down menu enables you to calculate the square root of any number that you specify as its sole number argument. For example, if you use the SQRT function to build the following formula in a cell:

=SQRT(144)

Excel returns 12 to that cell.

The SQRT function can’t deal with negative numbers, so if you try to find the square root of a negative value, Excel returns a nice #NUM! error value to that cell. To avoid such a nuisance, you need to use the ABS (for absolute) math function, which returns the absolute value of a number (that is, the number without a sign). For example, suppose that cell A15 contains ($49.00), a negative value formatted in parentheses with the Accounting Number format to show that it’s something you owe, and you want to return the square root of this number in cell A16. To avoid the dreaded #NUM! error, you nest the ABS function inside the SQRT function. The ABS function returns the absolute value of the number you specify as its sole argument (that is, the value without its sign). To nest this function inside the SQRT function, you create the following formula:

=SQRT(ABS(A15))

Excel then returns 7 instead of #NUM! to cell A16 because the ABS function removes the negative sign from the 49.00 before the SQRT function calculates its square root. (Remember that Excel always performs the calculations in the innermost pair of parentheses first.)

The SUM of the parts

No function in the entire galaxy of Excel functions comes anywhere close to the popularity of the SUM function in the spreadsheets that you build. So popular is this function, in fact, that Excel has its own Sum command button located on the HOME tab of the Ribbon (the one with the Σ on it) that you most often use to build your SUM formulas. You should, however, be aware of the workings of the basic SUM function that the AutoSum button enables you to use so easily.

For the record, the syntax of the SUM function is as follows:

SUM(number1,[number2],[…])

When using the SUM function, only the number1 argument is required; this is the range of numbers in a cell range or array constant that you want added together. Be aware that you can enter up to a total of 29 other optional number arguments in a single SUM formula, all of which are separated by a comma (,). For example, you can build a SUM formula that totals numbers in several different ranges, as in

=SUM(B3:B10,Sheet2!B3:B10,Sheet3!B3:B10)

In this example, Excel sums the values in the cell range B3:B10 on Sheet1, Sheet2, and Sheet3 of the workbook, giving you the grand total of all these values in whatever cell you build this SUM formula.

Conditional summing

The SUM function is perfect when you want to get the totals for all the numbers in a particular range or set of ranges. But what about those times when you only want the total of certain items within a cell range? For those situations, you can use the SUMIF or SUMIFS function on the Math & Trig command button’s drop-down menu.

The SUMIF function enables you to tell Excel to add together the numbers in a particular range only when those numbers meet the criteria that you specify. The syntax of the SUMIF function is as follows:

SUMIF(range,criteria,[sum_range])

In the SUMIF function, the range argument specifies the range of cells that you want Excel to evaluate when doing the summing; the criteria argument specifies the criteria to be used in evaluating whether to include certain values in the range in the summing; and finally, the optionalsum_range argument is the range of all the cells to be summed together. If you omit the sum_range argument, Excel sums only the cells specified in the range argument (and, of course, only if they meet the criteria specified in the criteria argument).

The SUMIFS (that’s ifs, plural) function works like SUMIF function except that it enables you to specify more than one criteria range that controls when a certain range of values are summed. Its syntax is a little bit different:

SUMIFS(sum_range,criteria_range,criteria,…)

For this function, the sum_range argument specifies all the possible values that can be summed, the criteria_range specifies the cells with all the entries that are to be evaluated by the if criteria, and the criteria argument contains the expression that is to be applied to the entries in thecriteria_range to determine which of the values to total in the sum_range.

Summing certain cells with SUMIF

Figure 5-2 illustrates how you can use the SUMIF function to total sales by the items sold. This figure shows a Sales data list sorted by the store location and then the item sold. In this Daily Sales data list, there are three locations: Mission Street, Anderson Rd., and Curtis Way, of which only sales made at the Anderson Rd. location are visible in this figure.

Figure 5-2: Using SUMIF to total sales by items sold.

To total the sales of Lemon tarts at all three locations in this data list, I created the following SUMIF formula in cell I3:

=SUMIF(item_sold,"=Lemon tarts",daily_sales)

In this example, item_sold is the range name given to the cell range C3:C62, which contains the list of each item that has been sold in the first five days of January, 2016 (Lemon tarts, Blueberry muffins, Lots of chips cookies, or Strawberry pie), and daily_sales is the range name assigned to the cell range G3:G62, which contains the extended sales made at each store for each item.

The SUMIF formula in cell I3 then looks for each occurrence of “Lemon tarts” in the item_sold range (the criteria argument for the SUMIF function) in the Item column of the Cookie Sales list and then adds its extended sales price from the daily_sales range in the Daily Sales column to the total.

The formulas in cells I4, I5, and I6 contain SUMIF functions very similar to the one in cell I3, except that they substitute the name of the dessert goodie in question in place of the =Lemon tarts criteria argument.

Summing on multiple criteria with SUMIFS

Figure 5-3 illustrates the use of the SUMIFS function to apply multiple criteria in the summing of the daily sales. Here, I want to know the total of the sales of one item (Lemon tarts) at one store location (Anderson Rd.).

Figure 5-3: Using SUMIFS to total sales by location as well as the items sold.

In order to do this, I created the following formula in cell I8, using the SUMIFS function:

=SUMIFS(daily_sales,item_sold,"Lemon tarts",store,"Anderson Rd.")

In this formula, the sum_range argument (specified first and not last as in SUMIF) is still the daily_sales cell range (G3:G62). The first criteria_range argument is item_sold (C3:C62) where the criteria is “Lemon tarts,” and the second criteria_range argument is store (B3:B62) where the criteria is “Anderson Rd.” When Excel evaluates the formula in cell I8, it applies both criteria so that the program ends up totaling only those daily sales where the item is Lemon tarts and the store location is Anderson Rd.

The formula in cell I9 immediately below in the worksheet shown in Figure 5-3 also uses the SUMIFS function, but this time applies just a single criteria in performing the summation. This formula sums the daily sales for any bakery item that is not a Strawberry pie:

=SUMIFS(daily_sales,item_sold,"<>Strawberry pie")

Because I prefaced the item Strawberry pie with the not (<>) operator (which can be placed before or after the open double quotation mark), Excel sums the sale of every item except for Strawberry pie.

Statistical Functions

Excel includes one of the most complete sets of statistical functions available outside a dedicated statistics software program. When you want to access these functions from the Ribbon’s Formulas tab instead of using the Insert Function dialog box, you need to click the More Functions command button and then highlight the Statistical option at the very top of the drop-down menu (or press Alt+MQS). Doing this displays a continuation menu listing all the statistical functions in alphabetical order.

The statistical functions run the gamut from the more mundane AVERAGE, MAX, and MIN functions to the more exotic and much more specialized CHITEST, POISSON, and PERCENTILE statistical functions.

In addition to the more specialized statistical functions, Excel offers an assortment of counting functions that enable you to count the number of cells that contain values, count the number that are nonblank (and thus contain entries of any kind), or count the cells in a given range that meet the criteria that you specify.

AVERAGE, MAX, and MIN

The AVERAGE, MAX (for maximum), and MIN (for minimum) functions are the most commonly used of the statistical functions because they are of use to both the average number cruncher as well as the dedicated statistician. All three functions follow the same syntax as the good old SUM function. For example, the syntax of the AVERAGE function uses the following arguments just as the SUM, MAX, and MIN functions do:

AVERAGE(number1,[number2],[…])

Just as in the SUM function, the number arguments are between 1 and 30 numeric arguments for which you want the average. Figure 5-4 illustrates how you can use the AVERAGE, MAX, MIN, and MEDIAN functions in a worksheet. This example uses these functions to compute a few statistics on the selling prices of homes in a particular neighborhood. These statistics include the average, highest, lowest, and median selling price for the homes sold in April and May 2016. All the statistical functions in this worksheet use the same number argument; that is, the cell range named home_price in C3:C7.

Figure 5-4: Home sales spreadsheet using common statistical functions.

The AVERAGE function computes the arithmetic mean of the values in this range by summing them and then dividing them by the number of values in the range. This AVERAGE function is equivalent to the following formula:

=SUM(home_price)/COUNT(home_price)

Note that this formula uses the SUM function to total the values and another statistical function called COUNT to determine the number of values in the list. The MAX and MIN functions simply compute the highest and lowest values in the cell range used as the number argument. The MEDIAN function computes the value that is in the middle of the range of values; that is, the one where half the values are greater and half are less. This is the reason that the median sales price (in cell C13) differs from the average sales price (in cell C10) in this worksheet.

Counting cells

Sometimes you need to know how many cells in a particular cell range, column or row, or even worksheet in your spreadsheet have cell entries and how many are still blank. Other times, you need to know just how many of the occupied cells have text entries and how many have numeric entries. Excel includes a number of counting functions that you can use in building formulas that calculate the number of cells in a particular region or worksheet that are occupied and can tell you what general type of entry they contain.

Building counting formulas

Figure 5-5 illustrates the different types of counting formulas that you can build to return such basic statistics as the total number of cells in a particular range, the number of occupied cells in that range, as well as the number of numeric and text entries in the occupied range. In this example spreadsheet, I gave the name sales_table to the cell range A1:C8 (shown selected in Figure 5-5).

Figure 5-5: A version of the home sales spreadsheet with various counting formulas.

I then used the sales_table range name in a number of formulas that count its different aspects. The most basic formula is the one that returns the total number of cells in the sales_table range. To build this formula in cell C10, I used the ROWS and COLUMNS information functions (see Book III, Chapter 6 for more on these types of functions) to return the number of rows and columns in the range, and then I created the following formula that multiplies these two values together:

=ROWS(sales_table)*COLUMNS(sales_table)

This formula, of course, returns 24 to cell C10. In the next formula, I calculated the number of these 24 cells that contain data entries (of whatever type) using the COUNTA function. This function counts the number of cells that are not empty in the ranges that you specify. The COUNTA function uses the following syntax:

COUNTA(value1,[value2],[…])

The value arguments (all of which are optional except for value1) are up to 30 different values or cell ranges that you want counted. Note that the COUNTA function counts a cell as long it has some entry, even if the entry is empty text set off by a single apostrophe (’). In the example shown in Figure 5-5, cell C11 contains the following COUNTA function:

=COUNTA(sales_table)

This formula returns 20 to cell C11. The next formula in the sample spreadsheet calculates the number of numeric entries in the cell range called sales_table. To do this, you use the COUNT function. The COUNT function takes the same arguments as COUNTA, the only difference being that COUNT counts a value or cell specified in its value arguments only if it contains a numeric entry.

Cell C12 contains the following formula for calculating the number of numeric entries in the Home Sales table range called sales_table:

=COUNT(sales_table)

Excel returns 11 to cell C12. Note that in calculating this result, Excel counts the five date entries (with the date of each sale) in the cell range B3:B7 as well as the six numeric data entries (with the selling prices of each home plus total) in the cell range C3:C8.

The next formula in the sample spreadsheet shown in Figure 5-5 uses the COUNTBLANK function to calculate the number of blank cells in the sales_table range. The COUNTBLANK function works just like the COUNTA and COUNT functions except that it returns the number of nonoccupied cells in the range. For this example, I entered the following COUNTBLANK function in cell C13:

=COUNTBLANK(sales_table)

Excel then returns 4 to cell C13 (which makes sense because you know that of the 24 total cells in this range, Excel already said that 20 of them have entries of some kind).

The last two counting formulas in the sample spreadsheet shown in Figure 5-5 return the number of text and nontext entries in the sales_table cell range. To do this, instead of counting functions, they use the ISTEXT and ISNONTEXT information functions as part of the IF conditions used in conjunction with the good old SUM function.

The first formula for returning the number of text entries in the sales_table range in cell C14 is

{=SUM(IF(ISTEXT(sales_table),1,0))}

The second formula for returning the number of nontext entries in the sales_table range in cell C15 is just like the one in cell C15 except that it uses the ISNONTEXT function instead of ISTEXT, as follows:

{=SUM(IF(ISNONTEXT(sales_table),1,0))}

The ISTEXT function in the formula in cell C14 returns logical TRUE when a cell in the sales_table range contains a text entry and FALSE when it does not. The ISNONTEXT function in the formula in cell C15 returns logical TRUE when a cell is blank or contains a numeric entry (in other words, anything but text) and FALSE when it contains text.

In both these formulas, the ISTEXT and ISNONTEXT functions are used as the logical_test arguments of an IF function with 1 as the value_if_true argument and 0 as the value_if_false argument (so that the cells are counted only when the ISTEXT or ISNONTEXT functions return the logical TRUE values). These IF functions are then nested within SUM functions, and these SUM functions, in turn, are entered as array formulas.

Note that you must enter these formulas in the worksheet as array formulas (by pressing Ctrl+Shift+Enter) so that Excel performs its counting calculations on each and every cell in the sales_table cell range. If you just enter the SUM formula with the nested IF and ISTEXT and ISNONTEXT functions as regular formulas, they would return 0 as the count for both text and nontext entries in the sales_table cell range. (See Book III, Chapter 1 for details on building array formulas.)

Counting occupied cells in entire rows, columns, and worksheets

You can use the COUNTA function to count the number of occupied cells in an entire row or column of a worksheet or even an entire worksheet in your workbook. For example, to count all the occupied cells in row 17 of a worksheet, you enter the following COUNTA formula:

=COUNTA(17:17)

If you want to find the number of nonblank cells in column B of the worksheet, you enter the following COUNTA formula:

=COUNTA(B:B)

To find out the number of occupied cells in the entire second worksheet of your workbook (assuming that it’s still called Sheet2), you enter this COUNTA formula:

=COUNTA(Sheet2!1:1048576)

Note that you can also enter the argument for this COUNTA function by designating the entire range of column letters (rather than the range of row numbers) as in:

=COUNTA(Sheet2!A:XFD)

However, Excel automatically converts the argument that specifies the range of columns to rows, using absolute references ($1:$1048576) as soon as you enter the COUNTA function in its cell.

When entering COUNTA functions that return the number of occupied cells in an entire row, column, or worksheet, you must be sure that you do not enter the formula in a cell within that row, column, or worksheet. If you do, Excel displays a Circular Reference Alert dialog box when you try to enter the formula in the worksheet. This happens because you are asking Excel to use the cell with the formula that does the counting in the count itself (definitely the type of circular logic that the program doesn’t allow).

Conditional counting

Excel includes a COUNTIF function that you can use to count cells in a range only when they meet a certain condition. The COUNTIF function takes two arguments and uses the following syntax:

COUNTIF(range,criteria)

The range argument specifies the range of cells from which the conditional count is to be calculated. The criteria argument specifies the condition to use. You can express this argument as a number, expression, or text that indicates which cells to count. When specifying a number for thecriteria argument, you don’t have to enclose the number in quotes. For example, in a cell range named table_data, to count the number of entries that contain the number 5, you enter the following COUNTIF formula:

=COUNTIF(table_data,5)

However, when specifying an expression or text as the criteria argument, you must enclose the expression or text in closed quotes as in “=5”, “>20”, or “New York”. So, if you want to use COUNTIF to find out how many cells in the table_data range have values greater than 5, you enter this version of the COUNTIF function:

=COUNTIF(table_data,">5")

When you want to use the COUNTIF function to find out the number of cells whose contents are equal to the contents of a particular cell in the worksheet, you just add the cell reference as the function’s criteria argument. For example, if you want to count the number of cells in the table_data range that are equal to the contents of cell B3 in the worksheet, you enter this formula:

=COUNTIF(table_data,B3)

However, when you want to specify an expression other than equality that refers to the contents of a cell in the worksheet, you must enclose the operator in a pair of double quotation marks and then add the ampersand (&) concatenation operator before the cell reference. For example, if you want to count how many cells in the table_data range have a value greater than the contents of cell B3, you enter this form of the COUNTIF function:

=COUNTIF(table_data,">"&B3)

Note that when specifying text as the condition, you can use the two wildcard characters: the asterisk (*) to represent an unspecified amount of characters and the question mark (?) to represent single characters in the COUNTIF function’s criteria argument. For example, to count all the cells in the table_data range whose text entries end with the word Street, you use the asterisk in the COUNTIF criteria argument as follows:

=COUNTIF(table_data,"*Street")

To count the cells in the table_data range whose text entries contain the word discount anywhere in the entry, you sandwich discount between two asterisks in the COUNTIF criteria argument as follows:

=COUNTIF(table_data,"*discount*")

To count the cells in the table_data range whose cell entries consist of any two characters followed by the letter y (as in day, say, pay, and so on), you use two question marks to stand in for the nonspecific characters followed by a y in the COUNTIF criteria argument, as in

=COUNTIF(table_data,"??y")

When using the COUNTIF function to find the number of cells, you can include other statistical functions as the criteria argument. For example, suppose that you want to know the number of cells in the table_data range whose values are less than the average value in the range. To do this, you insert the AVERAGE function in the COUNTIF criteria argument as follows:

=COUNTIF(table_data,"<"&AVERAGE(table_data))

Using specialized statistical functions

You can use the built-in statistical functions found on the Statistical continuation menu or located in the Statistical category in the Insert Function dialog box, both of which I discuss earlier in this chapter. Excel also offers a complete set of special analysis tools as part of the Analysis ToolPak and Analysis ToolPak - VBA add-ins.

The tools included in the Analysis ToolPak enable you to analyze worksheet data by using such things as ANOVA, F-Test, rank and percentile, t-Test, and Fourier Analysis.

To load these tool packs so that you can use their functions, you need to open the Add-Ins dialog box by clicking File  ⇒  Options  ⇒  Add-Ins and then clicking the Go button at the bottom of the Excel Options dialog box (make sure that Excel Add-ins is displayed in the Manage drop-down list box before you click Go). In the Add-Ins dialog box, you then click the Analysis ToolPak and Analysis ToolPak - VBA check boxes before clicking OK.

After that, you simply click the Data Analysis button that’s been added to the Analysis group on the Data tab of the Ribbon (Alt+AY2). Excel then opens the Data Analysis dialog box, as shown in Figure 5-6.

Figure 5-6: Selecting a statistical analysis tool added by the Analysis ToolPak.