fancy Weighted average in Excel
Microsoft Excel allows you to achieve a whole of noteworthy calculations. The power of Excel enables businesspeople, scientists, students and researchers to achieve virtually almost any needed mathematical prognosis that involves algebra, geometry and statistics.
fancy Weighted average in Excel
Many Excel calculations are performed by constructing a formula from scratch, while others leverage something called a function. A function can be understanding of as a pre-configured formula that can transform numbers, text or both in desirable ways.
One common mathematical calculation you may need to achieve in Excel is that of the weighted average. A weighted mean is a way of averaging two or more numbers by treating some as more necessary or important than others (i.e., by weighting them differently).
For example, to find the mean price paid for a product that was sold in three different orders at different price points and with a different whole of units per order, as follows:
Order 1: x 200 units
Order 2: x 350 units
Order 3: x 150 units
If you plainly mean the prices (, , ), you will not get the strict answer, since each order had a different whole of units. Instead, you need to use a weighted mean in order to properly "weight" each price point by the whole of units sold to find out the mean price paid.
Here are two methods to reason the weighted mean in Excel, using the numbers in the above-mentioned example:
1. The first formula requires no knowledge of Excel functions, but it does wish a bit of pre-calculation. You need to start by calculating the percentage gift to the "weight" of each set of units sold. To do this, just add up the whole of units sold in each order to get the total of whole of units sold. Then, divide each units sold whole by the total units sold. In this case, the total units sold is 700, so the resulting percentage gift coefficients are: 0.285 (for the 200 units portion), 0.5 (for 350 units), and 0.214 (for 150 units).
Now, just multiply each percentage coefficient times its corresponding price, then add those products together. Here is how this formula looks (when doing this in Excel, substitute the cell names for the numbers below):
= ( * 0.285) + ( * 0.5) + ( * 0.214)
2. The second formula does not wish pre-calculation of the percentage gift coefficients of each price, but it does wish the combined use of two functions: Sumproduct and Sum. Here is how the formula looks:
=Sumproduct(H11:H13,I11:I13)/Sum(I11:I13)
(where H11, H12 and H13 contain the prices for each order and I11, I12, and I13 contain their respective whole of units sold).
Both methods yield selfsame results, which in this case is: .36 (rounded to the second decimal).
No comments:
Post a Comment