Excel Tip 2 - Use SUMPRODUCT for Weighted Average

Sep, 24, 2012     By Vikram Murarka    2 comments


Here's another very important MS Excel tip, one that I got from one of our clients. It has made my life so much simpler. Thank you, Krishnan!

Need for Weighted Average

Let us say there is a company that exported goods worth $ 5 million and received payment at a Dollar-Rupee rate of 45.45 on 17-Aug-11. It then exported goods worth $ 1 million and received payment at a rate of 56.00 on 23-May-12.

What is the average rate at which the exports have taken place? You will be surprised at the number of people who reply saying 50.7250, the simple average of 45.60 and 56.00. However, as you know, the correct answer is actually 47.21, since the larger amount of $ 5 million was exported at the lower rate of 45.45 in August 2011, as compared to the smaller amount of $ 1 million which was exported at 56.00 in May 2012. The rate of 47.21, the weighted average rate calculated as (45.45 x 5 mln + 56.00 x 1 mln)/ 6 mln.

Not only do the export/ imports/ forward contract/ option transactions take place at different exchange rates, the transaction amounts are also always different. Therefore, there is always a need to calculate the weighted average rate at which forex transactions have taken place. Since the transaction amounts are different, calculating simple average is simply wrong.

Thankfully, while a few people do erroneously make do with a simple average, most people calculate the Weighted Average Exchange Rate. Unfortunately, the way most people (I was one of them) do the calculation is quite cumbersome.

Old Way of Calculating Weighted Average

Old Way of Calculating Weighted Average

The way I used to calculate weighted average earlier in Excel is as follows:

Calculate the Rupee equivalent of the Dollar amount
Sum the Dollar amounts
Sum the Rupee amounts
Divide the sum of Rupee amounts by the sum of Dollar amounts

 

SUMPRODUCT Way of Calculating Weighted Avg

SUMPRODUCT Way of Calculating Weighted Avg

Note the SUMPRODUCT function in the formula bar. The weighted average is now calculated as SUMPRODUCT(range with Dollar amounts, range with Export rates)/sum of Dollar amounts.

Much simpler, isn't it? Thanks are due to Krishnan, my client at Marico Ltd, and to MS Excel. I hope this function will be as useful to you as it is to me!

Vikram Murarka

Chief Currency Strategist at KSHITIJ.COM. Likes to look at the markets from many different angles. Weaves many conventional and unconventional technical analysis techniques and fundamental analysis into a global macro perspective. Likes to take the road less traveled.



Leave a Reply

  1. Good post, do you have any others on this topic?


Dollar Rupee Forecast. Get a Free Trial

TWEETS


9Aug17 Euro-Yen 129.05: Breaking rising trend line since 115 (Apr '17)? #Risk-Off? https://t.co/ZJ1pNNez5l https://t.co/ZJ1pNNez5l

14 days ago  

@VikramMurarka @ranajayb @Analyst_Viji And just one month later, Euro trades > 1.18. Tells me there is still a lot… https://t.co/4D88P0Dvgo

20 days ago  

02Aug17 Kospi 2429: Chances of correction down to 2300-2250. Profit-taking possible. https://t.co/peQKIXdiBs https://t.co/peQKIXdiBs

21 days ago  

27Jul17 EURSUD 1.1707: Coming off from 200-week MA Resistance at 1.18. Dip to 1.15-14? https://t.co/HtGS7rHm7N https://t.co/HtGS7rHm7N

26 days ago