Next, determine the standard deviation of each asset, and it is computed on the basis of the mean and actual return of each asset.The weight of the i th asset is denoted by w i. Firstly, determine the weight of each asset in the overall portfolio, and it is calculated by dividing the asset value by the total value of the portfolio.So, check out the course summary at the end, and best of all, explore Excel 2013 on your own.The portfolio variance formula of a particular portfolio can be derived by using the following steps: Now, you have a good idea about how to average numbers in Excel. If we had just averaged the Test scores, the value would be 75.5, a significant difference.įor more information about the SUMPRODUCT and SUM functions, see the course summary. To get the Weighted Average, you divide by the Total of the weights. SUMPRODUCT is essentially the Sum of Test 1 times its weight, plus the Mid-term times its weight, and so on. We then divide the outcome of SUMPRODUCT by the SUM of the weights.Īnd this returns the Weighted Average of 80. The SUMPRODUCT function multiplies each Test’s score by its weight, and then, adds these resulting numbers. We’ll use the SUMPRODUCT and SUM functions to determine the Weighted Average. In this example, the Mid-term and Final exams have a greater weight than Tests 1 and 2. With a Weighted Average, one or more numbers is given a greater significance, or weight. The numbers are added together and then divided by the number of numbers, as in this example, which returns an unweighted average of 5. Usually when you calculate an average, all of the numbers are given equal significance. The formula works by dividing the total cost of the two orders by the total number of cases ordered.Ĭalculate the average of a group of numbers To get the correct average, use this formula to get the result (28 cents per shipment): The math doesn’t take into account that there are more cases being sold at 30 cents than at 20 cents. If you averaged the cost of each shipment this way (0.20+0.30)/2 = 0.25, the result isn’t accurate. But a second shipment of 40 cases costs 30 cents per case, because pencils are in high demand. Use the SUMPRODUCT and the SUM functions to find a Weighted Average, which depends on the weight applied to the values.įor example, a shipment of 10 cases of pencils is 20 cents per case. Usually when you calculate an average, all of the numbers are given equal significance the numbers are added together, and then, divided by the number of numbers.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |