Excel FAQs: Common accounting calculations

Simon Hurst unearths and highlights some of the most commonly recurring themes from Excel Zone’s Any Answers.

Introduction

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.

Also covered:

  • 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?

 

Further information

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.