How to Find Max, Min, and Median Values with STOCKHISTORY in Excel
Question details
The user wants to calculate the maximum, minimum, median, and Nth values (and their corresponding dates) directly from the STOCKHISTORY function using a single algorithmic dynamic-array formula.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Analyzing historical stock market data logically within a single formula cell without generating external helper columns.
- Observed behavior
- The user needs an all-in-one algorithmic formula to process the arrays generated by STOCKHISTORY, but struggles to correctly retrieve specific dates tied to median or Nth values due to data ties and even-number median averages.
Ensure you are using Microsoft 365, as the STOCKHISTORY function is a connected data type not available in older standalone versions of Excel. Familiarize yourself with the LET function, which is required to efficiently store and manipulate array variables in memory.
Use the LET Function to Extract Max and Min Dates
Use the LET function to store the STOCKHISTORY array in memory, separate the dates and closing prices, and calculate MAX or MIN with their associated dates.
By combining the LET function with STOCKHISTORY, you eliminate the need for helper columns on your worksheet. You can separate the dates and closing prices into defined variables, then use FILTER or XLOOKUP to return the exact date when the highest or lowest price occurred.
Select an empty cell and type the beginning of the LET function to define your stock data. For example: =LET(data, STOCKHISTORY("MSFT", "1/1/2023", "12/31/2023"), dates, CHOOSECOLS(data, 1), prices, CHOOSECOLS(data, 2),
To find the date of the highest price, complete the LET function using XLOOKUP. Add the following as the calculation argument: XLOOKUP(MAX(prices), prices, dates))
Press Enter to execute the formula. Excel will store the STOCKHISTORY matrix entirely in memory and output only the date associated with the maximum or minimum closing price.
Calculate the Nth-Largest or Nth-Smallest Value
Use SORTBY and INDEX functions combined with the previously defined LET array to extract the Nth value in the historical dataset.
Try WPS Office for Powerful Data Analysis and Spreadsheets
While the STOCKHISTORY function requires an expensive Microsoft 365 subscription, WPS Office provides an incredibly robust, free spreadsheet tool that supports advanced dynamic arrays, the LET function, and comprehensive financial data analysis.
- 1. Download WPS Office: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install the Software: Run the downloaded installer file and follow the simple on-screen instructions to set up WPS Office on your computer.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing Excel workbooks to seamlessly continue your data analysis.
Frequently Asked Questions
Why can't Excel find a date for my MEDIAN STOCKHISTORY value?
If your STOCKHISTORY dataset has an even number of entries, the median is calculated mathematically by averaging the two middle values. Since this new averaged result does not necessarily correspond to an actual historical trading date, a single definitive date cannot be returned without defining specific rounding logic.
Can I use STOCKHISTORY without a Microsoft 365 subscription?
No, the STOCKHISTORY function relies on proprietary connected financial data services that are exclusive to Microsoft 365. Older standalone versions of Excel do not support this function natively.
How do I calculate the Nth earliest date using STOCKHISTORY?
You can use the SMALL function combined with the INDEX function to target the Nth smallest date directly from the date column array returned by the initial STOCKHISTORY call.
Why does my antivirus block spreadsheet templates containing STOCKHISTORY?
Strict antivirus software sometimes flags spreadsheets containing complex dynamic arrays or automated external web data connections as false positives. To avoid this, manually build the formula directly in a blank workbook rather than downloading external macro-enabled templates.
