One question that seems to come up frequently is “Is there a weighted average Excel formula?” You could use a formula like this to figure the average price of a group of items that you might purchase at the grocery store. Instead of just considering the average of all of your purchases, you should be more worried about the adjusted average based on the importance of each item on your list. This is known as the weighted average.
To start with you have to realize that there is not a specific weighted average Excel formula, you will find it quite easy to do using the SUMPRODUCT() function that is already built into the spreadsheet. SUMPRODUCT () ranges two arrays (multiplies) together and will then supply the sum of the product. It can be expressed in the formula {=SUM (A1:A5*B1:B5)/SUM (B1:B5)}. In this formula A1:A5 represent the quantities and B1:B5 the price. Leave out the curly brackets when you go to actually place this formula in a cell, they are only used to show that this is an array formula.
Once you have entered this formula in the appropriate cell, you press CTRL+SHIFT+ENTER at the same time and the formula will give you the average cost of any item. If you want to find the total cost, you use the formula {=SUM (A1:A5*B1:B5) to get the weighted average you will need to use the SUMPRODUCT function and this will also need to be entered as an array function.
The Excel formula for weighted average would read like this: {=sumproduct(A1:A5*B1:B5)/SUM(B1:B5)}. These are all array formulas, which means simply that they are designed to perform a specific array of functions before providing a final answer based on the information that has been entered into all of the included cells.
The best way to learn to use this type of weighted average Excel formula is to look online for a good tutorial. You will find that there are several that will take you by the hand and lead you step by step through the entire process. One thing to keep in mind is that while this may look like a very complicated formula and in fact as far as Excel formulas go, it is , once you have inputted it into your spreadsheet, the only thing you will need to in the future is to enter the relevant data and let the spreadsheet provide you with the answers you need.
