xllify

Standard Library

Conversion 2 functions

Value conversion including number bases and temperature units

CELSIUS_TO_FAHRENHEIT

Converts temperature from Celsius to Fahrenheit

Parameter Type Description
celsius number The temperature in Celsius

Returns: number - The temperature in Fahrenheit

Examples
CELSIUS_TO_FAHRENHEIT(0) → 32
CELSIUS_TO_FAHRENHEIT(100) → 212
CELSIUS_TO_FAHRENHEIT(-40) → -40
CELSIUS_TO_FAHRENHEIT(20) → 68

FAHRENHEIT_TO_CELSIUS

Converts temperature from Fahrenheit to Celsius

Parameter Type Description
fahrenheit number The temperature in Fahrenheit

Returns: number - The temperature in Celsius

Examples
FAHRENHEIT_TO_CELSIUS(32) → 0
FAHRENHEIT_TO_CELSIUS(212) → 100
FAHRENHEIT_TO_CELSIUS(-40) → -40
FAHRENHEIT_TO_CELSIUS(68) → 20

Date 4 functions

Date operations including arithmetic, boundaries, and weekday detection

ADD_DAYS

Adds a number of days to a date

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

Returns: number - The resulting date

Examples
ADD_DAYS(44927, 30) → 44957
ADD_DAYS(44927, -7) → 44920

DAYS_BETWEEN

Returns the number of days between two dates

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

Returns: number - The number of days between the dates (can be negative)

Examples
DAYS_BETWEEN(44927, 44957) → 30
DAYS_BETWEEN(44957, 44927) → -30

IS_WEEKEND

Checks if a date falls on a weekend

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

Returns: boolean - True if the date is a Saturday or Sunday

Examples
IS_WEEKEND(44927) → true (Sunday Jan 1 2023)
IS_WEEKEND(44930) → false (Wednesday)
IS_WEEKEND(44933) → true (Saturday)

QUARTER

Returns the quarter (1-4) for a given date

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

Returns: number - The quarter (1, 2, 3, or 4)

Examples
QUARTER(44927) → 1 (January 2023)
QUARTER(45017) → 2 (April 2023)
QUARTER(45108) → 3 (July 2023)
QUARTER(45200) → 4 (October 2023)

Financial 6 functions

Financial calculations including interest, percentages, and pricing

BLACK_SCHOLES

Calculates the Black-Scholes price for a European call option

Parameter Type Description
S number Current stock price
K number Strike price
T number Time to expiration in years
r number Risk-free interest rate (e.g., 0.05 for 5%)
sigma number Volatility (e.g., 0.2 for 20%)

Returns: number - The call option price

Examples
BLACK_SCHOLES(100, 100, 1, 0.05, 0.2) → 10.45
BLACK_SCHOLES(50, 55, 0.5, 0.03, 0.3) → 3.44

MARGIN

Calculates the selling price given cost and profit margin

Parameter Type Description
cost number The cost/purchase price
margin_pct number The desired profit margin (e.g., 0.25 for 25%)

Returns: number - The selling price

Examples
MARGIN(75, 0.25) → 100 (25% margin: $25 profit on $100 sale)
MARGIN(60, 0.4) → 100 (40% margin: $40 profit on $100 sale)
MARGIN(100, 0.5) → 200 (50% margin)

MARKUP

Calculates the selling price given cost and markup percentage

Parameter Type Description
cost number The cost/purchase price
markup_pct number The markup percentage (e.g., 0.25 for 25%)

Returns: number - The selling price

Examples
MARKUP(100, 0.25) → 125 (25% markup on $100 cost)
MARKUP(80, 0.5) → 120 (50% markup on $80 cost)
MARKUP(100, 1) → 200 (100% markup = double the price)

PERCENTAGE_CHANGE

Calculates the percentage change between two values

Parameter Type Description
old_value number The original value
new_value number The new value

Returns: number - The percentage change (e.g., 0.25 for 25% increase)

Examples
PERCENTAGE_CHANGE(100, 125) → 0.25 (25% increase)
PERCENTAGE_CHANGE(100, 75) → -0.25 (25% decrease)
PERCENTAGE_CHANGE(50, 100) → 1 (100% increase)

PERCENTAGE_OF

Calculates what percentage one value is of another

Parameter Type Description
part number The part value
whole number The whole value

Returns: number - The percentage (e.g., 0.25 for 25%)

Examples
PERCENTAGE_OF(25, 100) → 0.25 (25 is 25% of 100)
PERCENTAGE_OF(50, 200) → 0.25 (50 is 25% of 200)
PERCENTAGE_OF(100, 100) → 1 (100%)

PMT

Calculates the monthly payment for a loan

Parameter Type Description
rate number Annual interest rate (e.g., 0.06 for 6%)
nper number Total number of monthly payments (e.g., 360 for 30-year mortgage)
pv number Present value (loan amount)

Returns: number - The monthly payment (positive value)

Examples
PMT(0.06, 360, 200000) → 1199.10
PMT(0.05, 180, 150000) → 1186.19
PMT(0, 12, 1200) → 100

Logic 3 functions

Logical operations including conditional evaluation and null handling

BETWEEN

Checks if a value is between two bounds (inclusive)

Parameter Type Description
value any The value to check
lower any The lower bound
upper any The upper bound

Returns: boolean - True if value is within the range (inclusive)

Examples
BETWEEN(5, 1, 10) → true
BETWEEN(1, 1, 10) → true
BETWEEN(10, 1, 10) → true
BETWEEN(0, 1, 10) → false
BETWEEN("b", "a", "c") → true

IN_LIST

Checks if a value exists in a list of values

Parameter Type Description
value any The value to search for
list any The values to search in

Returns: boolean - True if value is found in the list

Examples
IN_LIST(2, 1, 2, 3, 4) → true
IN_LIST("b", "a", "b", "c") → true
IN_LIST(5, 1, 2, 3) → false
IN_LIST("hello", {"hello", "world"}) → true

NULL_IF

Returns nil if the value equals the specified value, otherwise returns the original value

Parameter Type Description
value any The value to check
null_value any The value that should return nil

Returns: any - The original value or nil

Examples
NULL_IF(0, 0) → nil
NULL_IF(5, 0) → 5
NULL_IF("N/A", "N/A") → nil
NULL_IF("hello", "N/A") → "hello"

Lookup 1 functions

Functions for finding and matching values

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

Math 8 functions

Mathematical operations including rounding, statistics, and aggregation

BMI

Calculates Body Mass Index from weight and height

Parameter Type Description
weight number Weight in kilograms
height number Height in meters

Returns: number - The BMI value

Examples
BMI(70, 1.75) → 22.86
BMI(90, 1.80) → 27.78
BMI(50, 1.60) → 19.53

CLAMP

Constrains a value between min and max

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

LERP

Performs linear interpolation between two values

Parameter Type Description
start_val number The starting value (t=0)
end_val number The ending value (t=1)
t number The interpolation factor (0 to 1)

Returns: number - The interpolated value

Examples
LERP(0, 100, 0.5) → 50
LERP(10, 20, 0.25) → 12.5
LERP(0, 100, 0) → 0
LERP(0, 100, 1) → 100

MAP_RANGE

Maps a value from one range to another

Parameter Type Description
value number The value to map
in_min number The minimum of the input range
in_max number The maximum of the input range
out_min number The minimum of the output range
out_max number The maximum of the output range

Returns: number - The mapped value in the output range

Examples
MAP_RANGE(50, 0, 100, 0, 1) → 0.5
MAP_RANGE(0, 0, 100, 0, 255) → 0
MAP_RANGE(100, 0, 100, 0, 255) → 255
MAP_RANGE(25, 0, 100, 100, 200) → 125

MEAN

Calculates the arithmetic mean (average) of a set of values

Parameter Type Description
values any One or more numbers to average

Returns: number - The arithmetic mean

Examples
MEAN(1, 2, 3, 4, 5) → 3
MEAN(10, 20) → 15
MEAN(2.5, 3.5, 4.0) → 3.333...

MULTIPLICATION_TABLE

Generates a multiplication table as a 2D matrix

Parameter Type Description
rows number Number of rows
cols number Number of columns

Returns: any - A 2D matrix where each cell is row * column

Examples
MULTIPLICATION_TABLE(3, 3) → {{1,2,3},{2,4,6},{3,6,9}}
MULTIPLICATION_TABLE(2, 4) → {{1,2,3,4},{2,4,6,8}}

ROUND_TO

Rounds a number to a specified number of decimal places

Parameter Type Description
value number The number to round
decimals number (optional) Number of decimal places (default: 0)

Returns: number - The rounded value

Examples
ROUND_TO(3.14159, 2) → 3.14
ROUND_TO(1234.5, -2) → 1200
ROUND_TO(2.5, 0) → 3

TRIMMED_MEAN

Calculates the mean after trimming outliers from both ends

Parameter Type Description
values any The values to average (range or list)
trim_pct number The fraction of values to trim from each end (e.g., 0.1 for 10%)

Returns: number - The trimmed mean

Examples
TRIMMED_MEAN({1, 2, 3, 4, 100}, 0.2) → 3 (trims 1 value from each end)
TRIMMED_MEAN({5, 10, 15, 20, 25, 30}, 0.1) → 17.5

Text 12 functions

Text manipulation including case conversion, padding, extraction, and formatting

EXTRACT_LETTERS

Extracts all alphabetic characters from a string

Parameter Type Description
text string The text to extract letters from

Returns: string - A string containing only the alphabetic characters

Examples
EXTRACT_LETTERS("abc123def456") → "abcdef"
EXTRACT_LETTERS("Hello, World! 123") → "HelloWorld"
EXTRACT_LETTERS("12345") → ""

EXTRACT_NUMBERS

Extracts all numeric characters from a string

Parameter Type Description
text string The text to extract numbers from

Returns: string - A string containing only the numeric characters

Examples
EXTRACT_NUMBERS("abc123def456") → "123456"
EXTRACT_NUMBERS("Phone: (555) 123-4567") → "5551234567"
EXTRACT_NUMBERS("no numbers here") → ""

PAD_LEFT

Pads a string on the left to a specified length

Parameter Type Description
text string The text to pad
length number The desired total length
pad_char string (optional) The character to pad with (default: space)

Returns: string - The left-padded string

Examples
PAD_LEFT("42", 5) → " 42"
PAD_LEFT("42", 5, "0") → "00042"
PAD_LEFT("hello", 10, "-") → "-----hello"

PAD_RIGHT

Pads a string on the right to a specified length

Parameter Type Description
text string The text to pad
length number The desired total length
pad_char string (optional) The character to pad with (default: space)

Returns: string - The right-padded string

Examples
PAD_RIGHT("42", 5) → "42 "
PAD_RIGHT("42", 5, "0") → "42000"
PAD_RIGHT("hello", 10, "-") → "hello-----"

REGEX_MATCH

Extracts text matching a Lua pattern

Parameter Type Description
text string The text to search in
pattern string The Lua pattern to match
occurrence number (optional) Which match to return (default: 1)

Returns: string - The matched text, or empty string if no match

Examples
REGEX_MATCH("Order #12345", "%d+") → "12345"
REGEX_MATCH("hello@example.com", "%w+@%w+%.%w+") → "hello@example.com"
REGEX_MATCH("a1 b2 c3", "%a%d", 2) → "b2"

REVERSE_TEXT

Reverses the characters in a string

Parameter Type Description
text string The text to reverse

Returns: string - The reversed text

Examples
REVERSE_TEXT("hello") → "olleh"
REVERSE_TEXT("Hello World") → "dlroW olleH"
REVERSE_TEXT("12345") → "54321"

SLUGIFY

Converts a string to a URL-safe slug

Parameter Type Description
text string The text to convert to a slug

Returns: string - The URL-safe slug

Examples
SLUGIFY("Hello World") → "hello-world"
SLUGIFY("My Blog Post!") → "my-blog-post"
SLUGIFY(" Multiple Spaces ") → "multiple-spaces"
SLUGIFY("Special @#$ Characters") → "special-characters"

SPELL_NUMBER

Converts a number to written words

Parameter Type Description
value number The number to convert

Returns: string - The number as written words

Examples
SPELL_NUMBER(123) → "one hundred twenty-three"
SPELL_NUMBER(1000) → "one thousand"
SPELL_NUMBER(-42) → "negative forty-two"
SPELL_NUMBER(3.14) → "three point one four"

TO_SENTENCE

Converts a string to sentence case

Parameter Type Description
text string The text to convert

Returns: string - The sentence case text

Examples
TO_SENTENCE("hello world") → "Hello world"
TO_SENTENCE("HELLO WORLD") → "Hello world"
TO_SENTENCE("the quick brown fox") → "The quick brown fox"

TO_TITLE

Converts a string to title case

Parameter Type Description
text string The text to convert

Returns: string - The title case text

Examples
TO_TITLE("hello world") → "Hello World"
TO_TITLE("HELLO WORLD") → "Hello World"
TO_TITLE("the quick brown fox") → "The Quick Brown Fox"

TRIM_ALL

Removes all extra whitespace from a string

Parameter Type Description
text string The text to trim

Returns: string - The trimmed text with single spaces between words

Examples
TRIM_ALL(" hello world ") → "hello world"
TRIM_ALL(" multiple spaces here ") → "multiple spaces here"
TRIM_ALL("already clean") → "already clean"

WORD_COUNT

Counts the number of words in a string

Parameter Type Description
text string The text to count words in

Returns: number - The number of words

Examples
WORD_COUNT("hello world") → 2
WORD_COUNT("one two three four five") → 5
WORD_COUNT(" spaces everywhere ") → 2
WORD_COUNT("") → 0

Validation 8 functions

Data validation including type checks and format validation

IS_BLANK

Checks if a value is blank (nil, empty string, or whitespace only)

Parameter Type Description
value any The value to check

Returns: boolean - True if the value is blank

Examples
IS_BLANK(nil) → true
IS_BLANK("") → true
IS_BLANK(" ") → true
IS_BLANK(0) → false
IS_BLANK("hello") → false

IS_EMAIL

Checks if a value is a valid email address format

Parameter Type Description
value any The value to check

Returns: boolean - True if the value looks like a valid email

Examples
IS_EMAIL("user@example.com") → true
IS_EMAIL("name.surname@company.co.uk") → true
IS_EMAIL("invalid") → false
IS_EMAIL("missing@domain") → false

IS_ERROR

Checks if a value represents an error

Parameter Type Description
value any The value to check

Returns: boolean - True if the value is an error

Examples
IS_ERROR("#VALUE!") → true
IS_ERROR("#REF!") → true
IS_ERROR("#N/A") → true
IS_ERROR("hello") → false
IS_ERROR(42) → false

IS_NUMBER

Checks if a value is a number

Parameter Type Description
value any The value to check

Returns: boolean - True if the value is a number

Examples
IS_NUMBER(42) → true
IS_NUMBER(3.14) → true
IS_NUMBER("42") → false
IS_NUMBER(nil) → false

IS_PHONE

Checks if a value looks like a phone number

Parameter Type Description
value any The value to check

Returns: boolean - True if the value looks like a phone number

Examples
IS_PHONE("555-1234") → true
IS_PHONE("(555) 123-4567") → true
IS_PHONE("+1 555 123 4567") → true
IS_PHONE("123") → false
IS_PHONE("not a phone") → false

IS_TEXT

Checks if a value is a text string

Parameter Type Description
value any The value to check

Returns: boolean - True if the value is a string

Examples
IS_TEXT("hello") → true
IS_TEXT("") → true
IS_TEXT(42) → false
IS_TEXT(nil) → false

IS_URL

Checks if a value is a valid URL format

Parameter Type Description
value any The value to check

Returns: boolean - True if the value looks like a valid URL

Examples
IS_URL("https://example.com") → true
IS_URL("http://example.com/path?query=1") → true
IS_URL("example.com") → false
IS_URL("not a url") → false

MATCHES_PATTERN

Checks if a value matches a Lua pattern

Parameter Type Description
value any The value to check
pattern string The Lua pattern to match against

Returns: boolean - True if the value matches the pattern

Examples
MATCHES_PATTERN("hello123", "%a+%d+") → true
MATCHES_PATTERN("ABC", "^%u+$") → true
MATCHES_PATTERN("abc", "^%u+$") → false
MATCHES_PATTERN("2024-01-15", "^%d%d%d%d%-%d%d%-%d%d$") → true