WPS Spreadsheets: Return the Correct New Business Year with a Formula
Question details
The user needs a spreadsheet formula that returns the project start year when it is marked as New Business, or the closest earlier qualifying year when it is not.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet contains year headings, New Business indicators, and a project start date. The user wants to identify the correct qualifying business year automatically.
- Observed behavior
- The desired result is the latest year heading that is no later than the project start year and has a New Business indicator of 1.
Verify that the year headings are in F5:J5, the corresponding New Business indicators are in F6:J6, and the project start date is in E6. Confirm that the year headings are stored as numbers and the indicator cells use 1 for qualifying years.
Use an array formula to return the latest qualifying New Business year
Use this solution when the year headings and New Business flags follow the referenced worksheet layout.
The formula checks for indicators equal to 1, excludes years later than the project start year, and returns the latest remaining year heading. In older spreadsheet versions, the formula must be confirmed as an array formula.
In WPS Spreadsheets, click the cell where you want the correct New Business year to appear.
Type =INDEX($F$5:$J$5,LARGE(IF((F6:J6=1)*(YEAR(E6)>=$F$5:$J$5),COLUMN(A:E)),1)) and adjust the ranges if your headings, flags, or start date use different cells.
Press Enter in current WPS versions. If your version requires legacy array entry, press Ctrl+Shift+Enter instead.
Confirm that the result is the project start year when that year is marked 1; otherwise, it should be the closest earlier heading marked 1.
Find the Correct New Business Year in WPS Spreadsheets
WPS Spreadsheets can evaluate the same Excel-compatible array formula and return the latest qualifying year from your headings and New Business indicators. Enter the formula, confirm it according to your WPS version, and verify the result against the project start date.
- 1. Open the worksheet: Open the workbook in WPS Spreadsheets and locate the year headings, indicators, and project start date.
- 2. Choose the output cell: Click the cell that should display the correct New Business year.
- 3. Enter the formula: Enter =INDEX($F$5:$J$5,LARGE(IF((F6:J6=1)*(YEAR(E6)>=$F$5:$J$5),COLUMN(A:E)),1)).
- 4. Confirm and review: Press Enter, or press Ctrl+Shift+Enter for a legacy array formula, then verify the returned year.
Frequently Asked Questions
What does the formula return when the project start year is marked as New Business?
It returns that project start year because the formula includes qualifying headings that are no later than YEAR(E6) and selects the latest matching year.
Why does the formula use F6:J6=1?
The expression F6:J6=1 identifies the years marked as New Business. If your worksheet uses TRUE, Yes, or another marker, replace 1 with the value used in your indicator cells.
Do I need to press Ctrl+Shift+Enter in WPS Spreadsheets?
Current WPS versions generally calculate this array formula after you press Enter. If your version treats it as a legacy array formula, confirm it with Ctrl+Shift+Enter.
Which cells should contain the year headings and project date?
In the example, year headings are in F5:J5, New Business indicators are in F6:J6, and the project start date is in E6. Update all three references if your layout differs.
What should I check if the formula returns an error?
Check that E6 contains a valid date, F5:J5 contains numeric years, and at least one corresponding cell in F6:J6 equals 1 for a year no later than the project start year.
