Using Worksheet Functions

Worksheet functions are built-in Excel formulas that perform calculations. An example of a simple function is the SUM formula which, as you might know, adds together the values in two or more cells. The result of the function =SUM(3, 2) is, of course, 5. In this case, the numbers 3 and 2 are called the function’s arguments, and the number 5 is called the function’s result. In technical terms, a function returns a result. The result a formula returns is displayed in the cell that contains the formula.

Very few meaningful data analysis tasks can be accomplished without calculations. Understanding how spreadsheet functions work is important; functions not only yield important business facts, but they also allow you to increase the range of data analysis tasks that you can perform. For example, by using spreadsheet functions you can

• Display the average insurance claim amount for a given year.

• Return the highest or lowest sales order without needing to also sort data values.

• Display values such as In Stock, Out of Stock, Back Ordered, or Must Reorder to highlight specific product stock levels.

Here’s a list of some popular business data analysis functions, the results they return, and a description of how you will likely use them in your daily data analysis tasks.

Function	Result
AND	Returns TRUE if all the arguments are true or FALSE if one or more of the arguments is false. This function is helpful for evaluating whether several data values, when taken as a group, meet specific business criteria. For instance, =AND(2+2=4, 1+3=3) returns FALSE.
AVERAGE	Returns the average (or mean) value of the arguments. For example, =AVERAGE(3, 2, 5, 7) returns 4.25.
CEILING	Returns a number rounded up to the nearest multiple that you specify. For instance, =CEILING(15.25, 0.50) returns 15.5, while =CEILING(15.25, 1) returns 16. This function, along with the FLOOR function described later, are ideal for rounding to specific financial increments, like eighths or quarters of points or dollars.
COUNT	From a group of cells, returns the number of cells that contain numbers. This function is helpful for ignoring cells that contain text values such as N/A or None. Contrast this function to the ISBLANK function described later.
COUNTIF	From a group of cells, returns the number of cells that match specific conditions. This function is helpful when you need a quick sum of matching data records but you don’t want to apply a filter, which could hide some of the data records.
FLOOR	In contrast to the CEILING function described earlier, returns a number rounded down to the nearest multiple that you specify. For instance, =FLOOR(15.25, 0.50) and =FLOOR(15.25, 1) both return 15.
FREQUENCY	Returns a vertical list of numbers, grouped into “bins” or “buckets,” that describes how often specific data values occur within a given list. This function is useful for grouping data values without sorting the original list of data records, which could disrupt the original list’s sort order.
IF	Based on a condition that you specify, returns one value if the condition is TRUE or returns another value if the condition is FALSE. This function is ideal for displaying word pair results such as In Stock/Out Of Stock, Yes/No, and so on. For example, =IF(1+1=3, “Correct”, “Incorrect”) returns Incorrect.
INT	Strips off the fractional part of a number. For instance, =INT(123.456) returns 123. This function is helpful in financial analyses where you want to remove portions of dollars from the result without rounding.
ISBLANK	Returns TRUE if the cell referred to is blank. This function is helpful for accounting for data-entry errors in which specific cells don’t contain data.
LARGE	Based on the values in a group of data values, returns the nth largest value using the position you specify. For example, =LARGE({2100, 3300, 1000, 5000, 4575}, 3) returns 3300, the third largest value in the group of numbers.
MAX	Returns the largest number in a group of data values, regardless of the number’s position. For instance, =MAX(5, 4, 3, 2, 1) returns 5.
MEDIAN	Returns the middle value in a group of data values. For example, =MEDIAN(99, 42, 50, 1, 3) returns 42. Note that the median is not the same as the mean (or average).
MIN	The opposite of the MAX function; returns the smallest number in a group of data values, regardless of the number’s position. For instance, =MIN (5, 4, 3, 2, 1) returns 1.
MODE	Returns the most frequently occurring number in a group of data values. For example, =MODE(5, 4, 3, 3, 2) returns 3. This function is helpful when you want to determine information such as the most frequently ordered product ID or product number.
OR	Similar to the AND worksheet function, returns TRUE if one or more of the arguments are true, or FALSE if all of the arguments are false. For instance, =OR(2+2=4, 1+3=3) returns TRUE.
PERCENTILE	Based on the percentile that you specify, returns the percentile value of a group of data values. For example, =PERCENTILE({1, 2, 3, 4, 5}, 0.9) returns 4.6. This function is helpful in determining which data values are above a certain cutoff point, such as a sales quota.
PERCENTRANK	Similar to the PERCENTILE function; returns where a specific data value ranks percentage-wise in a group of data values. For example, =PERCENTRANK({1, 2, 3, 4, 5}, 4) returns 0.75, or 75%. This function is very helpful for ranking data values next to each other for comparisons.
ROUND	Rounds a data value to a specified number of digits. For instance, =ROUND(15.755, 1) returns 15.8. This function is helpful for rounding when you don’t want to drop fractional portions of numbers but you want some degree of accuracy.
SMALL	The opposite of the LARGE function; based on the values in a group of data values, returns the nth smallest number using the position you specify. For example, =SMALL({3300,1000,2100,5000,4575}, 2) returns 2100, the second smallest number in the group.
SUMIF	Similar to the SUM function, but adds only values that match specific conditions. This function is helpful, for instance, if you need to sum data values for multiple sales promotion figures occurring in the same list.

Tip

Although the examples in the preceding table used actual data values, you can substitute cell addresses, and Excel will substitute the value of the cell when the function performs the calculation. For example, if you have the value 2 in cell A1 and the value 3 in cell A2, =SUM(A1, A2) would return 5.

Tip

For a group of cells that touch each other, you can specify the first cell’s address in the group, followed by a colon (:), followed by the last cell’s address in the group. For instance, A1:B3 refers to the group of cells A1, A2, A3, B1, B2, and B3.

Inserting a function into a worksheet cell is simple. First click the spreadsheet cell in which you want the function’s result to appear. Then, on the Insert menu, click Function. Select the function name and click OK. Fill in any empty boxes with data values, cell addresses, or other arguments, and then click OK to insert the function. Press Enter to run the function.

Functions are organized by category. If you know the function’s category, you can select the category in the Insert Function dialog box and then select the function’s name. (Excel 2000 presents only a list of function categories and function names. It doesn’t give you the ability to search for functions.) If you know the name of the function, you can type it in the Insert Function dialog box’s Search For A Function box, click Go, and then select the function’s name in the Select A Function list.

Tip

You can also display the Insert Function dialog box by clicking the Insert Function button next to the Formula Bar. If the Formula Bar is not visible, on the View menu, click Formula Bar to display it.

Your Turn

In this exercise, you will run various functions on several groups of cells to yield additional facts about your preferred customers. You can use the information to see whether you notice any trends or anomalies in your data.

1. Open Hotel.xls. If the file is already open, close it (do not save it) and open the file again.

2. In cell F2, type =SUM(D2:D313) and then press Enter. This returns the total amount of room service charges for the year ($147,683.80).

3. In cell F3, type =MAX(D2:D313) and then press Enter. This returns the highest monthly room service charge ($1,835.40).

4. In cell F4, type =MIN(D2:D313) and then press Enter. This returns the lowest monthly room service charge ($0.00).

5. In cell F5, type =AVERAGE(D2:D313) and then press Enter. This returns the average monthly room service charge ($473.35).

6. In cell F6, type =LARGE(D2:D313, 2) and then press Enter. This returns the second highest monthly room service charge ($1,794.55).

7. In cell F7, type =MEDIAN(D2:D313) and then press Enter. This returns the monthly room service charge midpoint ($384.10).

8. Add descriptions for each of these results in column E. For example, in cell E2 type Sum of All Room Service Charges. Compare your results to Figure 3-9.

   
   Figure 3-9: Summary of room service charges.

   What anomalies or trends do you notice based on this exercise? For example:

• Some preferred customers had no room service charges in some months.

• The average monthly room service charge was about 23 percent higher than midpoint.

What would you do, if anything, as the hotel chain’s marketing specialist, to influence these findings in the future? To answer this question, of course, you would need to know in a more in-depth manner than this sample scenario how the hotel deals with aspects of room service and how it treats its customers.