How do I use Excel to calculate a weighted average?
A weighted average is simply a method of determining the mean of a set of data in which certain points occur multiple times or in which certain points are valued more highly than others. Though the method of determining weights may vary, weighted averages are used in the calculation of a variety of technical indicators and financial metrics.
The earnings per share (EPS) metric, for example, is a method of corporate valuation used by analysts to determine the profitability of a potential investment. It is calculated by dividing the company's earnings for a given period by the number of common shares outstanding. Assume a company has 150,000 outstanding shares at the beginning of the year but buys back half of them in September, leaving only 75,000 at the end of the year. Neither of these figures accurately reflects the number of shares outstanding for the entire year, so the weighted average is calculated to determine how many shares to use in the computation of the EPS value for that period.
To calculate this weighted average using Microsoft Excel, first input the two values for the number of shares outstanding into adjacent cells. In January, there were 150,000 shares, so this value is entered into cell B2. In September, half of these shares were repurchased by the company, reducing the number of outstanding shares to 75,000. Enter this value into cell B3. In the next row, input the number of months for which these values held true. The initial number of shares was maintained from January to September, or nine months, meaning there were only 75,000 shares outstanding for the remaining three months of the year. These values can be put into cells C2 and C3, respectively. In cell D2, input the formula =C2/12 and copy the formula into cell D3 to render the respective weights of each of the values in row B. Finally, in cell E2, input the formula =(B2*D2)+(B3*D3) to render the weighted average. In this example, the weights are calculated as 0.75 and 0.25, respectively, and the weighted average number of shares outstanding is 131,250.