xllify

Standard Library GitHub ↗

Ready-made Excel functions written in Luau. Use them directly in your add-in or adapt them as a starting point. The xllify Assistant and Claude Code both have an index of these functions and will use them wherever possible. See also: built-in globals reference.

Conversion 2 functions

DECIMAL_TO_FRACTION

Converts a decimal to a readable fraction string (e.g. 0.75 → \

Parameter Type Description
value number The decimal value to convert
max_denominator number The maximum denominator to use (default: 100)

Returns: string - The fraction string (e.g. "3/4")

Examples
DECIMAL_TO_FRACTION(0.75) → "3/4"
DECIMAL_TO_FRACTION(0.333) → "1/3"
DECIMAL_TO_FRACTION(1.5) → "3/2"

FRACTION_TO_DECIMAL

Parses a fraction string and returns its decimal value

Parameter Type Description
fraction string The fraction string (e.g. "3/4", "1/3", "7/2")

Returns: number - The decimal value of the fraction

Examples
FRACTION_TO_DECIMAL("3/4") → 0.75
FRACTION_TO_DECIMAL("1/3") → 0.3333
FRACTION_TO_DECIMAL("7/2") → 3.5

Date 9 functions

ADD_MONTHS

Adds N calendar months to a date, snapping to end-of-month when needed

Parameter Type Description
date number The starting date (Excel serial number)
months number The number of months to add (can be negative)

Returns: number - The resulting date as an Excel serial number

Examples
ADD_MONTHS(44927, 1) → 44955 (Feb 2023 → Mar 2023)
ADD_MONTHS(44958, -1) → 44927 (Mar 31 → Feb 28, end-of-month snap)

ADD_YEARS

Adds N years to a date, handling Feb 29 leap year snapping

Parameter Type Description
date number The starting date (Excel serial number)
years number The number of years to add (can be negative)

Returns: number - The resulting date as an Excel serial number

Examples
ADD_YEARS(44927, 1) → 45292 (Jan 31 2023 → Jan 31 2024)
ADD_YEARS(44956, 1) → 45322 (Feb 28 2023 → Feb 28 2024, not Feb 29)

DATE_DIFF_LABEL

Human-readable time difference label (\

Parameter Type Description
from_date number The reference date (Excel serial number)
to_date number The target date (Excel serial number)

Returns: string - A human-readable label describing the difference

Examples
DATE_DIFF_LABEL(44927, 44930) → "in 3 days"
DATE_DIFF_LABEL(44930, 44927) → "3 days ago"
DATE_DIFF_LABEL(44927, 44927) → "today"

FISCAL_QUARTER

Fiscal quarter number given a date and fiscal year start month

Parameter Type Description
date number The date (Excel serial number)
fiscal_start_month number The month the fiscal year starts (1=Jan, 4=Apr, 7=Jul, 10=Oct)

Returns: number - The fiscal quarter number (1–4)

Examples
FISCAL_QUARTER(44927, 4) → 3 (Jan 2023 in fiscal year starting Apr is Q3)
FISCAL_QUARTER(44927, 1) → 1 (Jan 2023 in calendar year is Q1)
FISCAL_QUARTER(44682, 7) → 1 (Jul 2022 is Q1 in Jul fiscal year)

IS_BUSINESS_DAY

TRUE if a date falls on Monday through Friday

Parameter Type Description
date number The date (Excel serial number)

Returns: boolean - TRUE if the date is Monday–Friday

Examples
IS_BUSINESS_DAY(44927) → true
IS_BUSINESS_DAY(44928) → false

IS_LEAP_YEAR

TRUE if a year is a leap year

Parameter Type Description
year number The year to check

Returns: boolean - TRUE if the year is a leap year

Examples
IS_LEAP_YEAR(2024) → true
IS_LEAP_YEAR(2023) → false
IS_LEAP_YEAR(1900) → false
IS_LEAP_YEAR(2000) → true

START_OF_MONTH

Returns the serial date of the first day of the month

Parameter Type Description
date number The date (Excel serial number)

Returns: number - The Excel serial date of the first day of that month

Examples
START_OF_MONTH(44958) → 44928 (Mar 31 2023 → Mar 1 2023)
START_OF_MONTH(44927) → 44927 (Jan 31 2023 → Jan 1 2023 — if on the 1st, returns itself)

WEEK_NUMBER_ISO

Returns the ISO 8601 week number for a date

Parameter Type Description
date number The date (Excel serial number)

Returns: number - The ISO 8601 week number (1–53)

Examples
WEEK_NUMBER_ISO(44927) → 5
WEEK_NUMBER_ISO(45291) → 52
WEEK_NUMBER_ISO(44928) → 1

WORKDAYS_BETWEEN

Count of Mon–Fri working days between two dates (exclusive)

Parameter Type Description
start_date number The start date (Excel serial number, exclusive)
end_date number The end date (Excel serial number, exclusive)

Returns: number - The number of Mon–Fri working days between the two dates

Examples
WORKDAYS_BETWEEN(44927, 44934) → 5
WORKDAYS_BETWEEN(44927, 44928) → 0

Financial 7 functions

ANNUALISE_RETURN

Converts a total return over N days to an annualised rate

Parameter Type Description
total_return number The total return as a decimal (e.g. 0.15 for 15%)
days number The number of days the return was earned over

Returns: number - The annualised rate as a decimal

Examples
ANNUALISE_RETURN(0.1, 180) → 0.2071
ANNUALISE_RETURN(0.05, 365) → 0.05
ANNUALISE_RETURN(0.2, 730) → 0.0954

BREAK_EVEN_UNITS

Units needed to break even given fixed costs, price, and variable cost

Parameter Type Description
fixed_costs number Total fixed costs
price_per_unit number Selling price per unit
variable_cost_per_unit number Variable cost per unit

Returns: number - Units needed to break even

Examples
BREAK_EVEN_UNITS(10000, 50, 30) → 500
BREAK_EVEN_UNITS(5000, 20, 12) → 625

COMPOUND_INTEREST

Total value after compound interest: A = P(1 + r/n)^(nt)

Parameter Type Description
principal number The initial investment amount
rate number The annual interest rate as a decimal (e.g. 0.05 for 5%)
years number The number of years
compounds_per_year number How many times interest compounds per year (default: 12)

Returns: number - The total value after compounding

Examples
COMPOUND_INTEREST(1000, 0.05, 10) → 1647.01
COMPOUND_INTEREST(1000, 0.05, 10, 1) → 1628.89
COMPOUND_INTEREST(5000, 0.03, 20, 12) → 9070.09

DISCOUNT_FACTOR

Present value discount factor: 1 / (1 + rate)^periods

Parameter Type Description
rate number The discount rate per period as a decimal (e.g. 0.1 for 10%)
periods number The number of periods

Returns: number - The discount factor (between 0 and 1)

Examples
DISCOUNT_FACTOR(0.1, 1) → 0.9091
DISCOUNT_FACTOR(0.1, 5) → 0.6209
DISCOUNT_FACTOR(0.05, 10) → 0.6139

DRAWDOWN

Per-period drawdown from peak for a range of portfolio values

Parameter Type Description
values any The range of portfolio values

Returns: any - A column of drawdown values (negative decimals, e.g. -0.1 = 10% below peak)

Examples
DRAWDOWN({100, 110, 105, 115, 100}) → {0; 0; -0.0455; 0; -0.1304}

RULE_OF_72

Approximate years to double an investment at a given annual rate

Parameter Type Description
rate number The annual interest rate as a decimal (e.g. 0.06 for 6%)

Returns: number - Approximate years to double the investment

Examples
RULE_OF_72(0.06) → 12
RULE_OF_72(0.09) → 8
RULE_OF_72(0.12) → 6

SHARPE_RATIO

Sharpe ratio given a returns range and risk-free rate

Parameter Type Description
returns any The range of periodic returns (as decimals)
risk_free_rate number The risk-free rate per period as a decimal (e.g. 0.0001 daily)

Returns: number - The Sharpe ratio

Examples
SHARPE_RATIO({0.01, 0.02, -0.005, 0.015, 0.008}, 0.0001) → 1.32

Logic 4 functions

ALL_OF

TRUE if all values in a range satisfy a condition string

Parameter Type Description
values any The range of values to test
condition string The condition string (e.g. ">0", "<=100", "=active", "<>")

Returns: boolean - TRUE if all values satisfy the condition

Examples
ALL_OF({1, 2, 3, 4, 5}, ">0") → true
ALL_OF({1, -2, 3}, ">0") → false
ALL_OF({"yes","yes","yes"}, "=yes") → true

ANY_OF

TRUE if any value in a range satisfies a condition string

Parameter Type Description
values any The range of values to test
condition string The condition string (e.g. ">0", "<=100", "=active", "<>")

Returns: boolean - TRUE if at least one value satisfies the condition

Examples
ANY_OF({-1, -2, 3}, ">0") → true
ANY_OF({-1, -2, -3}, ">0") → false
ANY_OF({"yes","no","maybe"}, "=yes") → true

DEFAULT_IF_ERROR

Returns a default when input is error, blank, empty string, or zero

Parameter Type Description
value any The value to check
default any The default value to return if value is error, blank, empty, or zero

Returns: any - The original value, or default if it is error/blank/empty/zero

Examples
DEFAULT_IF_ERROR("", "N/A") → "N/A"
DEFAULT_IF_ERROR(0, 1) → 1
DEFAULT_IF_ERROR("hello", "N/A") → "hello"
DEFAULT_IF_ERROR(42, 0) → 42

SWITCH_MAP

Maps a value to an output using a paired key/value range

Parameter Type Description
value any The input value to look up
keys any The range of keys to match against
values any The range of corresponding output values
default any The value to return if no match is found (optional)

Returns: any - The mapped output value, or default if not found

Examples
SWITCH_MAP("B", {"A","B","C"}, {"Alpha","Beta","Gamma"}) → "Beta"
SWITCH_MAP(2, {1,2,3}, {"low","mid","high"}) → "mid"
SWITCH_MAP("X", {"A","B"}, {1,2}, "unknown") → "unknown"

Lookup 4 functions

CLOSEST

Returns the value in a range numerically closest to a target

Parameter Type Description
values any The range of numbers to search
target number The target value to find the closest to

Returns: number - The value in the range closest to the target

Examples
CLOSEST({1, 5, 10, 15, 20}, 12) → 10
CLOSEST({100, 200, 300}, 250) → 200
CLOSEST({3, 7, 11}, 9) → 7

COLLECT_UNIQUE

Returns unique matching values as a spilled array

Parameter Type Description
criteria_range any The range to check against the criteria
criteria any The value to match
return_range any The range to return values from

Returns: any - A column array of unique matching values

Examples
COLLECT_UNIQUE({"A","B","A","C"}, "A", {10, 10, 30, 40}) → {10; 30}
COLLECT_UNIQUE({1,1,2,1}, 1, {"x","x","y","z"}) → {"x"; "z"}

FUZZY

Returns similarity score between two strings (0-1)

Parameter Type Description
needle string The string to search for
haystack string The string to compare against

Returns: number - Similarity score from 0 to 1 (1 = exact match)

Examples
FUZZY("hello", "hello") → 1
FUZZY("hello", "helo") → 0.8
FUZZY("apple", "orange") → 0.16...
FUZZY("test", "TEST") → 1

NTH_MATCH

Returns the Nth matching value from a range

Parameter Type Description
criteria_range any The range to check
criteria any The value to match
return_range any The range to return values from
n number Which match to return (1 = first, 2 = second, etc.)

Returns: any - The Nth matching value, or empty string if not found

Examples
NTH_MATCH({"A","B","A","A"}, "A", {10,20,30,40}, 2) → 30
NTH_MATCH({"x","y","x"}, "x", {"a","b","c"}, 1) → "a"

Math 11 functions

CLAMP

Constrains a value between a minimum and maximum

Parameter Type Description
value number The value to constrain
min_val number The minimum allowed value
max_val number The maximum allowed value

Returns: number - The clamped value

Examples
CLAMP(15, 0, 10) → 10
CLAMP(-5, 0, 10) → 0
CLAMP(5, 0, 10) → 5

COUNT_IF_BETWEEN

Counts values in a range that fall between min and max

Parameter Type Description
values any The range to count from
min_val number The minimum value (inclusive)
max_val number The maximum value (inclusive)

Returns: number - The count of values in the range

Examples
COUNT_IF_BETWEEN({1, 5, 10, 15, 20}, 5, 15) → 3
COUNT_IF_BETWEEN({1, 2, 3, 4, 5}, 2, 4) → 3

GEOMETRIC_MEAN

Calculates the geometric mean of a range of positive numbers

Parameter Type Description
values any The range of positive numbers

Returns: number - The geometric mean

Examples
GEOMETRIC_MEAN({1, 2, 4, 8}) → 2.828
GEOMETRIC_MEAN({2, 8}) → 4
GEOMETRIC_MEAN({100, 110, 121}) → 110

HARMONIC_MEAN

Calculates the harmonic mean of a range of positive numbers

Parameter Type Description
values any The range of positive numbers

Returns: number - The harmonic mean

Examples
HARMONIC_MEAN({1, 2, 4}) → 1.714
HARMONIC_MEAN({60, 40}) → 48
HARMONIC_MEAN({2, 2, 2}) → 2

MODE_RANGE

Most frequently occurring value in a range, lowest on ties

Parameter Type Description
values any The range of values

Returns: any - The most frequently occurring value (lowest on ties)

Examples
MODE_RANGE({1, 2, 2, 3, 3}) → 2
MODE_RANGE({5, 5, 3, 3, 1}) → 3
MODE_RANGE({7, 7, 7, 2}) → 7

NORMALIZE

Calculates the z-score of a value given a mean and standard deviation

Parameter Type Description
value number The value to normalize
mean number The mean of the distribution
stddev number The standard deviation of the distribution

Returns: number - The z-score (number of standard deviations from the mean)

Examples
NORMALIZE(70, 60, 10) → 1
NORMALIZE(50, 60, 10) → -1
NORMALIZE(60, 60, 10) → 0

PERCENTILE_RANK

Returns what percentile (0–100) a value sits at within a range

Parameter Type Description
values any The range of numbers to rank within
value number The value to find the percentile rank for

Returns: number - The percentile rank (0–100)

Examples
PERCENTILE_RANK({1, 2, 3, 4, 5}, 3) → 50
PERCENTILE_RANK({10, 20, 30, 40, 50}, 10) → 0
PERCENTILE_RANK({10, 20, 30, 40, 50}, 50) → 100

ROUND_TO_MULTIPLE

Rounds a value to the nearest multiple

Parameter Type Description
value number The value to round
multiple number The multiple to round to (e.g. 0.25, 5, 10)

Returns: number - The value rounded to the nearest multiple

Examples
ROUND_TO_MULTIPLE(7, 5) → 5
ROUND_TO_MULTIPLE(8, 5) → 10
ROUND_TO_MULTIPLE(0.7, 0.25) → 0.75

RUNNING_TOTAL

Cumulative sum of a range, returned as a spilled column

Parameter Type Description
values any The range of numbers

Returns: any - A column of cumulative sums

Examples
RUNNING_TOTAL({1, 2, 3, 4, 5}) → {1; 3; 6; 10; 15}
RUNNING_TOTAL({10, -5, 20}) → {10; 5; 25}

STDDEV_RANGE

Population standard deviation of a range

Parameter Type Description
values any The range of numbers

Returns: number - The population standard deviation

Examples
STDDEV_RANGE({2, 4, 4, 4, 5, 5, 7, 9}) → 2
STDDEV_RANGE({10, 20, 30}) → 8.165

SUM_IF_BETWEEN

Sums values where the criteria range falls between min and max

Parameter Type Description
criteria_range any The range to test
min_val number The minimum value (inclusive)
max_val number The maximum value (inclusive)
sum_range any The range of values to sum (defaults to criteria_range if omitted)

Returns: number - The sum of matching values

Examples
SUM_IF_BETWEEN({1, 5, 10, 15, 20}, 5, 15) → 30
SUM_IF_BETWEEN({1, 2, 3, 4, 5}, 2, 4, {10, 20, 30, 40, 50}) → 90

Text 8 functions

EXTRACT_DOMAIN

Extracts the domain from an email address or URL

Parameter Type Description
text string An email address or URL

Returns: string - The domain (e.g. "acme.com")

Examples
EXTRACT_DOMAIN("user@acme.com") → "acme.com"
EXTRACT_DOMAIN("https://www.example.com/path") → "example.com"
EXTRACT_DOMAIN("mailto:info@company.org") → "company.org"

FIRST_NAME

Extracts the first word from a full name string

Parameter Type Description
name string The full name

Returns: string - The first name (first token)

Examples
FIRST_NAME("Jane Ann Smith") → "Jane"
FIRST_NAME("John Doe") → "John"
FIRST_NAME("Madonna") → "Madonna"

INITIALS

Extracts initials from a full name

Parameter Type Description
name string The full name
separator string The separator between initials (default: ".")

Returns: string - The initials (e.g. "J.A.S.")

Examples
INITIALS("Jane Ann Smith") → "J.A.S."
INITIALS("John Doe") → "J.D."
INITIALS("Madonna") → "M."
INITIALS("Jane Ann Smith", "") → "JAS"

LAST_NAME

Extracts the last word from a full name string

Parameter Type Description
name string The full name

Returns: string - The last name (last token)

Examples
LAST_NAME("Jane Ann Smith") → "Smith"
LAST_NAME("John Doe") → "Doe"
LAST_NAME("Madonna") → "Madonna"

REMOVE_ACCENTS

Strips diacritics from accented characters

Parameter Type Description
text string The text containing accented characters

Returns: string - The text with accents removed

Examples
REMOVE_ACCENTS("café") → "cafe"
REMOVE_ACCENTS("naïve") → "naive"
REMOVE_ACCENTS("Ångström") → "Angstrom"

STRIP_NON_ALPHA

Removes everything except letters and spaces from a string

Parameter Type Description
text string The text to clean

Returns: string - The text with only letters and spaces remaining

Examples
STRIP_NON_ALPHA("Hello, World! 123") → "Hello World "
STRIP_NON_ALPHA("user@email.com") → "useremailcom"
STRIP_NON_ALPHA("abc-def_ghi") → "abcdefghi"

STRIP_NON_NUMERIC

Removes everything except digits, dot, and minus sign

Parameter Type Description
text string The text to clean

Returns: string - The text with only numeric characters remaining

Examples
STRIP_NON_NUMERIC("$1,234.56") → "1234.56"
STRIP_NON_NUMERIC("-42.5°C") → "-42.5"
STRIP_NON_NUMERIC("phone: 555-1234") → "555-1234"

WRAP_TEXT

Inserts line breaks every N characters at word boundaries

Parameter Type Description
text string The text to wrap
width number The maximum line width in characters

Returns: string - The text with line breaks inserted

Examples
WRAP_TEXT("The quick brown fox jumps over the lazy dog", 15) → "The quick brown\nfox jumps over\nthe lazy dog"
WRAP_TEXT("Hello world", 20) → "Hello world"

Validation 4 functions

IS_BLANK_OR_ZERO

TRUE if a value is blank, empty string, or zero

Parameter Type Description
value any The value to check

Returns: boolean - TRUE if the value is blank, empty string, or zero

Examples
IS_BLANK_OR_ZERO("") → true
IS_BLANK_OR_ZERO(0) → true
IS_BLANK_OR_ZERO("hello") → false
IS_BLANK_OR_ZERO(42) → false

IS_CREDIT_CARD

Validates a credit card number using the Luhn algorithm

Parameter Type Description
value any The card number (string or number, spaces and dashes ignored)

Returns: boolean - TRUE if the card number passes the Luhn check

Examples
IS_CREDIT_CARD("4111111111111111") → true
IS_CREDIT_CARD("4111111111111112") → false
IS_CREDIT_CARD("5500005555555559") → true

IS_DATE_VALID

TRUE if a value is a plausible Excel date serial (1900–2200)

Parameter Type Description
value any The value to check

Returns: boolean - TRUE if the value is a plausible Excel date serial number

Examples
IS_DATE_VALID(44927) → true
IS_DATE_VALID(0) → false
IS_DATE_VALID(109574) → false
IS_DATE_VALID("hello") → false

IS_JSON

TRUE if a string is valid JSON

Parameter Type Description
value any The value to check

Returns: boolean - TRUE if the value is a valid JSON string

Examples
IS_JSON("{\"key\": \"value\"}") → true
IS_JSON("[1, 2, 3]") → true
IS_JSON("not json") → false
IS_JSON("") → false