'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Find the Maximum and Minimum Value for Each Year in Excel
Question details
The user wants to calculate the maximum and minimum values for specific years in a large dataset and retrieve the exact dates associated with those values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Analyzing a large yearly dataset to find peak and lowest metrics along with their exact occurrence dates, while avoiding complex array formulas.
- Observed behavior
- The user needs to extract the max and min values per year and return their exact dates ensuring correct date formatting, as incorrect formats are causing the formulas to return 0.
Ensure your dataset's date column contains valid Excel date-time values rather than text strings. If your dates are stored as text (e.g., '29.06.2018'), functions like MAXIFS and MINIFS will not recognize the year conditions and may return 0.
Use MAXIFS, MINIFS, and INDEX/MATCH to Find Yearly Extremes
This is the most efficient method to extract the maximum and minimum values for a specific year and accurately match their corresponding dates.
This approach uses the MAXIFS and MINIFS functions to filter data by year, replacing older and more complex array formulas. It relies on the DATE function to set the boundary criteria for the start and end of each year.
Create headers for your summary on a separate sheet or area. For example, enter the target year (e.g., 2018) in cell B1.
In cell B2, enter the formula: =MAXIFS(Sheet1!$B$2:$B$100000, Sheet1!$A$2:$A$100000, ">="&DATE(B1,1,1), Sheet1!$A$2:$A$100000, "<="&DATE(B1,12,31)). This checks the date column (A) against the first and last day of the year in B1, returning the highest value from column B.
In cell B3, enter the formula: =INDEX(Sheet1!$A$2:$A$100000, MATCH(B2, Sheet1!$B$2:$B$100000, 0)). This searches for the maximum value calculated in B2 and returns its corresponding date.
In cell B4, enter the formula: =MINIFS(Sheet1!$B$2:$B$100000, Sheet1!$A$2:$A$100000, ">="&DATE(B1,1,1), Sheet1!$A$2:$A$100000, "<="&DATE(B1,12,31)) to find the lowest value for that specific year.
In cell B5, enter the formula: =INDEX(Sheet1!$A$2:$A$100000, MATCH(B4, Sheet1!$B$2:$B$100000, 0)) to locate the date for the minimum value. You can then copy these formulas across multiple columns to calculate different years.
Use MAX and IF Array Formula for Older Excel Versions
If your spreadsheet software does not support the newer MAXIFS or MINIFS functions, you can achieve the same result using an array formula combining MAX and IF.
Easily Find Max and Min Values with WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced mathematical and lookup functions like MAXIFS, MINIFS, INDEX, and MATCH, allowing you to easily process and analyze large datasets without complex array formulas.
- 1. Open your dataset: Launch WPS Spreadsheet and open your existing workbook containing the date and value columns.
- 2. Verify date formats: Select your date column, right-click, choose 'Format Cells', and ensure it is formatted as a Date rather than General or Text.
- 3. Apply advanced functions: Enter the MAXIFS, MINIFS, and INDEX/MATCH formulas directly into your summary cells to instantly retrieve your yearly extremes.
Frequently Asked Questions
Why do my MAXIFS and MINIFS formulas return a value of 0?
This usually happens when the dates in your dataset are formatted as text instead of valid Excel date values. You must convert text strings (like "29.06.2018") into proper date formats so the formulas can successfully evaluate the year boundary conditions.
Can I find the absolute maximum value without specifying a date range?
Yes. If you do not need to filter your data by specific years, you can bypass MAXIFS and simply use the standard =MAX(range) function to find the absolute highest value in your entire dataset.
What happens if there are multiple dates with the exact same maximum value?
When using the standard INDEX and MATCH combination to find the corresponding date of the highest value, the formula is designed to return the date associated with the very first instance it encounters reading from top to bottom.
