101 Ready-to-Use Excel Formulas

Chapter 8: Common Statistical Analyses

Excel is an excellent tool for performing statistical analysis, in part because of the many statistical functions it provides. In this chapter, you look at formulas for analyzing data, such as averages, buckets, and frequency distribution.

You can download the files for all the formulas at www.wiley.com/go/101excelformula.

Formula 82: Calculating a Weighted Average

You use a weighted average to average values where each value plays a larger or smaller role in the whole set.

Figure 8-1 shows an investment portfolio. For each fund in the portfolio, the total value of the investment and the return on that investment appear. You 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 determine the total return, you can use the following formula:

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

How it works

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 you need only two arguments for this formula.

Figure 8-1: An investment portfolio with rates of return.

The first argument takes each ...