Learn one useful thing today.
Generate Repeating Block Numbers Every N Rows (1,1,1, 2,2,2)
Assign repeating pallet, carton, or cohort group IDs (1, 1, 1, 2, 2, 2...) and within-block slot numbers (1, 2, 3, 1, 2, 3...) every N rows using INT and MOD.
Read the guide →Statistical & Clinical AnalysisGeorge-Desu & Rubinstein-Lachin-Foulkes Survival Trial Sample Size: Accrual & Follow-Up
Calculate required Phase III survival events via George & Desu (1974) d = CEILING(4*(z_alpha/2 + z_beta)^2 / (LN(HR))^2, 1) and convert events into enrolled patients N under uniform accrual A and additional follow-up F.
Read the guide →Statistical & Clinical AnalysisGeriatric Nutritional Risk Index: historical formula and context
Explore the GNRI equation using albumin and a capped historical reference-weight ratio, with units and cohort limitations.
Read the guide →Quantitative Finance & Real OptionsGeske Call-on-Call Compound Option Exercise Threshold and Bound
Evaluate staged R&D and M&A call-on-call options in Excel and Arte Sheets by testing Geske’s (1979) T_1 residual Black-Scholes exercise condition C_BSM(S_T1, K_2, T_2 - T_1) >= K_1.
Read the guide →Lookup & ReferenceGet address of first and last cell in a range (ADDRESS, ROW, ROWS, COLUMN & COLUMNS)
Return the top-left C3, bottom-right E6, or full $C$3:$E$6 address text for a rectangular range.
Read the guide →Date & TimeGet Calendar Date for the Nth Day of the Year (Leap-Aware DATE Overflow)
Convert an ordinal day-of-year integer (1–366) and 4-digit year into an exact calendar date—automatically adjusting for February 29 in leap years—using DATE(year, 1, n).
Read the guide →Lookup & ReferenceGet Cell Value & Coordinate String at Given Row and Column (INDEX + ADDRESS)
Retrieve any cell’s exact value without volatile INDIRECT by pairing INDEX(B2:D5, G2, G3) with ADDRESS(Row_Offset + G2, Col_Offset + G3, 4) for audit trail reporting.
Read the guide →Lookup & ReferenceGet Column Name from Index & Column Position from Header (INDEX / MATCH)
Retrieve the nth column header label from a table header row with INDEX(A1:E1, 1, k) and find the 1-based column index of any named field with MATCH(name, A1:E1, 0).
Read the guide →Date & TimeGet day name from a date: locale-safe weekday labels with WEEKDAY and CHOOSE
Turn any calendar date into "Monday".."Sunday" or "Mon".."Sun" using WEEKDAY(date, 2) + CHOOSE so formulas never break across locales.
Read the guide →Date & TimeGet days in month: leap-year safe month length and daily proration
Combine DAY and EOMONTH(date, 0) to return 28, 29, 30, or 31 for any calendar date and prorate monthly fees accurately.
Read the guide →Date & TimeGet days, hours, and minutes between two timestamps ("3d 15h 30m")
Calculate multi-day incident or transit durations into clean "Xd Yh Zm" labels without losing 24-hour rollovers or suffering floating-point minute drift.
Read the guide →Date & TimeGet Earliest Start & Latest Finish Project Dates by Phase (MINIFS & MAXIFS)
Summarize valid task dates by phase using MINIFS and MAXIFS, with explicit checks for missing tasks, incomplete dates and reversed date ranges.
Read the guide →Lookup & ReferenceGet First & Last Numeric or Text Value in a Sparse Row
Find the leftmost and rightmost numbers or nonempty text entries in a mixed row, preserving column order and distinguishing zero, numeric text and empty-string formulas.
Read the guide →Lookup & ReferenceGet First & Last Text Value in a Mixed Range (Wildcard XLOOKUP)
Extract the first or last non-empty text entry from a column or row mixed with numbers and blanks using XLOOKUP("?*", range, range, "No Text Found", 2, 1) and reverse search_mode -1.
Read the guide →Lookup & ReferenceGet first and last entry by month and year (FILTER, TAKE, DATE & EOMONTH)
Return the first and last source-row balances within a selected calendar month, including timestamps on its final day.
Read the guide →Lookup & ReferenceGet First or Last Match Where Cell Contains Text (Wildcard XLOOKUP)
Retrieve either the earliest or most recent transaction whose description contains a target keyword by pairing XLOOKUP match_mode = 2 (wildcards) with search_mode = 1 or -1.
Read the guide →Lookup & ReferenceGet First Text Value in a Row or Column (Skipping Blanks & Numbers)
Extract the first non-empty text entry across mixed blank, numeric, and text cells using wildcard XLOOKUP("?*", B2:F2, B2:F2, "No Text Found", 2) or INDEX + MATCH("?*", ...).
Read the guide →Date & TimeGet fiscal quarter and fiscal year from a date (any start month)
Convert transaction dates into calendar quarters (Q1–Q4) or shifted fiscal quarters and fiscal years (April, July, or October starts).
Read the guide →Dates & TimeGet Last Specific Weekday of Month (Last Friday, Monday, or Thursday)
Calculate the exact date of the last Friday, Monday, or any target weekday (1=Sun to 7=Sat) in a month using EOMONTH(date, 0) - MOD(WEEKDAY(EOMONTH(date, 0)) - target_dow + 7, 7).
Read the guide →Date & TimeGet Monday of the week from any date (weekly cohort grouping)
Roll daily timestamps back to the Monday (or Sunday) starting their week using Date - WEEKDAY(Date, 2) + 1.
Read the guide →Date & TimeGet month name from a date: locale-safe full and 3-letter labels
Extract English full ("April") or abbreviated ("Apr") month names reliably across locales with CHOOSE and MONTH.
Read the guide →Dates & TimeGet Most Recent Day of Week (Week-Starting Monday or Prior Friday)
Roll any transaction timestamp back to its week-starting Monday (inclusive on-or-before) or strictly prior weekday using B2 - MOD(WEEKDAY(B2) - target_dow + 7, 7).
Read the guide →StatisticalGet nth largest or smallest value—and matching name—with criteria (LARGE, SORT & FILTER)
Extract the 2nd, 3rd, or kth highest (or lowest) booking inside a specific team along with the rep's name using LARGE(FILTER(...), k) and INDEX(SORT(FILTER(...))).
Read the guide →MathGet original number from percent change: Final / (1 + PctChange)
Back out pre-hike salary, pre-markup cost, or pre-discount list price accurately using division by (1 + Pct) instead of the broken Final * (1 - Pct) shortcut.
Read the guide →Date & TimeGet previous Monday, Sunday, or any most-recent weekday from a date
Floor daily timestamps back to their "Week Starting Monday" or "Week Starting Sunday" cohort date—or find the most recent Friday/payday.
Read the guide →Date & Time FormulasGet Project Start Date, End Date & Total Span by Project (MINIFS & MAXIFS)
Roll up task-level schedules into overall project kickoff date MINIFS, final completion date MAXIFS, and inclusive calendar duration MAXIFS - MINIFS + 1.
Read the guide →Date & TimeGet quarter start date and quarter end date from any date
Snap any transaction date to the first day (Jan 1, Apr 1, Jul 1, Oct 1) and last day (Mar 31, Jun 30, Sep 30, Dec 31) of its calendar quarter.
Read the guide →Lookup & ReferenceGet the 2nd, 3rd, or Nth match with INDEX and FILTER
Extract the Nth matching record for a client or SKU cleanly with INDEX(FILTER(...), N, 1) and a COUNTIF bounds guard.
Read the guide →Lookup & ReferenceGet the Address of the First and Last Cell in a Range (ADDRESS + ROWS + COLUMNS)
Compute the exact A1 coordinates of a range's top-left anchor, bottom-right corner, and full "$B$3:$D$6" span text for audit logs and dynamic range documentation.
Read the guide →Math & TrigGet the decimal part of a number (positive and negative safe)
Extract cents or fractional parts accurately using x - TRUNC(x) instead of x - INT(x), which flips -14.75 into +0.25.
Read the guide →Date & TimeGet the first day of the month (current, previous, or next month)
Anchor any date to the 1st of its month—or shift cleanly to prior or upcoming months—with EOMONTH(date, n) + 1.
Read the guide →Lookup & ReferenceGet the first non-blank value in a row or column (COALESCE pattern)
Implement SQL-style COALESCE fallback across price or contact columns with XLOOKUP—while keeping valid numeric zeros intact.
Read the guide →Lookup & ReferenceGet the last match in a column (reverse bottom-to-top XLOOKUP)
Pass -1 as XLOOKUP's 6th search_mode argument to pull the most recent appended price, status, or log row for a repeated key.
Read the guide →Lookup & ReferenceGet the last non-blank value and row position in a column with blanks
Retrieve the bottom-most populated entry and its row index—even when empty spacer rows appear throughout the column—using reverse XLOOKUP and XMATCH.
Read the guide →Lookup & ReferenceGet the last number or nonempty text in a column
Scan upward from the bottom of a partially filled column with XLOOKUP(..., 0, -1) to pull the latest balance—even when gaps exist.
Read the guide →Date & TimeGet the last working day of the month (skipping weekends and holidays)
Step backward 1 business day from the 1st of next month with WORKDAY(EOMONTH(date, 0) + 1, -1, holidays) for payroll and close dates.
Read the guide →Lookup & ReferenceGet the name or label associated with the maximum value
Pair XLOOKUP with MAX (or filtered sub-arrays) to return the top-performing rep, product, or date to the left of your metric column.
Read the guide →Date & TimeGet the next scheduled event from an unsorted date list
Find the earliest upcoming date on or after a reference date with MINIFS and retrieve its event name with XLOOKUP.
Read the guide →Date & TimeGet the next specific weekday from a date (e.g., next Friday)
Advance any order or billing date to the upcoming target weekday—choosing explicitly between same-day inclusive and strictly next week.
Read the guide →Date & TimeGet the Nth weekday of a month (e.g., 4th Thursday of November)
Calculate floating holidays and recurring monthly board dates from Year, Month, Nth occurrence, and ISO weekday—with a 5th-week guard.
Read the guide →Date & TimeGet the Same Calendar Date Next Month or Next Year Without Overflow (EDATE vs DATE)
Roll subscription billing and anniversary dates forward by 1, 3, or 12 months with EDATE so Jan 31 and Feb 29 leap-day anchors clamp to Feb 28 instead of spilling into March.
Read the guide →Math & TrigGet total from percentage: find the whole when you know a part and its % share
Recover a whole from a known part and its fraction of that same whole.
Read the guide →Spatial Statistics & OperationsGetis-Ord Gi* Local Spatial Hotspot and Coldspot Z-Score Formula
Separate high-value spatial hotspots (Z > +1.96) from low-value coldspots (Z < -1.96) using Ord and Getis’s (1995) standardized self-inclusive G_i* statistic.
Read the guide →Lookup & ReferenceGETPIVOTDATA function: pull specific totals from a PivotTable by field name
Retrieve aggregated PivotTable values by field and item pairs with GETPIVOTDATA so layout refreshes do not break report links.
Read the guide →Quantitative Finance & Empirical Asset PricingGibbons-Ross-Shanken (GRS) Joint Alpha F-Test Formula
Test whether N portfolio alphas are jointly zero using the exact Gibbons-Ross-Shanken (1989) Sharpe-ratio F-statistic F_GRS = ((T - N - K) / N) * ((SR_max^2 - SR_f^2) / (1 + SR_f^2)).
Read the guide →Engineering & ScienceGibbs-Thomson Nanoparticle Melting-Point Depression & LSW Ostwald Ripening
Calculate curvature-induced melting-point depression Delta_Tm = (2 * gamma_SL * Tm) / (Delta_Hf * r) in Kelvin for nanoparticles/dendrite tips and Lifshitz-Slyozov-Wagner coarsening radius r(t) = (r0^3 + K * t)^(1/3).
Read the guide →Civil, Structural & Geotechnical EngineeringGibson-Anderson & Vesić Undrained Soil Cavity Expansion Limit Pressure Formula
Calculate undrained clay Rigidity Index I_r = G / s_u, Cylindrical Limit Pressure p_lim,cyl = p_0 + s_u * (1 + LN(I_r)) (pressuremeter / pile shaft), and Spherical Limit Pressure p_lim,sph = p_0 + (4/3) * s_u * (1 + LN(I_r)) (deep pile tip).
Read the guide →Semiconductor PVD/Implant Vacuum Systems, Gifford-McMahon Capture Cryopump SizingGifford-McMahon Cryopump Crossover & H2 Capacity Formula
Calculate Maximum Safe Rough-to-Cryo Crossover Pressure (mbar), 15 K Activated-Charcoal H2 Sorption Capacity (mbar·L), Hours Between Regenerations (h), and Thawed H2 Pressure (mbar).
Read the guide →StatisticalGini Coefficient formula: measure income inequality & customer whale concentration
Calculate a rounded empirical Gini coefficient for five nonnegative observations and compare it with the largest observation share.
Read the guide →Engineering & ScienceGinzburg-Landau Parameter (κ) & Upper Critical Field (B_c2) Formula
Calculate the Ginzburg-Landau ratio κ = λ_L / ξ_GL, classify Type-I vs. Type-II Abrikosov vortex superconductors across κ = 1/SQRT(2), and compute upper critical field B_c2 = SQRT(2) * κ * B_c.
Read the guide →