Learn one useful thing today.
Conditional Formatting Formula: Flag Past-Due Open Invoices & Missing Required Fields
Write row-level AND($C2 <> "Paid", $B2 < $G$2) rules that highlight overdue invoices without false-flagging settled rows or blank rows.
Read the guide →Text, Cleaning & ExtractionConditional Formatting If Cell Contains Specific Text, Any Text, or Not Equal
Write bullet-proof text conditional-formatting formulas: =ISNUMBER(SEARCH("urgent", $B2)) for case-insensitive substring highlights, =ISNUMBER(FIND("URGENT", $B2)) for exact-case tokens, and =AND(ISTEXT($B2), LEN(TRIM($B2))>0) for non-blank text.
Read the guide →Conditional Logic & ValidationConditional Formatting: Highlight an Entire Row or Column Based on Another Cell ($C2 vs C$2)
Master mixed cell references in custom conditional-formatting rules: lock the column ($C2="Overdue") to highlight an entire row across A2:E20, or lock the row (B$2="Blocked") to highlight an entire column.
Read the guide →StatisticalConditional MEDIAN and MODE (MEDIANIFS / MODEIFS with FILTER)
Nest FILTER inside MEDIAN and MODE.SNGL to calculate outlier-resistant group medians and most-common values without helper columns.
Read the guide →Dynamic ArraysConditional Mode: Most Frequent Value with Criteria (MODE.SNGL & FILTER)
Find the most common price point, defect code, or ticket response time within a specific department or region using IFERROR(MODE.SNGL(FILTER(values, criteria)), "No Repeating Mode").
Read the guide →StatisticalConditional Percentile (PERCENTILE IF) with FILTER for P90 SLA and pay bands
Calculate P90, P75, or P25 percentiles for a specific department, region, or service tier by nesting FILTER inside PERCENTILE.INC.
Read the guide →Decision Science, Governance & Ensemble MLCondorcet Jury Theorem Majority Accuracy Formula
Calculate majority-vote accuracy for 3-, 5-, and 7-member independent committees/ensembles and verify Marquis de Condorcet’s (1785) p < 0.50 crowd-degradation trap.
Read the guide →Aggregate Quarry & Mineral Comminution, Symons / Hydrocone Parallel-Zone KinematicsCone Crusher CSS, Eccentric Throw & Parallel-Zone Capacity
Calculate Open-Side Setting OSS (mm), Gravity Free-Fall Drop Per Half-Cycle (mm), Parallel-Zone Compression Hits, and Choke-Fed Volumetric Throughput (t/h).
Read the guide →StatisticalConfidence interval for a mean with known population standard deviation
Calculate a normal-theory confidence interval with known population SD: standard error sigma/SQRT(n), margin Z*SE, and lower/upper bounds.
Read the guide →Statistical & Clinical AnalysisConger’s Multi-Rater Generalized Kappa (κ_C): Rater-Specific Marginal Agreement
Calculate Anthony Conger’s (1980) exact multi-rater kappa κ_C = (P_o - P_e,C) / (1 - P_e,C) across fixed raters with distinct positive-call rates—and contrast against Fleiss pooled P_e.
Read the guide →Clinical Medicine & PhysiologyConicity Index (Valdez) & Waist-to-Height Ratio (WHtR) Calculator: Cylinder vs Bicone
Calculate Roberto Valdez’s (1991) Abdominal Conicity Index C_index = WC_m / (0.109 * SQRT(Weight_kg / Height_m)) and Margaret Ashwell’s universal Waist-to-Height Ratio WHtR = WC / Height (< 0.50 healthy threshold).
Read the guide →Product Pricing Research & Discrete Choice EconometricsConjoint Part-Worth & Multinomial Logit Share Formula
Calculate Conjoint Net Part-Worth Utility U_A = alpha_A - beta * Price_A, McFadden Multinomial Logit (MNL) Choice Shares EXP(U_i) / SUM(EXP(U_j)), and IIA Relative Odds Ratio EXP(U_A - U_B).
Read the guide →Construction Accounting, Miller Act Surety Bonding & Bid EstimatingConstruction Surety Sliding-Scale Bond & Capacity Formula
Calculate Commercial / Public-Works Miller Act Performance & Payment Surety Bond Tiered Sliding-Scale Premium ($), Effective Blended Bond Rate, Working-Capital Aggregate Bonding Capacity ($), and Remaining Unbonded Backlog Headroom.
Read the guide →Construction Accounting & Surety Job CostingConstruction WIP Overbilling & Retainage Formula
Calculate Cost-to-Cost Earned WIP Revenue, Overbilling Liability vs Underbilling Asset (Billings - Earned Revenue), Collectible Cash Net of Retainage Holdback, and Net Job Cashflow.
Read the guide →Inorganic Chemical Manufacturing, Sulfuric Acid & Emissions ControlContact Process Sulfuric Acid (DCDA) Tonnage & SO2 Slip Formula
Calculate Sulfur-Burner SO2 Generated (t/d), 100%-Basis H2SO4 Equivalent (t/d), Commercial 98 wt% Acid Output (t/d), and Unconverted Stack SO2 Emissions (t/d).
Read the guide →Container Liner Shipping & Maritime FinanceContainer BAF Surcharge & TEU Slot Break-Even Formula
Calculate IMO-2020 Floating Bunker Adjustment Factor BAF = MAX(0, P_fuel - P_base) * Trade_Factor, All-In TEU Freight Rate, Breakeven Loaded TEUs, and Vessel Slot Utilization.
Read the guide →Railway Track Infrastructure, AREMA Continuous Welded Rail (CWR) Thermal BucklingContinuous Welded Rail (CWR) Neutral Temp & Buckling Force
Calculate Rail Temperature Excursion Above Neutral (°C), Constrained Axial Thermal Stress (MPa), Single-Rail Axial Force (kN), and Two-Rail Track Panel Force (kN).
Read the guide →FinancialContribution Margin CM1, CM2, and CM3 formulas (e-commerce unit economics)
Build an illustrative per-order contribution waterfall from net revenue through product, fulfillment, payment and advertising costs.
Read the guide →Statistical & Clinical AnalysisCONUT (Controlling Nutritional Status) Score (0–12) Formula: Automated Hospital Lab Screen
Automate inpatient malnutrition screening from three routine blood labs—Serum Albumin (0, 2, 4, 6 pts), Total Lymphocytes (0, 1, 2, 3 pts), and Total Cholesterol (0, 1, 2, 3 pts)—using Ignacio de Ulíbarri’s (2005) CONUT Score (0–12).
Read the guide →Deep Foundations, Bridge Pier Pile Caps & Soil Stress OverlapConverse-Labarre Friction Pile Group Efficiency Formula
Calculate Stress-Shadow Angle theta (deg), Converse-Labarre Group Efficiency eta_g (%), Unfactored Sum of Single Piles (kN), and Interaction-Reduced Group Ultimate Capacity (kN).
Read the guide →Date & TimeConvert a fixed-format UTC timestamp with an explicit hour offset
Parse YYYY-MM-DDTHH:MM:SSZ timestamps and apply a supplied fixed UTC offset. Named-zone and daylight-saving rules are not inferred.
Read the guide →FinancialConvert Budget Amounts Between Daily, Weekly, Monthly and Annual Units
Compare recurring amounts using an explicit periods-per-year table. Calculate planning equivalents, not calendar-accurate payment dates or payroll entitlements.
Read the guide →EngineeringConvert cm to inches, kg to lbs, and km to miles (exact NIST constants)
Translate metric and imperial length and weight columns with exact international definition constants—compatible across every spreadsheet engine.
Read the guide →Lookup & ReferenceConvert column number to letter (and back) with ADDRESS and COLUMN
Translate 1-based column numbers like 1, 26, 27, and 53 into A, Z, AA, and BA using ADDRESS and SUBSTITUTE—and reverse the conversion with COLUMN.
Read the guide →Date & TimeConvert Compact HHMMSS Timestamps (91500 or 144530) Into Time & Duration
Parse colon-free 6-digit HHMMSS log integers—even when CSV import strips leading zeros from 091500 to 91500—using TIME(INT(x/10000), MOD(INT(x/100),100), MOD(x,100)).
Read the guide →Date & TimeConvert date to Julian day of year (1–366 and YYYYDDD) and back
Calculate the ordinal day of the year with Date - DATE(YEAR(Date), 1, 0), format 7-digit YYYYDDD codes, and parse them back.
Read the guide →Date & TimeConvert DD/MM/YYYY text strings to real spreadsheet dates without locale bugs
Parse slash-separated DD/MM/YYYY text of variable digit width using DATE, TEXTBEFORE, and TEXTAFTER so days and months never swap.
Read the guide →Engineering & Number SystemsConvert Decimal Bytes (0–255) To & From Hexadecimal and Octal (Positional Radix Math)
Convert 8-bit RGB channels, register bytes, and permissions between Decimal, 2-digit Hexadecimal, and 3-digit Octal using positional INT, MOD, MID, and FIND.
Read the guide →Date & TimeConvert decimal hours, minutes, or seconds to spreadsheet time (and back)
Divide decimal hours by 24, minutes by 1440, or seconds by 86400 to build valid time values—and multiply by 24 for payroll.
Read the guide →TextConvert feet and inches text (5' 10") to total inches and back
Parse architectural measurement strings like 5' 10" into numeric inches with TEXTBEFORE and TEXTAFTER, and format inches back.
Read the guide →TextConvert letters to numbers (A=1 to Z=26 and phone keypad digits)
Map letters A–Z to 1–26 using CODE(UPPER(ch)) - 64, convert 1–26 back to letters with CHAR(n + 64), or map letters to T9 phone digits.
Read the guide →Date & TimeConvert month name ("April" or "Apr") to month number (1–12)
Match the first 3 letters of any English month name against a 12-month array constant in XMATCH for locale-independent 1–12 numbers.
Read the guide →MathConvert negative numbers to zero (and clamp values between Min and Max)
Replace verbose IF(x<0, 0, x) formulas with MAX(0, x) for zero floors and MEDIAN(Min, x, Max) for two-sided caps.
Read the guide →Logical & Lookup FormulasConvert Numbers to 1 or 0 Binary Flags (--(B2>0), SIGN & SUMPRODUCT)
Transform arbitrary revenue, usage, or inventory quantities into clean 1/0 binary indicator variables using double-unary --(B2 > 0) and tally active periods across rows with SUMPRODUCT.
Read the guide →Math & Everyday CalculationsConvert Pounds (lb) to Kilograms (kg), Stones & Compound lb + oz Formula
Convert avoirdupois pounds to exact kilograms (* 0.45359237), kilograms back to pounds (/ 0.45359237), UK stones + pounds, and postal pounds + ounces using pure arithmetic that works in every spreadsheet.
Read the guide →Date & TimeConvert seconds to Excel time (Seconds / 86400) & HH:MM:SS over 24 hours
Transform raw call-center ACD seconds and video durations into Excel fractional-day serials and rollover-proof HH:MM:SS strings.
Read the guide →TextConvert snake_case, kebab-case, camelCase, and Human Title labels
Transform database column names (customer_first_name) into report headers ("Customer First Name"), camelCase ("customerFirstName"), and ENV_VARS.
Read the guide →Date & TimeConvert spreadsheet dates to Unix timestamps (and Unix epoch seconds back to dates)
Translate UTC date-time serials to Unix seconds using DATE(1970, 1, 1) and 86400 seconds/day—including millisecond API feeds.
Read the guide →Lookup & ReferenceConvert State and Region Names to 2-Letter Postal Codes (Bidirectional XLOOKUP)
Standardize mixed CRM address columns that contain both full state names ("California", "new york ") and existing 2-letter postal codes ("TX") into clean ISO/USPS codes without #N/A errors.
Read the guide →FinancialConvert time worked into money: billable hours x hourly rate formula
Multiply spreadsheet HH:MM durations by 24 * HourlyRate to avoid the 24x underbilling bug, and enforce minimum callout fees.
Read the guide →Date & TimeConvert timestamps across time zones: UTC offsets and midnight rollover
Add OffsetHours / 24 to full date-time values to shift calendar dates automatically across midnight, or wrap time-only values with MOD.
Read the guide →TextConvert Y/N schedule columns into days-of-week text ("Mon, Wed, Fri")
Turn seven Y/N delivery or availability flags into a weekday label list and a matching count.
Read the guide →Date & Time FormulasConvert Year & Day-of-Year Number (1–366) to Calendar Date
Convert ordinal day-of-year numbers (e.g., Year = 2026, Day = 135 -> 2026-05-15) into ISO calendar dates via DATE(Year, 1, Day_Number) while catching invalid Day 366 in non-leap years.
Read the guide →Hybrid Securities, Corporate Finance & Equity-Linked DebtConvertible Bond Parity, Premium & Breakeven Formula
Calculate Corporate Convertible Bond Conversion Ratio (Par / Conv_Price), Equity Conversion Parity Value, Dollar & Percentage Conversion Premium, and Coupon-vs-Dividend Income Payback Years.
Read the guide →Venture Capital & Seed FinancingConvertible Note Cap vs Discount Conversion Formula
Calculate Accrued Simple-Interest Note Balance, Effective Conversion Price MIN(P_round * (1 - d), V_cap / S_pre), Converted Preferred Shares, and Day-1 Paper Multiple on Principal.
Read the guide →Engineering & ScienceConwell-Weisskopf Ionized-Impurity Mobility & Matthiessen Total μ
Calculate Coulombic ionized-impurity carrier mobility μ_CW (cm^2/(V·s)) via Esther Conwell and Victor Weisskopf’s screened T^(3/2)/N_I Rutherford formula and combine with phonon mobility μ_L by Matthiessen’s Rule.
Read the guide →Engineering & ScienceCooling Fin Efficiency (eta_fin) & Fin Effectiveness (epsilon_fin) Heat-Sink Formula
Calculate the extended-surface fin parameter m*L = L * SQRT((h * P) / (k * Ac)), adiabatic-tip fin efficiency eta_fin = tanh(m*L) / (m*L) (%) via exact EXP identity, and fin effectiveness epsilon_fin.
Read the guide →HVAC Central Plants, Industrial Water Treatment & Mass BalanceCooling Tower Cycles of Concentration & Blowdown Formula
Calculate Cooling Tower Evaporation Loss (GPM), Chloride Cycles of Concentration (COC), Controlled Basin Blowdown Rate (GPM), and Total Makeup Water Demand (GPM).
Read the guide →Central Chiller Plant Condenser Water, CTI Evaporative Cooling Towers & Wet-Bulb ApproachCooling Tower Range, Wet-Bulb Approach & Effectiveness Formula
Calculate Condenser-Water Cooling Range T_hot - T_cold (°C), Wet-Bulb Approach T_cold - T_wb (°C), Cooling Tower Thermal Effectiveness (%), and Total Heat Rejection Rate (kW).
Read the guide →EngineeringCooper-Jacob Transient Well Drawdown Formula (Theis Approximation)
Estimate constant-rate confined-aquifer drawdown using the Cooper–Jacob late-time approximation. Calculate u, apply a conservative small-u screen, and inspect drawdown per tenfold increase in time.
Read the guide →