Learn one useful thing today.
Filter and count values within or out of tolerance (Target ± Spec Band)
Extract passing parts inside [Target - Tol, Target + Tol] and count out-of-spec defects across manufacturing or audit logs.
Read the guide →Dynamic ArraysFilter and Sort Without Errors or Blanks (SORTBY + FILTER + ISNUMBER)
Build an error-proof leaderboard pipeline that strips blank cells and #DIV/0! formula errors via ISNUMBER(B2) flags before sorting top performers descending with SORTBY + FILTER.
Read the guide →Date & TimeFilter and Sum Records from the Last N Weeks (FILTER & SUMIFS Rolling Window)
Extract and total transactions falling within a rolling N-week lookback window anchored to a report cutoff date, excluding future-dated entries beyond the cutoff.
Read the guide →Water Treatment Hydraulics, Granular Media Filtration & FluidizationFilter Backwash Bed Expansion % & Fluidized Head-Loss Formula
Calculate Fluidized Backwash Expanded Bed Depth L_e (m), Percentage Bed Expansion (%), Constant Fluidized Buoyant Head Loss h_f (m water), and Underdrain Buoyant Pressure (kPa).
Read the guide →Dynamic Arrays & FilteringFilter Data Between Two Dates (Inclusive Boolean Multiplication & Fallback)
Extract rows whose date serials fall inclusively between Start_Date and End_Date using FILTER boolean multiplication (Date >= Start) * (Date <= End) with swap-safe MIN/MAX bounds.
Read the guide →Dynamic ArraysFilter dates expiring in the next 30 days (sorted renewal alert queue)
Extract contracts, certificates, or subscriptions expiring inside [AsOfDate, AsOfDate + 30] and sort them soonest-first with SORT + FILTER.
Read the guide →Dynamic ArraysFilter every Nth row: extract periodic intervals and audit samples
Extract every 2nd, 3rd, or Nth row from a production log into a live summary list using a relative row-index MOD flag and FILTER.
Read the guide →Dynamic arraysFILTER function: extract rows matching multiple conditions
Return every matching item dynamically instead of stopping at the first lookup match. Combine conditions with boolean multiplication and provide an if_empty message when zero rows qualify.
Read the guide →Dynamic ArraysFilter horizontal columns & sort left-to-right by row with FILTER and SORT
Filter a wide month-across-columns KPI table where revenue meets a threshold and sort matching columns highest-to-lowest using SORT(..., 2, -1, TRUE).
Read the guide →Dynamic ArraysFilter horizontally to exclude or keep specific columns (FILTER on headers)
Create a derived table that omits columns with specified header names using a horizontal FILTER mask.
Read the guide →Lookup & Dynamic ArraysFILTER Rows Matching This OR That Criteria with Boolean Addition (+)
Extract rows matching Condition A OR Condition B inside FILTER using elementwise boolean addition (condA) + (condB) > 0 instead of scalar OR().
Read the guide →Dynamic ArraysFILTER to show duplicate rows: inspect all collisions vs 2nd+ repeats
Extract every transaction row involved in a duplicate key collision (TotalCount > 1) so AP auditors can compare both copies side by side.
Read the guide →Dynamic Arrays & FilteringFilter Top N Records with Category Criteria (LARGE + FILTER vs TAKE + SORT)
Extract the Nth threshold, Top-N subtotal, and ranked leaderboard within a specific department or region using LARGE(FILTER(...), N) and TAKE(SORT(FILTER(...))).
Read the guide →Dynamic ArraysFILTER with optional criteria: treat blank filter inputs as "Match All"
Build interactive multi-dropdown FILTER dashboards where clearing any search box includes all rows instead of returning zero matches.
Read the guide →Text & String ManipulationFind and Replace Multiple Values in One Formula (Readable LET + SUBSTITUTE Pipeline)
Standardize multiple street, unit, or product abbreviations in a single formula without unreadable nested SUBSTITUTE pyramids by naming each replacement step inside LET.
Read the guide →Lookup & ReferenceFind candidate amounts within an inclusive tolerance band
Compare numeric amounts with a target and flag ambiguous matches before selecting a candidate.
Read the guide →Data Cleaning & AuditFind Common Duplicate Values Appearing in Both Columns (CRM vs ERP Audit)
Reconcile two invoice or SKU columns to flag records present in both lists versus missing from the second list using COUNTIF and dynamic FILTER.
Read the guide →Lookup & ReferenceFind First Match Between Two Ranges (Priority-Ordered List Intersection)
Return the highest-priority item from List A that also appears anywhere in List B using a COUNTIF membership flag with INDEX and FILTER, with a clean fallback when the two lists are disjoint.
Read the guide →Date & TimeFind highest & lowest value on a specific day of week (WEEKDAY, MAXIFS & MINIFS)
Use a Monday-first weekday helper to find maximum and minimum numeric readings, while distinguishing no matching dates from a genuine zero result.
Read the guide →Statistical & AggregationFind MAX & MIN Values While Ignoring All Errors (Even in Negative Ranges)
Extract the true maximum and minimum finite numbers from ranges containing #DIV/0! or #N/A errors using MAXIFS and MINIFS without failing on all-negative readings.
Read the guide →StatisticalFind maximum period-over-period jump, steepest drop & largest absolute swing
Find the largest and smallest signed adjacent-period changes, the largest absolute change, and its first matching period.
Read the guide →Lookup & ReferenceFind missing values between two lists: reconcile invoices with COUNTIF and FILTER
Flag items in List A that are absent from List B using COUNTIF or XMATCH, and spill an instant exception report with FILTER.
Read the guide →Lookup & ReferenceFind nearest value & closest location with ABS, MIN, and XMATCH
Locate the closest warehouse milepost, resistor value, or price point above OR below a target—unlike approximate VLOOKUP which only steps one way—using ABS distance.
Read the guide →Math & TrigFind skipped or missing invoice numbers in a sequence
Count total skipped integers between MIN and MAX and pinpoint exact gap ranges between sorted check or invoice rows.
Read the guide →Lookup & ReferenceFind the closest match to a target number (unsorted lookup)
Locate the nearest standard gauge, size, or setpoint—whether above or below your target—using squared differences inside MIN and XLOOKUP.
Read the guide →Lookup & ReferenceFind the exact or next-larger capacity tier with XLOOKUP
Select the smallest listed capacity meeting a requested quantity and return a clear message when none qualifies.
Read the guide →Lookup & ReferenceFind the first cell that does NOT begin with a comment or test prefix
Skip variable-length metadata/comment header lines ("#", "//", "TEST-") in imported logs to locate the first real transaction row.
Read the guide →Lookup & ReferenceFind the first literal substring with XMATCH wildcard mode
Escape wildcard characters in a search token, then find its first case-insensitive occurrence and return the matched text.
Read the guide →Lookup & ReferenceFind the first negative projected balance with XLOOKUP
Return the first listed period with a negative projected cash or inventory balance. Sort periods chronologically before interpreting the result.
Read the guide →Lookup & ReferenceFind the First Non-Blank Value and Row Number in a Column (XLOOKUP + XMATCH)
Locate the earliest populated entry, its relative position, its worksheet row number, and the last non-blank entry in sparse columns—including legitimate 0 values.
Read the guide →Lookup & ReferenceFind the last matching revision and release tag with reverse XLOOKUP
Search an append-ordered revision log from bottom to top using a literal filename prefix and return the matching filename or release tag.
Read the guide →Lookup & ReferenceFind the Last Populated Row Number in Numeric, Text, and Mixed Ranges
Calculate the exact worksheet row number, relative table index, and bottom-most populated value across contiguous no-blank tables vs gapped mixed text/number logs.
Read the guide →TextFind the longest or shortest matching title with FILTER and character lengths
Return the longest or shortest text value within a specific category or department by pairing a LEN helper column with MAXIFS, FILTER, and XMATCH.
Read the guide →TextFind the longest or shortest text string in a column (LEN + XLOOKUP)
Audit maximum character width before database imports and retrieve the exact longest or shortest string overall or by category.
Read the guide →Lookup & ReferenceFind the lowest-value record within a group with FILTER and SORTBY
Filter records before ranking, use source order to break equal-value ties, and return a matching record with its minimum value.
Read the guide →Dynamic Arrays & LookupFind the Most Common Text Values (#1 Categorical Mode & Ranked Runner-Ups)
Extract the most frequent text label and second-most common category—where numeric MODE returns #N/A—using COUNTIF frequencies, MAX, MATCH, INDEX, and FILTER.
Read the guide →StatisticalFind the most frequently occurring text value in a column
Pair a COUNTIF frequency helper column with XLOOKUP(MAX(...)) to return the modal text category—where numeric MODE fails.
Read the guide →TextFind the Nth occurrence position of a character (SUBSTITUTE + FIND)
Locate the exact 1-based character index of the 2nd, 3rd, or Nth delimiter ("-", "/", "_") in a string using SUBSTITUTE's instance_num argument.
Read the guide →Lookup & ReferenceFind the row and column of a value in a 2D matrix (TOCOL + XMATCH)
Locate any unique SKU, employee badge, or peak MAX reading inside an R x C grid and return its Row Header, Column Header, or cell ADDRESS.
Read the guide →Cleaning imported dataFIND vs SEARCH: choose case-sensitive or wildcard text matching
Both functions return the character position where a substring starts, or #VALUE! when absent—but FIND enforces exact uppercase/lowercase while SEARCH ignores case and interprets * and ? wildcards.
Read the guide →Biostatistics & Survival AnalysisFine-Gray Competing-Risks Subdistribution Risk-Set Weight Formula
Compute Fine and Gray’s (1999) time-dependent subdistribution risk-set weights w_i(t) = S_cens(t) / S_cens(T_i) and contrast them against cause-specific Cox censoring.
Read the guide →Statistical & Clinical AnalysisFinnström’s Newborn Maturity Assessment: Background and Gestational Age Records
Learn the limits of newborn physical maturity assessments and use a spreadsheet to express an independently documented gestational age as completed weeks and days.
Read the guide →Date & TimeFirst-In and Last-Out attendance times from badge logs (MINIFS & MAXIFS)
Find the earliest and latest recorded swipes for an employee and date, and calculate their elapsed span.
Read the guide →Public Finance & Fiscal FederalismFiscal Equalization Grant & Flypaper Effect Formula
Calculate Representative Tax System (RTS) fiscal equalization transfers G_i = t_std * MAX(0, B_std - B_i) and model Arthur Okun’s "money sticks where it hits" Flypaper Effect.
Read the guide →Synthetic Fuels, Gas-to-Liquids (GTL) & Polymerization KineticsFischer-Tropsch ASF Chain-Growth Alpha & C5+ Selectivity Formula
Calculate Anderson-Schulz-Flory (ASF) Methane C1 Weight Selectivity (%), Liquid+Wax C5+ Selectivity (%), Heavy Paraffin Wax C21+ Fraction (%), and Daily C5+ Syncrude Tonnage (t/d).
Read the guide →FinancialFisher Equation formula: Exact Real Interest Rate ((1 + i) / (1 + pi) - 1)
Convert effective nominal and real returns over the same period using an explicit inflation factor, with guards against invalid price-level changes.
Read the guide →Statistical & Mathematical FormulasFisher z-Transformation & 95% Confidence Interval for Correlation (r)
Stabilize skewed Pearson correlation sampling distributions via Ronald Fisher’s exact logarithmic transform z = 0.5 * LN((1 + r) / (1 - r)) and back-transform 95% confidence bounds r = (EXP(2*z) - 1) / (EXP(2*z) + 1).
Read the guide →Statistical & Clinical AnalysisFitts’s Law (ISO 9241-9 Shannon Form): Index of Difficulty & UI Pointing Time Formula
Predict cursor, touch, and XR target acquisition time using Scott MacKenzie’s Shannon formulation of Paul Fitts’s Law: ID = LOG(D / W + 1, 2) bits and MT = a + b * ID seconds.
Read the guide →Data Validation & AuditingFix & Prevent #### Negative Date/Time Errors & #NULL. Space-Operator Typos
Eliminate Excel’s #### negative-duration display error with IF(End < Start, ...) guards and replace accidental space-separated #NULL! range intersections with comma unions.
Read the guide →Dynamic ArraysFix #CALC! & #SPILL. Dynamic Array Errors (Empty FILTER Guard)
Handle an empty FILTER result without confusing zero totals with undefined averages, and distinguish empty results from blocked spill output.
Read the guide →