How to Calculate Average Share Price in Excel (2 Easy Ways)

The average share price is the weighted average of different shares. Excel does not have any default function to calculate the weighted average. So, in this article, we will show how to calculate average share price in Excel which is in other words calculating the weighted average of the shares.


Download Practice Workbook

You can download practice workbook here.


What Is the Weighted Average?

The weighted average is a neat way to calculate the average of the items that have different quantities as well as prices. For example, you bought 4 apples at a unit price of $1.5 and 6 oranges at a unit price of $2. Then your average cost will be the weighted average of these two costs.

Weighted Average =∑(Number of units* Unit Price)/Number of Total Units

In this case the average cost is =( $ 1.5*4 + $ 2*6)/10 = $ 1.8

So, The term “weighted average” refers to an average in which each of the quantities to be averaged is given a weight. We can determine the relative relevance of each quantity using this weighting. In this case, the weight was the number of units bought for each fruit.


2 Easy Ways to Calculate Average Share Price in Excel

In this article, we will discuss 2 effective ways to calculate the average share price in Excel. Firstly, we will use the SUMPRODUCT function to do the task. Then, we will manually insert the formula of the weighted average to calculate the average share price.


1. Using SUMPRODUCT  Function

The SUMPRODUCT function multiplies the values in the described ranges or arrays and then adds the multiplied values and returns it. In this method,   we will use the function to calculate the average share prices.

Steps:

  • Firstly, select the F5 cell and write the following formula,
=SUMPRODUCT(C5:C10,D5:D10)/C11
  • Then, hit Enter.

inserting formula to show how to calculate average share price in excel

  • Consequently, we will have the average share price of all the shares.

using sumproduct function to show how to calculate average share price in excel

Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)


2. Applying Formula Manually

In this method, we will manually use the weighted average formula to calculate the average share price. The quantity of the shares will be the weighting factor in this instance.

Steps:

  • To begin with, select the F5 cell and write the following formula,
=SUMPRODUCT(C5:C10,D5:D10)/C11
  • Then, hit the Enter button.

typing formula to show how to calculate average share price in excel

  • As a result, we will have the average share price of the shares.

manually applying weighted average formula to show how to calculate average share price in excel

Read More: How to Calculate Average in Excel (Including All Criteria)


How to Calculate Average Trading Price

The average trade price is the average price of all trades executed during a specific period of time. The average trade price is determined by adding up the prices of the transactions done over a specified time period and then dividing that total by the total number of trades.

The formula of the Average Trade Price is,

Average Trade Price, = ∑(Prices)/ Number of Trades

In this method, we will find out the average trade price for different trades.

Steps:

  • To start with, select the F5 cell and write the following formula down,
=D11/C11
  • Then, hit Enter.

  • As a result, we will have the average trading price of the trades.


Conclusion

In this article, we have discussed 2 different ways to calculate the average share price in excel. These methods will help users to get the average share prices of different shares and, since it is a weighted average, they will be able to understand which share has the greatest weight among all the shares. Thank you for your time. Follow ExcelDemy.com for more articles like this.


Related Articles

Adnan Masruf

Adnan Masruf

I am an engineering graduate. I graduated from Bangladesh University of Engineering and Technology(BUET), one of the top universities in Bangladesh from department of Naval Architecture & Marine Engineering with a major in structure. I am an avid reader of fiction especially fantasy. I also keep myself abreast of the recent developments in science and technology. I believe diligence will eventually pay off and luck tends to favor those who work hard.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo