Microsoft Office Tutorials and References

In Depth Information

**Working with Functions**

The advantage of using cell references is that the values used in the function are

visible to users and can be easily edited as needed. Functions can be included as part of

larger formulas. For example, the following formula, which includes the MAX function,

returns the maximum value from the range A1:A100, and then divides that value by 100:

=MAX(A1:A100)/100

Functions can also be placed inside another function, or
nested
. If a formula contains

several functions, Excel starts with the innermost function and then moves outward. For

example, the following formula ﬁ rst calculates the average of the values in the range

A1:A100 using the AVERAGE function, and then extracts the integer portion of that value

using the INT function:

=INT(AVERAGE(A1:A100))

One challenge of nesting functions is to make sure that you include all of the

parentheses. You can check this by counting the number of left parentheses, and making sure

that number matches the number of right parentheses. Excel will also display different

levels of nested parentheses in different colors to make it easier to match the opening

and closing parentheses in the formula. If the number of parentheses doesn’t match,

Excel will not accept the formula and will offer a suggestion for rewriting the formula so

the number of left and right parentheses does match.

Problem Solving: Choosing the Right Summary Function

Problem solving involves determining how to best summarize a large sample of data into a

few easy-to-use statistics. The field of statistics provides several summary measures, each

with its own advantages and disadvantages.

One of the most common statistical approaches is to average the sample data. You

can calculate the average in Excel with the AVERAGE function. However, determining an

average is not always the best choice. Averages are susceptible to extremely large or

small data values. Imagine calculating the average size of homes in a neighborhood that

has one huge mansion and several small houses. In this case, the average value is heavily

influenced by the size of that single mansion, and might not represent a typical

neighborhood house.

When the data includes a few extremely large or extremely small values that have the

potential to skew results, it’s often better to use the
median
, or middle, value from the

sample. For example, in a survey of nine homes, the median would be the size of the fifth

largest—or middle-sized—home in the sample. You can calculate the median in Excel with

the MEDIAN function.

Another approach is to calculate the most common value in the data, otherwise known

as the
mode
. The mode is most often used with data that has only a few possible values,

each of which might be repeated several times. Rather than using square feet, you might

want to express the value in terms of the number of bedrooms each home contains. The

mode would return the most common number of bedrooms in the homes included in the

sample. You can calculate the mode in Excel using the MODE function.

By knowing which summary measure best fits your data, you can create useful and

precise information that will aid you and others in interpreting the results.