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.
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.
- Consequently, we will have the average share price of all the shares.
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.
- As a result, we will have the average share price of the shares.
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
- How to Calculate Average Quarterly Revenue in Excel (8 Ways)
- Calculate Average Numbers in Excel (9 Handy Methods)
- How to Do Subtotal Average in Excel (5 Suitable Ways)
- Calculate Average Revenue in Excel (6 Easy Methods)
- How to Calculate Average Growth Rate in Excel (3 Easy Methods)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
- How to Calculate Average and Standard Deviation in Excel