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.
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.
- Consequently, we will have the average share price of all the shares.
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.
- As a result, we will have the average share price of the shares.
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
- Average Attendance Formula in Excel
- How to Calculate Average, Minimum And Maximum in Excel
- How to Calculate Average of Averages in Excel
- How to Calculate Average True Range in Excel
- How to Calculate Average Percentage in Excel
- How to Calculate Average Percentage of Marks in Excel
- How to Calculate Class Average in Excel
- How to Calculate Average Revenue in Excel
- How to Calculate Average Quarterly Revenue in Excel
- How to Calculate Average Length of Stay in Excel
- How to Calculate Average Price in Excel
