Taking physical rows before filtering
Interior empty-ID slots would consume the requested count.
Return the last N active records
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Use the eight source slots A2:D9 in their existing oldest-to-newest order. A slot is active when its ID in column A is nonempty. Return the last F2 active records as ID, Units and Team, with no headings, preserving their original relative order. Retain duplicate IDs, numeric zero and signed units. If F2 exceeds the active count, return every active record. If none are active, return No records in one cell. F2 must be a numeric integer from 1 through 8.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:I12. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Skip empty-ID slots, reorder the fields and take the final requested records without sorting or reversing them.
THE QUICK ANSWER01
Skip empty-ID slots, reorder the fields and take the final requested records without sorting or reversing them.
ƒx
=IF(SUMPRODUCT(--(A2:A9<>""))=0,"No records",LET(records,FILTER(A2:D9,A2:A9<>""),TAKE(CHOOSECOLS(records,1,4,2),-F2)))| G | H | I |
|---|---|---|
| A* | 7 | North |
| B? | -3 | South |
| 009 | 4 | West |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Filter before taking
The first source has six active records. Requesting three returns A*/7/North, B?/-3/South and 009/4/West. Empty-ID slots do not consume the requested count.
Define recent using source order
This bounded input is already oldest to newest. TAKE selects positions; it does not infer dates from IDs or Notes. A source ordered differently needs an explicit ordering step first.
Keep a short result
If only one active record exists and F2 is eight, return that single record. There are no invented filler observations. The unused output cells remain empty.
Edit within the input contract
Active IDs and teams use nonempty ASCII text up to 40 characters. Active records require numeric whole units within ±1,000,000. An empty-ID slot is excluded regardless of its other values. F2 must be a numeric integer from 1 through 8. Keep G5 and the reserved spill area clear.
Read the full step-by-step walkthrough
- Decide whether a record exists
A nonempty ID defines an active slot. Count those slots rather than adding units, because signed units can cancel.
- Return a state when no record exists
IF returns one text cell when the active count is zero. The normal branch can then filter and select actual records.
- Remove empty-ID slots before taking rows
FILTER preserves source positions and skips holes anywhere in the eight-slot input.
- Reorder the selected fields
CHOOSECOLS returns ID, Units and Team from columns 1,4,2. The Note field stays in the source but is absent from the report.
- Take records from the end
A negative row count asks TAKE for the final records. The retained records keep their original relative order.
Things to watch
Returning the first N records
A positive row count reads from the start. This task asks for the last active records.
Reversing the selected rows
Taking from the end does not mean newest-first output. Preserve their original order.
About this example
Original bounded source arrays; current browser calculations and complete spills are checked against independent source rules. Native client recalculation and edit/save are separate gates.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.