Excel 2019 Bible

CHAPTER 16Using Formulas for Statistical Analysis

IN THIS CHAPTER

• Working with weighted averages
• Smoothing data with moving averages
• Creating descriptive statistics
• Creating frequency distributions

Excel is an excellent tool for performing statistical analysis, in part because of the many statistical functions that it provides. In this chapter, you'll look at formulas for performing statistical analyses such as moving averages, descriptive statistics, and frequency distributions.

This book's website, www.wiley.com/go/excel2019bible, includes a copy of the sample workbook for this chapter. The file is named Statistical Analysis.xlsx.

Working with Weighted Averages

A weighted average is used to average values where each value plays a larger or smaller role in the whole set. Figure 16.1 shows an investment portfolio. For each fund in the portfolio, the total value of the investment and the return on that investment are shown. We want to determine the total return on the portfolio. A simple average won't do because each investment contributes a different amount to the whole portfolio.

FIGURE 16.1 An investment portfolio with rates of return

To compute the weighted average, the percentage that each investment contributes to the total value of the portfolio is multiplied ...