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

Get FREE Advanced Excel Exercises with Solutions!

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 the average share price in Excel which is in other words calculating the weighted average of the shares.


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.


How to Calculate Average Share Price in Excel: 2 Easy Ways

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 them. 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


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 Sum & Average with Excel Formula


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.


Download Practice Workbook

You can download the practice workbook here.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo