Excel 2019 Bible

365

C H A P T E R

16

Using Formulas for Statistical

Analysis

IN THIS CHAPTER

Working with weighted averages

Smoothing data with moving averages

Creating descriptive statistics

Creating frequency distributions

E

xcel is an excellent tool for performing statistical analysis, in part because of the many statis-

tical functions that it provides. In this chapter, you’ll look at formulas for performing statis-

tical analyses such as moving averages, descriptive statistics, and frequency distributions.

This book’s website, http://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.

To compute the weighted average, the percentage that each investment contributes to the total

value of the portfolio is multiplied by that investment’s rate of return. The SUMPRODUCT function

is ideal for multiplying two sets of values and summing each result. SUMPRODUCT takes up to 255

arguments separated by commas, but we need only two arguments for this formula.

=SUMPRODUCT((C3:C7/$C$8),D3:D7)

The first argument takes each investment’s value and divides it by the total value. This results

in five percentages that represent the weight of each investment. For the Roboto Bond Fund, the

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.