Weighted Arithmetic Mean in Excel – SUMPRODUCT

Weighted arithmetic mean is a statistical value of broad use not only in mathematics or statistics. If you need to calculate weighted arithmetic mean in Excel, here is a guide

How Works Function SUMPRODUCT and Weighted Arithmetic Mean in Excel

Example: Lets have few loans where every loan has different principal and interest rate. The task is to calculate average interest rate of loans.

Basic arithmetic mean gives us 9,8% which does not make sense since all loans have different weight (exploitation). Basic arithmetic mean influences last loan only up to 1 000 with high interest rate.

Solution is to use weighted arithmetic mean where the weights is Principal. Function SUMPRODUCT is used to do just that. It will give us the correct rate for the whole loan portfolio.


Rate this post
Category: Excel functions Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody, banking and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.