'%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 Mark the Most Recent Event Year Using Excel Formulas
Question details
The user needs an Excel formula to identify and mark the row containing the latest year a specific company experienced an event, filtering by company ID, year, and event flag.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Analyzing company event data and extracting the most recent occurrence based on multiple conditions.
- Observed behavior
- The user wants a formula that outputs a specific marker (like 1) only on the row corresponding to the latest event year for each company, without manually checking each group.
Ensure your data is organized in clear columns (e.g., Company ID in Column A, Year in Column B, Event Status in Column C) and take note of the exact cell ranges before applying the formula.
Use MAXIFS and IF Functions to Identify the Latest Event Year
This is the most reliable method, using the MAXIFS function to find the highest year for a specific company under specific event conditions, then using the IF function to mark the matching row.
The MAXIFS function allows you to find the maximum value based on one or more criteria. By comparing this maximum year back to the current row's year, you can accurately isolate the latest event. Ensure you lock your data ranges with absolute references (dollar signs) so they do not shift when you drag the formula down.
Click on the first empty cell in your target column where you want the result to appear (for example, D2).
Type the following formula: =IF(MAXIFS($B$2:$B$27,$A$2:$A$27,A2,$C$2:$C$27,1)=B2,1,"") and press Enter. This assumes Column A is Company ID, Column B is Year, and Column C is the Event Value.
Click and drag the fill handle at the bottom-right corner of cell D2 downwards to copy the formula to the rest of the rows in your dataset.
Use Sorting and a Simple IF Formula
If your spreadsheet software does not support the MAXIFS function, you can sort your data first and use a simpler IF and AND formula to detect changes in the Company ID.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical and statistical functions like MAXIFS, IF, and AND. You can efficiently analyze large datasets, extract specific event years, and perform complex data filtering with a lightweight, user-friendly tool.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .csv file containing the company event data.
- 2. Enter the formula: Click on the target cell and type your =IF(MAXIFS(...)) formula into the formula bar.
- 3. Use AutoFill: Double-click the fill handle in the bottom right corner of the cell to instantly apply the formula to all rows.
Frequently Asked Questions
What if my version of Excel doesn't support the MAXIFS function?
If you are using an older version of Excel (like Excel 2016 or earlier), MAXIFS is not available. You can use an array formula instead. Enter =IF(MAX(IF(($A$2:$A$27=A2)*($C$2:$C$27=1),$B$2:$B$27))=B2,1,"") and press Ctrl+Shift+Enter to apply it.
Why is the formula just replicating the event variable instead of isolating the latest year?
This usually happens if the range references in the MAXIFS function are not properly locked with dollar signs ($), causing the ranges to shift as you drag the formula down. Double-check that your ranges are written as $B$2:$B$27 instead of B2:B27.
Can I highlight the row automatically instead of just marking it with a 1?
Yes. You can use Conditional Formatting for this. Select your entire data range, go to Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the logic =MAXIFS($B$2:$B$27,$A$2:$A$27,$A2,$C$2:$C$27,1)=$B2 and choose your desired fill color.
