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 fi 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.
Search JabSto ::




Custom Search