Investors often struggle to extract precise historical performance when standard funding sources and basic brokerage exports lack customizable date filters. While traditional financial platforms offer only static reports, utilizing Excel's dynamic functions grants analysts the ultimate flexibility to isolate specific market cycles. This analytical power carries the stipulation that non-trading days and market holidays must be filtered out to prevent data skewing. For example, pairing the AVERAGE and STOCKHISTORY functions allows you to calculate the mean close of "MSFT" between two custom dates. Below, we will explore the exact formula syntax and step-by-step implementation.

Excel Formula To Average Stock Prices Between Two Dates

In financial analysis, tracking and analyzing stock price performance over specific periods is a fundamental task. Whether you are calculating the average cost basis of an asset, evaluating portfolio performance over a quarter, or smoothing out short-term market volatility to identify long-term trends, you often need to find the average stock price between two specific dates. Excel provides several highly efficient formulas to automate this process, saving you from manual filtering and calculations.

This comprehensive guide will walk you through the best methods to calculate the average stock price between two dates in Excel, ranging from the classic AVERAGEIFS function to dynamic modern solutions utilizing the power of the STOCKHISTORY function.

Method 1: The Standard Approach Using AVERAGEIFS

The most robust, straightforward, and widely compatible way to average stock prices between two dates in Excel is by using the AVERAGEIFS function. This function calculates the average (arithmetic mean) of all cells that meet multiple specified criteria.

The Formula Syntax

The general syntax for the AVERAGEIFS function is:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

To apply this to stock data, we need to instruct Excel to average the price column only if the corresponding date is greater than or equal to the start date, and less than or equal to the end date.

The resulting formula looks like this:

=AVERAGEIFS(Price_Range, Date_Range, ">=" & Start_Date, Date_Range, "<=" & End_Date)

Why the Ampersand (&) is Used

A common point of confusion for Excel users is the syntax ">=" & Start_Date. In Excel, logical operators (like >= or <=) must be enclosed in double quotes when used inside conditional functions like SUMIFS, COUNTIFS, or AVERAGEIFS. To link these operators to a cell reference containing your boundary dates, you must use the concatenation operator (&).

Step-by-Step Implementation with an Example

Let's look at a practical example. Suppose you have historical data for a stock stored in columns A and B, running from row 2 to row 100. Column A contains the dates, and Column B contains the daily closing prices.

• Column A (A2:A100): Transaction/Trading Dates
• Column B (B2:B100): Daily Closing Stock Prices
• Cell D2: Start Date (e.g., 2023-01-01)
• Cell E2: End Date (e.g., 2023-06-30)

To calculate the average stock price for this six-month window, enter the following formula into your destination cell:

=AVERAGEIFS(B2:B100, A2:A100, ">="&D2, A2:A100, "<="&E2)

Sample Data Table

Here is how your data and the formula output would look inside an Excel spreadsheet:

In this example, the formula only averages the values on 2023-01-05 ($151.00), 2023-01-06 ($149.50), and 2023-01-09 ($155.00), resulting in an average of $151.83. The dates before January 5th and after January 9th are completely ignored.

Method 2: Dynamically Fetching and Averaging Prices with STOCKHISTORY

If you are using Microsoft 365 or Excel for the Web, you don't even need to maintain a static historical table of stock prices manually. You can leverage the incredibly powerful STOCKHISTORY function to fetch live financial market data dynamically and average it on the fly.

By default, STOCKHISTORY returns a table containing dates and closing prices. Since we only want to average the closing prices, we can use the INDEX function to extract only the price column and wrap it inside the AVERAGE function.

The Dynamic Formula

=AVERAGE(INDEX(STOCKHISTORY("AAPL", D2, E2, 0, 0, 1), 0, 1))

How this formula works step-by-step:

1. STOCKHISTORY("AAPL", D2, E2, 0, 0, 1): This calls the stock history for Apple Inc. (AAPL) starting from the date in D2 to the date in E2.
   • The first 0 specifies "daily" intervals.
   • The second 0 tells Excel not to return headers (which avoids mixing text headers with numerical prices).
   • The final 1 tells Excel to only pull the "Close" price column.
2. INDEX(..., 0, 1): Since STOCKHISTORY naturally returns a 2-column array (Date and Close Price) even when only Close Price is requested, INDEX with a row argument of 0 extracts the entire first data column (the stock prices).
3. AVERAGE(...): This averages the numerical array of prices extracted by the INDEX function.

This dynamic method ensures that your calculations remain completely up-to-date without needing to paste or update historic stock databases inside your Excel sheets manually.

Handling Pitfalls and Error Troubleshooting

When working with date-based criteria and financial datasets in Excel, minor errors can break formulas or return incorrect averages. Below are the most common issues and how to easily resolve them.

1. Preventing the #DIV/0! Error

If there are no dates in your dataset that fall within your specified date range, the AVERAGEIFS function will return a division-by-zero error (#DIV/0!). This is highly common during weekends, market holidays, or when there is a typo in your boundary dates.

To keep your spreadsheet clean and functional, wrap your formula in an IFERROR function:

=IFERROR(AVERAGEIFS(B2:B100, A2:A100, ">="&D2, A2:A100, "<="&E2), "No Data in Range")

Now, instead of an ugly error, Excel will print a clean message indicating that no trading data was found for that specific interval.

2. Handling Non-Trading Days (Weekends and Holidays)

Stock markets are closed on weekends and federal holidays. If your start or end date lands on a Saturday, Sunday, or Thanksgiving Day, there won't be a price entry. The beauty of AVERAGEIFS is that it naturally ignores blank cells or missing dates in its calculations. It simply sums the existing transaction dates and divides by the count of active trading days found in your range.

3. Ensuring Proper Date Formatting

If your formula returns #DIV/0! but you are certain that dates exist in the range, check your date formatting. Excel stores dates as serial numbers. If your dates are stored as "Text", the logical operations (">=") will fail to recognize them.

The Fix: Select your date column, navigate to the Home tab, change the dropdown format to Short Date. If that doesn't fix it, use the DATEVALUE function to convert text strings to true Excel dates.

Alternative: Weighted Average Stock Price

If you are calculating the average stock price of actual purchases you made, a simple average (arithmetic mean) might not tell the whole story. You likely want to calculate a volume-weighted average price (VWAP) or your exact dollar-cost average basis.

For instance, if you bought 10 shares of a stock at $150 and 100 shares at $100, the simple average is $125. However, your actual cost basis is heavily weighted toward $100.

To calculate a weighted average between two dates, you must combine SUMPRODUCT with SUMIFS:

=SUMPRODUCT((A2:A100>=D2)*(A2:A100<=E2), B2:B100, C2:C100) / SUMIFS(C2:C100, A2:A100, ">="&D2, A2:A100, "<="&E2)

(In this weighted formula, Column B represents Price per share, and Column C represents the Volume or Quantity of shares purchased.)

Summary

Averaging stock prices over a specific timeframe is a crucial skill for tracking market trends, portfolio evaluation, and building robust financial models.

• For standard static datasets, use AVERAGEIFS due to its simplicity, speed, and absolute backwards compatibility.
• For real-time dynamic portfolio tracking in modern Excel (Microsoft 365), merge the STOCKHISTORY function with INDEX and AVERAGE.
• Always safeguard your analytical models using IFERROR to ensure missing trading data on weekends or holidays doesn't break your formulas.