xllify Standard Library

A collection of pure logic Excel functions in Luau, designed to complement Excel's built-in functions without duplicating them. xllify Assistant will use these where possible when suggesting solutions. See blog post.

Design Principles

1. No Excel Overlap - Functions only exist when they provide value beyond Excel's built-in capabilities
2. Luau Native - Work directly with Luau arrays and types
3. Self-Documenting - Function names clearly describe their purpose

Installation

Install xllify Tools:

macOS / Linux

curl -fsSL https://xllify.com/install.sh | bash

Windows (PowerShell)

irm https://xllify.com/install.ps1 | iex

Get an XLLIFY_DEV_KEY from xllify and set it:

export XLLIFY_DEV_KEY=your_key_here

---

Testing from command line

Start the REPL, then load a file and call functions interactively:

$ xllify-lua
> load functions/math.luau
> call ROUND_TO_MULTIPLE 7 5
5
> call RUNNING_TOTAL [1,2,3,4,5]
Matrix (5x1):
     A
   ┌───────────┐
 1 │ 1.000000  │
   ├───────────┤
 2 │ 3.000000  │
   ├───────────┤
 3 │ 6.000000  │
   ├───────────┤
 4 │ 10.000000 │
   ├───────────┤
 5 │ 15.000000 │
   └───────────┘

Or run a one-liner directly:

xllify-lua --load functions/math.luau call ROUND_TO_MULTIPLE 7 5
# → 5

List all functions in a file:

xllify-lua --load functions/math.luau list

---

Building a kitchen sink XLL (optional)

You can use xllify to build an XLL locally to try these functions out. The build will work on any platform, but obviously, you'll need a Windows machine to run it.

$ ./scripts/build_functions.sh
$ make xll

mkdir -p builds
xllify build xll --title "xllify-kitchen-sink" --namespace UTIL -o builds/xllify-kitchen-sink.xll functions/conversion.luau functions/date.luau functions/financial.luau functions/logic.luau functions/lookup.luau functions/math.luau functions/text.luau functions/validation.luau
wrote builds/xllify-kitchen-sink.xll (970340 bytes)
Done. Copy builds/xllify-kitchen-sink.xll to a Windows machine; see https://xllify.com/xll-need-to-know for next steps.

---

Function overview

Conversion (2 functions)

Function	Description
DECIMAL_TO_FRACTION	Converts a decimal to a readable fraction string (e.g. 0.75 → \
FRACTION_TO_DECIMAL	Parses a fraction string and returns its decimal value

Date (9 functions)

Function	Description
ADD_MONTHS	Adds N calendar months to a date, snapping to end-of-month when needed
ADD_YEARS	Adds N years to a date, handling Feb 29 leap year snapping
DATE_DIFF_LABEL	Human-readable time difference label (\
FISCAL_QUARTER	Fiscal quarter number given a date and fiscal year start month
IS_BUSINESS_DAY	TRUE if a date falls on Monday through Friday
IS_LEAP_YEAR	TRUE if a year is a leap year
START_OF_MONTH	Returns the serial date of the first day of the month
WEEK_NUMBER_ISO	Returns the ISO 8601 week number for a date
WORKDAYS_BETWEEN	Count of Mon–Fri working days between two dates (exclusive)

Financial (7 functions)

Function	Description
ANNUALISE_RETURN	Converts a total return over N days to an annualised rate
BREAK_EVEN_UNITS	Units needed to break even given fixed costs, price, and variable cost
COMPOUND_INTEREST	Total value after compound interest: A = P(1 + r/n)^(nt)
DISCOUNT_FACTOR	Present value discount factor: 1 / (1 + rate)^periods
DRAWDOWN	Per-period drawdown from peak for a range of portfolio values
RULE_OF_72	Approximate years to double an investment at a given annual rate
SHARPE_RATIO	Sharpe ratio given a returns range and risk-free rate

Logic (4 functions)

Function	Description
ALL_OF	TRUE if all values in a range satisfy a condition string
ANY_OF	TRUE if any value in a range satisfies a condition string
DEFAULT_IF_ERROR	Returns a default when input is error, blank, empty string, or zero
SWITCH_MAP	Maps a value to an output using a paired key/value range

Lookup (3 functions)

Function	Description
CLOSEST	Returns the value in a range numerically closest to a target
COLLECT_UNIQUE	Returns unique matching values as a spilled array
NTH_MATCH	Returns the Nth matching value from a range

Math (10 functions)

Function	Description
COUNT_IF_BETWEEN	Counts values in a range that fall between min and max
GEOMETRIC_MEAN	Calculates the geometric mean of a range of positive numbers
HARMONIC_MEAN	Calculates the harmonic mean of a range of positive numbers
MODE_RANGE	Most frequently occurring value in a range, lowest on ties
NORMALIZE	Calculates the z-score of a value given a mean and standard deviation
PERCENTILE_RANK	Returns what percentile (0–100) a value sits at within a range
ROUND_TO_MULTIPLE	Rounds a value to the nearest multiple
RUNNING_TOTAL	Cumulative sum of a range, returned as a spilled column
STDDEV_RANGE	Population standard deviation of a range
SUM_IF_BETWEEN	Sums values where the criteria range falls between min and max

Text (8 functions)

Function	Description
EXTRACT_DOMAIN	Extracts the domain from an email address or URL
FIRST_NAME	Extracts the first word from a full name string
INITIALS	Extracts initials from a full name
LAST_NAME	Extracts the last word from a full name string
REMOVE_ACCENTS	Strips diacritics from accented characters
STRIP_NON_ALPHA	Removes everything except letters and spaces from a string
STRIP_NON_NUMERIC	Removes everything except digits, dot, and minus sign
WRAP_TEXT	Inserts line breaks every N characters at word boundaries

Validation (4 functions)

Function	Description
IS_BLANK_OR_ZERO	TRUE if a value is blank, empty string, or zero
IS_CREDIT_CARD	Validates a credit card number using the Luhn algorithm
IS_DATE_VALID	TRUE if a value is a plausible Excel date serial (1900–2200)
IS_JSON	TRUE if a string is valid JSON

Scripts

# Concatenate lib/<category>/*.luau into functions/<category>.luau
./scripts/build_functions.sh|ps1

# Generate index.json and update README tables from source
./scripts/generate_docs.sh|ps1

Function File Format

Each .luau file contains a single function with metadata for MCP indexing. The dense tagging and descriptions are intentional: stdlib functions need rich metadata so the code model can retrieve the right function from a large index. For functions you write yourself, this level of annotation isn't necessary.

--[[
@function CLAMP
@description Constrains a value to a specified range
@category Math
@tags constrain, limit, range
@param value number The value to constrain
@param min_val number The minimum allowed value
@param max_val number The maximum allowed value
@returns number The clamped value
@examples
  CLAMP(5, 0, 10) → 5
  CLAMP(-5, 0, 10) → 0
@rationale Excel doesn't have a CLAMP function. Commonly needed.
]]
xllify.ExcelFunction({
    name = "CLAMP",
    description = "Constrains a value to a specified range",
    category = "Math",
    parameters = {
        { name = "value", type = "number", description = "The value to constrain" },
        { name = "min_val", type = "number", description = "The minimum allowed value" },
        { name = "max_val", type = "number", description = "The maximum allowed value" }
    }
}, function(value, min_val, max_val)
    if value < min_val then return min_val end
    if value > max_val then return max_val end
    return value
end)