Need to calculate statistical averages? Microsoft Excel supplies several easy-to-use statistical functions for just such a purpose says bestselling computer book author Stephen L. Nelson
Excel provides useful functions for finding the mean, median, and mode of a data set. In general, the functions look at a set of values and then make the expected calculation. For example, the mean functions, which calculate averages, take the sum of the values in the set and divide it by the number of values.
AVERAGE
The AVERAGE function ignores cells that contain text, that are empty, or that contain logical values. To use the AVERAGE function, simply enter the data set range as the single argument using the following syntax:
=AVERAGE (data set range)
For example, if you wanted to find the average birth rate per 1,000 people in the United States in 1996 using the data in Figure 4-11, you would use the following formula:
=AVERAGE (C2:C52)
The function returns the value 14.27.
AVERAGEA
The AVERAGEA function includes cells containing text or logical values in the calculation. Excel includes cells containing the word TRUE as the value 1 in the calculation; it includes cells containing the word FALSE or any other text as the value 0.To use the AVERAGEA function, simply enter the data set range as the single argument in the function using the following syntax:
=AVERAGEA (data set range)
Median (MEDIAN)
The median is the middle value in a set of values. Half of the data in the set fall above this value and half fall below, so the median estimates the 50% quantile.
The MEDIAN function uses the following syntax:
=MEDIAN (data set range)
If the data set contains an even number of values, Excel averages the two middle values.
Mode (MODE)
The mode is the most-frequently occurring value in a set of values.
When Excel calculates the mode, it ignores empty cells and cells containing text or logical values. The MODE function uses the following syntax:
=MODE (data set range)
Geometric Mean (GEOMEAN)
If you have a set of n numbers, the geometric mean of the set is the nth root of the product of the numbers.
To find the geometric mean, enter the data set range as the sole parameter of the GEOMEAN function, which uses the following syntax:
=GEOMEAN (data set range)
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
