Learn one useful thing today.
Find 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 External Physical Maturity Score Formula: Newborn Gestational Age Without Tone Exam
Estimate neonatal gestational age (days and weeks) in sedated, asphyxiated, or ventilated newborns using Orvar Finnström’s (1972/1977) 7-characteristic External Physical Maturity Score (7–28 points) without neuromuscular tone testing.
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 →Formula Auditing & ErrorsFix and Prevent #REF! Errors in VLOOKUP, INDEX, and Structural Edits
Reproduce #REF! when col_index_num or INDEX coordinates exceed table dimensions, guard column bounds dynamically with COLUMNS/ROWS, and prevent broken column counts with MATCH or XLOOKUP.
Read the guide →MathFix floating-point errors: why 1.33 - 1.3 is not 0.03 and how ROUND solves it
Prevent false reconciliation breaks caused by IEEE-754 binary decimals using explicit ROUND(..., 2) or ABS tolerance checks.
Read the guide →Lookup & ReferenceFixed annual date-band lookup: season labels and conventional zodiac bands
Use month/day thresholds for recurring annual categories, including bands crossing January 1.
Read the guide →Financial ModelingFixed-Declining Balance Depreciation (DB Rate, Partial First-Year & Final Stub)
Compute exact fixed-declining balance depreciation rates rounded to 3 decimal places, prorate partial first-year months, and close out final stub periods cleanly.
Read the guide →Logical & ConditionalFlag & Count Values NOT Between X and Y (OR & Two-Tail COUNTIF)
Detect cold-chain, blood-glucose, or machining readings that fall strictly outside an acceptable [Min, Max] window using OR(B2 < MIN(X,Y), B2 > MAX(X,Y)) and count total two-sided excursions via COUNTIF.
Read the guide →StatisticalFlag customers as New vs Returning on every order row
Compare each order date against MINIFS earliest purchase date per customer so unsorted order exports still classify accurately.
Read the guide →LogicalFlag original vs. first duplicate vs. repeat duplicates (Expanding COUNTIF)
Use an expanding range COUNTIF($A$2:A2, A2) to distinguish the 1st valid occurrence (=1), the exact 1st duplicate (=2), and 3rd+ repeats (>2).
Read the guide →LogicalFlag out-of-sequence, skipped, and duplicate invoice or serial numbers
Audit sequential check, voucher, and batch serial logs to classify exact "+1 OK" transitions versus missing-number gaps, duplicates, and reversals.
Read the guide →FinancialFlat add-on interest versus annual cash-flow IRR: a four-payment example
Compare four end-of-year flat-interest payments with a declining-balance loan and calculate the annual IRR of the cash flows.
Read the guide →Statistical & MathFleiss’ Kappa (kappa) Multi-Rater Categorical Agreement Formula
Calculate chance-corrected multi-annotator reliability Fleiss’ Kappa = (P_bar - P_e) / (1 - P_e) across N items rated by n >= 3 raters into k categories for ML data-labeling QA and clinical radiology panels.
Read the guide →StatisticalFlesch Reading Ease & Flesch-Kincaid Grade Level Readability Formulas
Calculate Flesch Reading Ease 206.835 - 1.015*(Words/Sentences) - 84.6*(Syllables/Words) and US school Flesch-Kincaid Grade Level 0.39*(Words/Sentences) + 11.8*(Syllables/Words) - 15.59 for editorial and compliance auditing.
Read the guide →Dynamic Arrays & SpillFlip Table Rows Upside-Down in Exact Reverse Order (SORTBY & SEQUENCE)
Invert any 1D list or multi-column 2D log table from bottom-to-top positional order—even when timestamps have ties or non-chronological text—using SORTBY(range, SEQUENCE(ROWS(range)), -1).
Read the guide →Advanced IC Packaging & Heterogeneous Integration, Flip-Chip C4 Underfill & DNP StrainFlip-Chip C4 DNP Shear Strain & Capillary Underfill Formula
Calculate Corner-Bump CTE Differential Displacement (um), Un-Underfilled Bump Shear Strain (%), Young-Laplace Gap Driving Pressure (kPa), and Washburn-Hele-Shaw Fill Time (s).
Read the guide →Statistical & Clinical AnalysisFLIPI Score Formula: Follicular Lymphoma International Prognostic Index (0 to 5)
Stratify newly diagnosed follicular lymphoma survival across Low (0–1), Intermediate (2), and High Risk (3–5) using Philippe Solal-Céligny’s 5-factor FLIPI score (NoLASH: Nodal areas > 4, LDH > ULN, Age >= 60, Stage III–IV, Hemoglobin < 12 g/dL).
Read the guide →Charts & Visual Data ModelingFloating Bar & Up-Down Column Span Chart Formula: Hidden Base & Positive/Negative Delta
Create Floating Bar (salary bands, temperature ranges) and Up-Down Floating Column charts using stacked series: Hidden Base = MIN(Start, End), Up Gain = MAX(0, End - Start), and Down Loss = MAX(0, Start - End).
Read the guide →Engineering & ScienceFlory-Huggins Polymer Solution Theory: Mixing Free Energy & Critical Chi (chi_c)
Calculate dimensionless Flory-Huggins lattice free energy of mixing Delta_Gm / (n_site * R * T) and the critical spinodal phase-separation parameter chi_c = 0.5 * (1 + 1 / SQRT(N))^2.
Read the guide →Mechanical Energy Storage, Composite Rotordynamics & UPS Grid Frequency RegulationFlywheel Kinetic Energy Storage & Rim Hoop-Stress Formula
Calculate High-Speed Vacuum Flywheel Usable Extractable Kinetic Energy (kWh) Across [RPM_min, RPM_max], Outer-Rim Linear Tip Speed (m/s), Thin-Rim Centrifugal Hoop Stress σ_θ = ρ*v² (MPa), and Composite Burst Safety Factor.
Read the guide →Machine Dynamics, Mechanical Press Flywheels & Rotational InertiaFlywheel Speed Fluctuation (Cs) & Moment of Inertia Formula
Calculate Mean Angular Velocity omega (rad/s), Required Mass Moment of Inertia I (kg·m²) for Target Speed Fluctuation Cs, Equivalent Thin-Rim Mass (kg), and Rim Tip Speed (m/s).
Read the guide →Autonomous Sensing, 77 GHz Automotive FMCW Radar & Signal ProcessingFMCW Radar Chirp Bandwidth, Beat Frequency & Range Formula
Calculate 77 GHz Frequency-Modulated Continuous-Wave (FMCW) Chirp Radar Range Resolution ΔR = c/(2*B), Chirp Slope S (MHz/µs), Target IF Beat Frequency f_b (MHz), and ADC-Limited Maximum Unaliased Detection Range (m).
Read the guide →StatisticalFORECAST function: project a linear trend from historical X and Y pairs
Estimate future demand or utility load along a least-squares linear regression line using FORECAST, and guard against flat X inputs.
Read the guide →FinancialForecast vs Actual Budget Variance: Favorable (F) vs Unfavorable (U)
Build a budget-versus-actual report with an explicit favorable-positive convention, validated account types, zero-base handling and separate amount, percentage and status outputs.
Read the guide →Statistical & MathForest Plot Meta-Analysis: Pooled Effect, 95% CI & Inverse-Variance Weights
Build clinical-trial Forest Plot summary rows by exponentiating log-odds/log-risk effects EXP(theta +/- 1.96 * SE), computing fixed-effect inverse-variance weights w_i = 1 / SE_i^2, and pooling theta_pooled = SUM(w_i * theta_i) / SUM(w_i).
Read the guide →