Learn one useful thing today.
Remove file extension from a filename (multi-dot safe)
Search from the end of a filename with instance_num = -1 in TEXTBEFORE and TEXTAFTER so version dots like .v2.final stay intact.
Read the guide →TextRemove first N or last N characters from a cell
Strip fixed-width prefixes, suffixes, or both from product codes safely without #VALUE! errors on short strings.
Read the guide →TextRemove leading zeros from numbers and alphanumeric codes ("000408-B" -> "408-B")
Convert zero-padded numeric text with VALUE or strip only leading zeros from alphanumeric SKUs while preserving internal zeros.
Read the guide →TextRemove non-numeric characters (keep digits only, or strip all digits)
Extract only digits 0–9 from messy IDs ("Order #482-B9" -> "4829") or strip all digits to isolate text labels using double-TEXTSPLIT.
Read the guide →TextRemove special characters from text (and generate clean URL slugs)
Strip punctuation symbols in a single pass or convert product titles into hyphenated URL slugs using TEXTSPLIT array delimiters and TEXTJOIN.
Read the guide →Text & String CleaningRemove Text Between Variable Delimiter Positions (REPLACE & FIND)
Excise variable-length middle segments—such as bracketed revision tags "[REV-204B]" or internal cost codes—regardless of length by feeding FIND start and end positions into REPLACE(text, start, end - start + 1, "").
Read the guide →Text & Data CleaningRemove the First Character or Strip the Last Word from Text Strings Safely
Trim leading symbol prefixes ("#INV-408" -> "INV-408") and strip trailing titles/units ("Elena Petrova PhD" -> "Elena Petrova") without #VALUE! errors on single-character or single-word cells.
Read the guide →Date & TimeRemove Time from Datetime Serials (INT, TRUNC & Pure-Date Matching)
Remove the time fraction from positive spreadsheet datetime serials, extract time of day, and sum a calendar day using an inclusive start and exclusive next-midnight boundary.
Read the guide →Text & Data CleaningRemove Trailing Slash from URLs for Canonical SEO & Lookup Deduplication
Normalize exported sitemap and analytics URLs by trimming spaces and stripping trailing slashes without corrupting bare protocol prefixes or already clean URLs.
Read the guide →Project Finance, Renewable Energy & Tax-Equity StructuringRenewable Tax-Equity Partnership Flip PTC & Cash Formula
Calculate Utility-Scale Renewable Tax-Equity Partnership Flip Gross Production Tax Credits ($M), Pre-Flip 99% Investor Tax Benefit ($M), and Post-Flip 5% Safe-Harbor Residual Cash Split ($M).
Read the guide →FinancialRent-free months: average monthly rent and discounted payment schedule
Model a 36-month rent schedule with its first three months free, and discount the actual remaining payments rather than an unabated annuity.
Read the guide →VLSI Packaging, Chiplets & FPGA ArchitectureRent’s Rule IC Pin Count & Chiplet I/O Formula
Calculate Landman-Russo Rent’s Rule Signal Terminal Count T = k * N_gates^p, External I/O Pins per Gate Locality Ratio, Package Micro-Bump Surplus/Deficit, and Pin-Limited vs Core-Limited Die Status.
Read the guide →Lookup & ReferenceRepeat a 1..N number sequence & round-robin assign leads from a list (MOD & INDEX)
Cycle through non-empty names in a fixed roster range. Learn how roster changes affect calculated assignments.
Read the guide →Lookup & ReferenceRepeat a compacted list cyclically with INDEX, FILTER and MOD
Skip gaps and empty-string entries, then cycle through the remaining list with a validated positive step number.
Read the guide →Date & TimeRepeat a payment every 3 months (or N months) in a monthly financial model
Build a monthly schedule that includes the start month and repeats an illustrative payment every positive whole number of months.
Read the guide →Database & Dynamic FilteringReplace DAVERAGE, DMAX, DMIN, and DSTDEV Criteria Ranges With Dynamic FILTER Formulas
Eliminate fragile multi-row database criteria blocks by pairing FILTER, AVERAGEIFS, MAXIFS, MINIFS, and STDEV_S with direct cell-referenced criteria.
Read the guide →Data Cleaning & TextReplace Inconsistent Delimiters with a Clean Standard Separator (SUBSTITUTE)
Normalize mixed semicolon, comma, and optional-space delimiters into a consistent pipe or CSV separator using nested SUBSTITUTE and TRIM without double spaces.
Read the guide →Money Markets, Securities Financing & Tri-Party / Bilateral RepoRepo Collateral Haircut, ACT/360 Interest & Leverage Formula
Calculate Securities Repurchase Agreement (Repo) Purchase Cash Lent After Haircut on Dirty Collateral Value, ACT/360 Repo Interest ($M), Term Repurchase Price ($M), and Implied Collateral Leverage Multiple (1 / Haircut).
Read the guide →TextREPT function: build in-cell progress bars and zero-pad fixed-width IDs
Repeat characters N times with REPT to render lightweight text bar charts or pad variable-length account numbers.
Read the guide →FinancialRequired recovery rate: percentage gain needed to break even after a loss
Calculate the asymmetric percentage gain required to recover from a portfolio drawdown or sales drop using LossPct / (1 - LossPct).
Read the guide →Lookup & ReferenceReshape a Single Column Into Fixed-Width Rows or Columns (WRAPROWS & WRAPCOLS Formula)
Convert a flat 1D vertical list into a row-major (WRAPROWS) or column-major (WRAPCOLS) 2D table with custom padding for incomplete final blocks using INDEX and COUNTA.
Read the guide →FinanceResidual Income (RI) Equity Valuation & Justified Price-to-Book (P/B)
Value banks, insurers, and non-dividend companies using the Edwards-Bell-Ohlson Residual Income Model V_0 = BV_0 + ((ROE - r_e) * BV_0) / (r_e - g) and Justified P/B = 1 + (ROE - r_e) / (r_e - g).
Read the guide →Automotive Body-in-White Joining, AWS D8.1 Resistance Spot Welding & Joule HeatingResistance Spot Welding Joule Heat & Nugget Size Formula
Calculate AWS D8.1 Minimum Weld Nugget Diameter 4*SQRT(t) (mm), 60 Hz Weld Squeeze/Hold Time (s), Joule Weld Energy I²Rt (J), and Electrode Cap Current Density (A/mm²).
Read the guide →FinancialRestaurant food cost %, edible yield, and menu pricing formulas
Calculate Period Food Cost % from inventory counts, adjust ingredient costs for butcher/prep trim loss (AP Cost / Yield %), and price menu items.
Read the guide →Restaurant Operations & Hospitality FinanceRestaurant Prime Cost & Menu Engineering Formula
Audit restaurant Prime Cost % = (CoGS + Total_Labor) / Sales and classify dishes into Stars, Plowhorses, Puzzles, and Dogs via Michael Kasavana and Donald Smith’s (1982) 70% popularity rule.
Read the guide →AI workflowsRestore a table from an image with AI, then check the numbers
Turn a screenshot into editable spreadsheet cells. Review uncertain values, verify quantities and prices, and add formulas only after checking the transcription.
Read the guide →Biostatistics & Survival AnalysisRestricted Mean Survival Time (RMST) Exact Step-Function Area Formula
Calculate exact Restricted Mean Survival Time RMST(tau) and Restricted Mean Time Lost RMTL(tau) under a Kaplan-Meier step curve without trapezoidal downward bias.
Read the guide →Retail Merchandising & Revenue ManagementRetail Clearance Markdown & Salvage Profit Formula
Calculate Constant-Elasticity Clearance Units Sold MIN(I_rem, v_0 * (1 - d)^epsilon * W_rem), Hold-Full-Price vs Markdown Gross Margin (with end-of-season liquidator salvage), and Net Markdown Profit Lift.
Read the guide →FinancialRetail Open-to-Buy (OTB) formula: merchandise budget at retail and cost
Calculate monthly Open-to-Buy purchasing capacity OTB = PlannedSales + Markdowns + PlannedEOM - BOM - OnOrder at both retail value and wholesale cost.
Read the guide →Retail Real Estate & Lease AccountingRetail Percentage Lease & Natural Breakpoint Formula
Calculate shopping-center Natural Breakpoint = Base_Rent / Pct_Rate, Overage Rent = Pct_Rate * MAX(0, Sales - Breakpoint), and Effective Occupancy-Cost-to-Sales Ratio.
Read the guide →Commercial Real Estate, Retail Leasing & Store EconomicsRetail Percentage Rent, Natural Breakpoint & CAM Formula
Calculate Retail Shopping-Center Natural Sales Breakpoint (Base_Rent / Overage_Pct), Percentage Rent Overage Charge MAX(0, (Sales - Breakpoint)*Pct), Capped Controllable CAM Reconciliation, and Total Occupancy Cost % of Sales.
Read the guide →Retail Category Management & MerchandisingRetail Planogram Linear-Foot Margin & Slotting Formula
Calculate Allocated Planogram Linear Shelf Feet, Weekly Gross Margin per Linear Foot (GM/LF), Space-to-Margin Productivity Index, and New-SKU Slotting Allowance Payback Weeks.
Read the guide →Retail Merchandising & Trade PromotionRetail Promo Cannibalization, Halo & Net Margin Formula
Decompose Trade Promotion ROI across Naive Promoted-SKU Margin Lift, Sister-SKU Cannibalization Loss, Cross-Category Basket Halo Profit, and True Net Incremental Category Margin.
Read the guide →FinancialRetail Sell-Through Rate, Weeks of Supply (WOS) & Stock-to-Sales
Calculate sell-through, beginning-stock-to-sales and remaining weeks of supply for a closed inventory cohort with no replenishment or returns.
Read the guide →Financial & AccountingRetrieve Latest Closing Price, As-Of Quote & Trailing N-Day Average From a Price Log
Extract the most recent closing price for any ticker in an unsorted multi-symbol quote log, snap weekend or holiday valuation dates back to the prior trading close, and compute trailing N-day averages.
Read the guide →LogicalReturn Blank ("") If Input Is Empty—Without Breaking Downstream Math!
Suppress ugly 0s on unentered template rows with IF(ISBLANK(B2), "", ...) while ensuring downstream totals use SUM/N() rather than + so "" never triggers #VALUE!.
Read the guide →Lookup & ReferenceReturn Cell Address of Highest Value or Lookup Match (ADDRESS + MATCH)
Return address text for the first maximum, minimum or matching reading ID, with explicit validation and missing-match handling.
Read the guide →Dynamic ArraysReturn multiple lookup matches into separate rows or columns (FILTER + TRANSPOSE)
Turn one-to-many lookups (all reps in a territory or all SKUs on an order) into vertical lists with FILTER or horizontal columns with TRANSPOSE(FILTER(...)).
Read the guide →Lookup & ReferenceReturn the A1 cell address of the MAX value or lookup match (ADDRESS & XMATCH)
Locate the exact "$C$6" or "C6" coordinate of the highest number or a matched lookup key inside any offset range using ADDRESS, ROW, COLUMN, and XMATCH.
Read the guide →Auction Theory & Mechanism DesignRevenue Equivalence: First-Price vs All-Pay Bid Formula
Compare symmetric Bayes-Nash equilibrium bids across Second-Price/English, First-Price/Dutch, and All-Pay auctions on [0, V_max] and verify William Vickrey (1961) and Roger Myerson’s (1981) Revenue Equivalence identity.
Read the guide →Fintech Credit, SaaS/E-Commerce Capital & Non-Dilutive DebtRevenue-Based Financing (RBF) Royalty Sweep & APR Formula
Calculate Revenue-Based Financing Total Repayment Cap ($k), Monthly Gross-Revenue Sweep ($k), Months to Payoff Cap, and Nominal Annualized Fee Rate (%).
Read the guide →FinancialReverse discount formula: find original price before a percentage discount
Divide SalePrice by (1 - DiscountPct) to recover exact list price—avoiding the common mistake of multiplying SalePrice by (1 + DiscountPct).
Read the guide →Supply Chain Finance & Corporate TreasuryReverse Factoring (SCF) Arbitrage & Savings Formula
Calculate Supply Chain Finance (Reverse Factoring) ACT/360 Early-Payment Discount Fee, Supplier Day-10 Net Cash Proceeds, Supplier Interest Savings vs Standalone Borrowing, and Participation Viability.
Read the guide →Lookup & ReferenceReverse Left Lookup: Retrieve Values to the Left of the Lookup Column
Look up an ID or SKU positioned to the left of the search column without rearranging worksheet columns using XLOOKUP or INDEX and MATCH.
Read the guide →Corporate Tax M&A, Divestiture Structuring & IRC Section 355(e)Reverse Morris Trust >50% Tax-Free Spin-Merge Formula
Calculate Reverse Morris Trust (RMT) SpinCo Contributed Equity (EV - Leveraged Cash Distribution), Combined MergeCo Equity Value, Parent Shareholder Pro-Forma Ownership Share, and IRC Section 355(e) / Tax-Basis Qualification.
Read the guide →FinancialReverse mortgage illustration: growing balance and projected home equity with FV
Model an initial draw and fixed end-of-month advances at a constant monthly rate, then compare debt with an assumed future property value.
Read the guide →Lookup & ReferenceReverse the order of a list or column dynamically
Flip a chronological log upside down without sorting alphabetically by pairing SORTBY with a descending SEQUENCE index or INDEX/ROWS.
Read the guide →TextReverse word or category breadcrumb order inside a cell
Flip hierarchical paths ("Global / US / Retail / Shoes" -> "Shoes / Retail / US / Global") or space-separated words using vertical TEXTSPLIT + SORTBY.
Read the guide →Healthcare & ClinicalRevised Baux Score & Osler Logistic Burn Mortality Formula
Calculate the Revised Baux Score = Age + %TBSA + 17 * Inhalation_Injury and predicted inpatient burn-center mortality (%) via Turner Osler’s 2010 National Burn Repository logistic model.
Read the guide →Healthcare & ClinicalRevised Cardiac Risk Index (Lee RCRI) Preoperative MACE Score Formula
Calculate Thomas Lee’s ACC/AHA/CCS-endorsed 0–6 Revised Cardiac Risk Index (High-Risk Surgery, Ischemic Heart Disease, Heart Failure, Cerebrovascular Disease, Pre-op Insulin, Creatinine > 2.0 mg/dL) and Duceppe 30-day perioperative MACE risk (%).
Read the guide →