Excel FAQs: Common accounting calculations
Simon Hurst unearths and highlights some of the most commonly recurring themes from Excel Zone’s Any Answers.
Of the many features and functions used in accounting spreadsheets there are some recurring themes evident in the history of Excel Zones Any Answers and also in the comments to past articles.
How do I calculate weighted averages?
Weighted average calculations usually involve rows of items with multiple values – such as products in stock where we have a number of products in one column and a value per product in another. To calculate the average value of every item in stock we need to take account, not only of the value of each product line, but also the number of each item in stock. So our weighted average would be the sum of:
(Product 1 number in stock*Product 1 value) through to (Product x number in stock*Product x value)
All divided by the total number in stock. We could achieve this by adding a column to our stock list table calculating the total value of each product in stock then divide the total of this column by the total of the number of products in stock.
- How do I calculate mortgage repayments or other capital/interest equations?
- How can I find more advanced financial functions?
- Are there any other useful functions in the Analysis Toolpak?
- Use Excel SUM functions to calculate weighted averages
- Excel - weighted average formula
- ExcelZone Compendium: Using functions and formulae