Function Problems

How to Find the Maximum STOCKHISTORY Value While Ignoring Errors in Excel

Kushani Nimanthika Sep 30, 2026 870 views

Question details

The user wants to calculate the highest historical stock price from a dataset generated by the STOCKHISTORY function, but needs a way to bypass #N/A errors caused by non-trading days or missing market data.

How to Find the Maximum STOCKHISTORY Value While Ignoring Errors

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing historical financial data to find the peak stock value over a specific period where the raw data contains error values due to market closures.
Observed behavior: Applying the MAX function directly to the STOCKHISTORY array results in an error if the array contains any #N/A values from unavailable prices.

Before you start

Ensure you have an active internet connection so the STOCKHISTORY function can fetch financial data, and verify your stock ticker symbol and date ranges are formatted correctly.

Solution 1Recommended

Use IFERROR to Replace Missing Data with Zero

Wrap the STOCKHISTORY function inside an IFERROR function to handle missing data gracefully before calculating the maximum value.

When STOCKHISTORY attempts to retrieve price data for weekends or market holidays, it frequently returns an #N/A error. By nesting this array inside an IFERROR function, you can automatically convert these errors into zeros. This allows the encompassing MAX function to evaluate the remaining valid numerical prices without failing.

Select the target cell

Click on the cell where you want the maximum stock price to be displayed.

Enter the nested formula

Type the formula structure: =MAX(IFERROR(STOCKHISTORY("Ticker", "Start_Date", "End_Date",,,1), 0))

Customize the parameters

Replace the placeholders with your specific stock symbol and dates. For example: =MAX(IFERROR(STOCKHISTORY("MSFT","01-01-2024","30-06-2024",,,1), 0))

Execute the formula

Press Enter to calculate the highest stock price for the specified period. The formula will automatically treat unavailable dates as 0, ignoring the errors.

Use IFERROR to Replace Missing Data with Zero

Dynamic Array Compatibility: In modern versions of Excel that support dynamic arrays, this formula works natively and does not require pressing Ctrl+Shift+Enter.

Solution 2

Calculate Maximum Using the AGGREGATE Function

If your STOCKHISTORY data is already spilled into a range of cells, use the AGGREGATE function to extract the maximum value while ignoring errors.

Free Microsoft Office alternative

Experience Powerful Spreadsheet Analysis with WPS Office

While connected financial data types like STOCKHISTORY are unique to Microsoft 365 subscriptions, WPS Office provides a robust, free, and highly compatible alternative for your broader data analysis needs. Seamlessly open your existing workbooks, execute advanced array formulas like MAX and IFERROR, and perform complex analytics without heavy subscription fees.

1. Download and Install: Download WPS Office from the official website and complete the quick installation process.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx files directly.
3. Apply Formulas: Use standard financial formulas, IFERROR, and MAX functions exactly as you would in Microsoft Excel.

Free, lightweight, and fast installation processHigh compatibility with Microsoft Excel (.xlsx) files and standard array formulasBuilt-in advanced financial and statistical functions for professional data analysisFamiliar user interface ensuring a seamless, zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Why does STOCKHISTORY return an #N/A error for certain dates?

The #N/A error typically occurs when the requested date falls on a weekend, a recognized market holiday, or when the external financial data provider lacks historical pricing information for that specific day.

Can I use MAXIFS instead of IFERROR to ignore these stock errors?

MAXIFS requires physical ranges rather than the in-memory arrays generated directly by the STOCKHISTORY formula. To evaluate an in-memory array directly within a single cell, combining MAX and IFERROR is the most efficient approach.

Will the IFERROR method affect the legitimate stock prices?

No. The IFERROR function only intercepts error values (like #N/A or #VALUE!). It leaves legitimate numerical stock prices intact. By replacing the errors with 0, the MAX function simply ignores them since a stock price is inherently greater than 0.

Excel & Data Problems