To calculate an average price, add up all the prices and divide by the number of prices. That works when each price carries equal weight. When you’ve bought different quantities at different prices, you need a weighted average instead: multiply each price by its quantity, add those products together, then divide by the total quantity. The method you choose depends on what you’re averaging and why.
Simple Average Price
A simple average (also called an arithmetic mean) treats every price equally. You add all the prices together and divide by how many there are.
Formula: Average Price = Sum of All Prices / Number of Prices
Say you’re comparing gas prices at five stations: $3.10, $3.25, $3.15, $3.40, and $3.30. Add them up to get $16.20, then divide by 5. The average price is $3.24.
This method works well when you’re comparing prices across stores, tracking how a product’s price changes over time, or finding a ballpark figure. It falls short when the quantities behind those prices aren’t equal, because it gives the same importance to a $50 purchase of 2 units and a $50 purchase of 200 units.
Weighted Average Price
A weighted average accounts for the fact that you bought more at some prices than others. It’s the method you want for tracking your true cost basis on investments, calculating inventory costs, or figuring out what you actually paid per unit across multiple purchases.
Formula: Weighted Average Price = (Price₁ × Quantity₁ + Price₂ × Quantity₂ + …) / Total Quantity
Here’s a practical example. Suppose you bought shares of a stock three times:
- Purchase 1: 20 shares at $10 each = $200
- Purchase 2: 50 shares at $50 each = $2,500
- Purchase 3: 30 shares at $40 each = $1,200
Your total cost is $3,900, and you own 100 shares total. Divide $3,900 by 100, and your weighted average price is $39 per share. Notice this is different from the simple average of the three prices ($10 + $50 + $40 = $100 / 3 = $33.33), because you bought far more shares at the higher prices.
How Businesses Use Weighted Averages for Inventory
If you manage inventory, the weighted average cost method helps you figure out what your goods cost when you’ve purchased the same item at different prices over time. There are two approaches depending on how your business tracks stock.
In a periodic system, you calculate the average at the end of a reporting period. You take the total cost of all goods available for sale and divide by the total number of units available. That single average cost per unit is then applied to the items you sold and the items still in stock.
In a perpetual system (sometimes called the “moving average” method), you recalculate the average cost every time new inventory arrives. This gives you a more up-to-date picture but requires more ongoing bookkeeping. The two systems will often produce slightly different cost figures for the same transactions, because the perpetual method recalculates before each sale while the periodic method waits until the end of the period.
Volume Weighted Average Price (VWAP)
VWAP is a specialized calculation used in stock trading. It tells you the average price a security traded at throughout the day, weighted by the volume of shares traded at each price level. Traders use it to judge whether they got a good execution price on a trade: buying below VWAP is generally considered favorable, and selling above it is too.
Formula: VWAP = Sum of (Price × Volume for each period) / Total Volume
To calculate VWAP manually, break the trading day into time intervals (five minutes is common). For each interval, find the “typical price” by adding the high, low, and closing price for that interval, then dividing by three. Multiply that typical price by the volume traded during the interval. Add up all those values across every interval, then divide by the total volume for the day. Most charting platforms calculate this automatically, but understanding the math helps you interpret what the indicator is actually measuring.
How to Calculate Average Price in a Spreadsheet
For a simple average in Excel or Google Sheets, use the AVERAGE function. If your prices are in cells A1 through A10, the formula is:
=AVERAGE(A1:A10)
For a weighted average, the SUMPRODUCT function does the heavy lifting. If your prices are in column A and quantities are in column B (rows 1 through 10), use:
=SUMPRODUCT(A1:A10, B1:B10) / SUM(B1:B10)
SUMPRODUCT multiplies each price by its corresponding quantity, then sums all those products. Dividing by the total quantity gives you the weighted average price. One thing to watch: both ranges must have the same number of rows. If they don’t match, the formula returns an error. Also avoid referencing entire columns (like A:A), which can slow down your spreadsheet significantly.
Choosing the Right Method
Use a simple average when you’re comparing prices and each data point matters equally, like finding the average rent in your neighborhood from a list of apartment prices. Use a weighted average whenever quantities differ, which covers most real-world scenarios: tracking your average cost per share across multiple stock purchases, calculating inventory costs for your business, or figuring out your blended interest rate across multiple loans. The weighted version is almost always more accurate when money is involved, because it reflects how much you actually spent rather than treating a small purchase the same as a large one.