Formulas

Every function a sheet computes, as the help card under a cell and the function browser show it. Pax reads the same docs. For how to write a formula, see Sheets. At the end are how formulas read each column type and what each error means.

The sample sheet the examples use

Examples use a sample expenses sheet with rows 1 to 6: A Item, B Category, C Amount, D Date, E Paid (a checkbox) and F Flow (an investment's cash flows). Each example is worked out with row 1 as the current row.

A ItemB CategoryC AmountD DateE PaidF Flow
1 CoffeeFood4.52026-11-02TRUE-1000
2 RentHome12002026-11-01TRUE300
3 GroceriesFood86.252026-11-04FALSE400
4 TrainTravel322026-11-07TRUE500
5 LunchFood14.752026-11-09FALSE200
6 InternetHome602026-11-10

Aggregate

SUM Adds up numbers, cells and ranges into a total.

SUM(value1, [value2], ...)

value1
A number, a cell or a range. Empty cells and words in a range are skipped.
value2 optional
More numbers, cells or ranges, as many as you need.

Example: =SUM(C1:C6) gives 1397.5.

A ticked checkbox counts as 1 and an unticked one as 0. Excel skips TRUE and FALSE in a range.

AVERAGE Returns the average of numbers, cells and ranges.

AVERAGE(value1, [value2], ...)

value1
A number, a cell or a range. Empty cells and words in a range are skipped.
value2 optional
More numbers, cells or ranges, as many as you need.

Example: =AVERAGE(C1:C6) gives 232.92.

With no numbers to average it returns 0, where Excel returns #DIV/0!.

AVG Returns the average of numbers, the same as AVERAGE.

AVG(value1, [value2], ...)

value1
A number, a cell or a range. Empty cells and words in a range are skipped.
value2 optional
More numbers, cells or ranges, as many as you need.

Example: =AVG(C3, C5) gives 50.5.

AVG is another name for AVERAGE and works the same way.

MIN Returns the smallest number.

MIN(value1, [value2], ...)

value1
A number, a cell or a range. Empty cells and words in a range are skipped.
value2 optional
More numbers, cells or ranges, as many as you need.

Example: =MIN(C1:C6) gives 4.5.

With no numbers it returns an empty cell, where Excel returns 0. Over dates (and no numbers) it returns the earliest date.

MAX Returns the largest number.

MAX(value1, [value2], ...)

value1
A number, a cell or a range. Empty cells and words in a range are skipped.
value2 optional
More numbers, cells or ranges, as many as you need.

Example: =MAX(C1:C6) gives 1200.

With no numbers it returns an empty cell, where Excel returns 0. Over dates (and no numbers) it returns the latest date, so =MAX(D1:D6) is the last due date.

COUNT Counts how many numbers there are in cells, ranges and values.

COUNT(value1, [value2], ...)

value1
A value, a cell or a range to count in.
value2 optional
More values, cells or ranges, as many as you need.

Example: =COUNT(A1:C6) gives 6.

A value you type counts when it reads as a number, so =COUNT("12", "x", 5) is 2. A checkbox cell counts too, where Excel skips TRUE and FALSE in a range.

COUNTA Counts the cells and values that aren't empty.

COUNTA(value1, [value2], ...)

value1
A value, a cell or a range to count in.
value2 optional
More values, cells or ranges, as many as you need.

Example: =COUNTA(E1:E6) gives 5.

A cell that holds only spaces counts as empty.

SUMIF Adds the cells that match one condition.

SUMIF(range, criteria, [sum_range])

range
The cells to test.
criteria
What a cell must match, such as "Food", "Co*", ">10" or a cell holding the value. Text matches ignore case, and * and ? are wildcards.
sum_range optional
The cells to add, matched to range by position. Leave it out to add the matching cells of range itself.

Example: =SUMIF(B1:B6, "Food", C1:C6) gives 105.5.

In text criteria * matches any run of characters and ? any one, so "Co*" matches Coffee and Cola. Put ~ before * or ? to match it as itself. Wildcards match text only, never numbers.

COUNTIF Counts the cells that match one condition.

COUNTIF(range, criteria)

range
The cells to test.
criteria
What a cell must match, such as "Food", "Co*", ">10" or a cell holding the value. Text matches ignore case, and * and ? are wildcards.

Example: =COUNTIF(B1:B6, "Food") gives 3.

In text criteria * matches any run of characters and ? any one, so "Co*" matches Coffee and Cola. Put ~ before * or ? to match it as itself. Wildcards match text only, never numbers.

SUMIFS Adds the cells in a range whose rows match every condition you give.

SUMIFS(sum_range, criteria_range1, criteria1, ...)

sum_range
The cells to add up.
criteria_range1
The cells to test, one for each cell in sum_range.
criteria1
What a cell in criteria_range1 must match, such as "Food", "Co*", ">10" or a cell holding the value.

Example: =SUMIFS(C1:C6, B1:B6, "Food", C1:C6, ">10") gives 101.

Add more pairs of range and criteria to narrow it down. A row counts only when it matches every pair.

COUNTIFS Counts the rows that match every condition you give.

COUNTIFS(criteria_range1, criteria1, ...)

criteria_range1
The cells to test. Every range you test must be the same size.
criteria1
What a cell in criteria_range1 must match, such as "Food", "Co*", ">10" or a cell holding the value.

Example: =COUNTIFS(B1:B6, "Food", E1:E6, FALSE) gives 2.

Add more pairs of range and criteria to narrow it down. A row counts only when it matches every pair.

AVERAGEIF Returns the average of the cells that match one condition.

AVERAGEIF(range, criteria, [average_range])

range
The cells to test.
criteria
What a cell must match, such as "Food", "Co*", ">10" or a cell holding the value. Text matches ignore case, and * and ? are wildcards.
average_range optional
The cells to average, matched to range by position. Leave it out to average the matching cells of range itself.

Example: =AVERAGEIF(B1:B6, "Food", C1:C6) gives 35.17.

Empty cells are left out of the average. With nothing to average it returns #DIV/0.

AVERAGEIFS Returns the average of the cells whose rows match every condition you give.

AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

average_range
The cells to average.
criteria_range1
The cells to test, one for each cell in the first range.
criteria1
What a cell in criteria_range1 must match, such as "Food", "Co*", ">10" or a cell holding the value.

Example: =AVERAGEIFS(C1:C6, B1:B6, "Home") gives 630.

Empty cells are left out of the average. With nothing to average it returns #DIV/0.

MAXIFS Returns the largest number among the cells whose rows match every condition.

MAXIFS(max_range, criteria_range1, criteria1, ...)

max_range
The cells to find the largest number in.
criteria_range1
The cells to test, one for each cell in the first range.
criteria1
What a cell in criteria_range1 must match, such as "Food", "Co*", ">10" or a cell holding the value.

Example: =MAXIFS(C1:C6, B1:B6, "Food") gives 86.25.

With no matching numbers it returns 0, as Excel does. When what matches is dates, it returns the latest.

MINIFS Returns the smallest number among the cells whose rows match every condition.

MINIFS(min_range, criteria_range1, criteria1, ...)

min_range
The cells to find the smallest number in.
criteria_range1
The cells to test, one for each cell in the first range.
criteria1
What a cell in criteria_range1 must match, such as "Food", "Co*", ">10" or a cell holding the value.

Example: =MINIFS(C1:C6, B1:B6, "Home") gives 60.

With no matching numbers it returns 0, as Excel does. When what matches is dates, it returns the earliest.

COUNTBLANK Counts the empty cells in a range.

COUNTBLANK(range)

range
The cells to check. A cell counts when it's empty or holds only spaces.

Example: =COUNTBLANK(E1:E6) gives 1.

MEDIAN Returns the middle value of a set of numbers.

MEDIAN(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =MEDIAN(C1:C6) gives 46.

With an even count of numbers it averages the two in the middle. Over dates (and no numbers) it returns the middle date, or the day halfway between the two middle ones.

MODE Returns the number that appears most often.

MODE(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =MODE(3, 5, 3, 8) gives 3.

When two numbers appear equally often, the one that comes first wins. If no number repeats it returns #N/A.

STDEV Returns the standard deviation of a sample.

STDEV(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =STDEV(C1:C6) gives 474.72.

It needs at least two numbers. Use STDEVP when the numbers are a whole population.

STDEVP Returns the standard deviation of a whole population.

STDEVP(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =STDEVP(C1:C6) gives 433.36.

Use STDEV when the numbers are a sample of a larger group.

VAR Returns the variance of a sample.

VAR(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =VAR(C1:C6) gives 225359.97.

It needs at least two numbers. Use VARP when the numbers are a whole population.

VARP Returns the variance of a whole population.

VARP(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =VARP(C1:C6) gives 187799.97.

Use VAR when the numbers are a sample of a larger group.

LARGE Returns the k-th largest number in a range.

LARGE(range, k)

range
The numbers to look through.
k
Which one to return, 1 for the largest and 2 for the next. A fraction rounds up.

Example: =LARGE(C1:C6, 2) gives 86.25.

A k below 1 or above the count of numbers returns #TYPE, where Excel shows #NUM!. Over dates (and no numbers) it returns a date: =LARGE(D1:D6, 1) is the latest.

SMALL Returns the k-th smallest number in a range.

SMALL(range, k)

range
The numbers to look through.
k
Which one to return, 1 for the smallest and 2 for the next. A fraction rounds up.

Example: =SMALL(C1:C6, 2) gives 14.75.

A k below 1 or above the count of numbers returns #TYPE, where Excel shows #NUM!. Over dates (and no numbers) it returns a date: =SMALL(D1:D6, 1) is the earliest.

RANK Returns the rank of a number among other numbers, counting from the largest.

RANK(number, range, [order])

number
The number to rank. It must be one of the numbers in range, or the result is #N/A.
range
The numbers to rank it among.
order optional
Leave it out or pass 0 to rank the largest number first. Any other number ranks the smallest first.

Example: =RANK(C3, C1:C6) gives 2.

Equal numbers share a rank, and the rank after them is skipped.

PERCENTILE Returns the k-th percentile of the numbers in a range.

PERCENTILE(range, k)

range
The numbers.
k
A fraction from 0 to 1, such as 0.9 for the 90th percentile. Outside that it returns #TYPE.

Example: =PERCENTILE(C1:C6, 0.8) gives 86.25.

Between two numbers it interpolates, as Excel's PERCENTILE.INC does. PERCENTILE(range, 0.5) is the median.

QUARTILE Returns a quartile of the numbers in a range.

QUARTILE(range, quart)

range
The numbers.
quart
Which quartile, from 0 for the smallest number to 4 for the largest. 2 is the median, and a fraction is cut off.

Example: =QUARTILE(C1:C6, 1) gives 19.06.

It's the same as PERCENTILE with k set to quart divided by 4.

Math

ABS Returns a number without its sign.

ABS(number)

number
A number or a cell holding one.

Example: =ABS(C1 - C3) gives 81.75.

ROUND Rounds a number to a given number of decimal places.

ROUND(number, [digits])

number
The number to round.
digits optional
How many decimal places to keep. Leave it out to round to a whole number.

Example: =ROUND(C3, 1) gives 86.3.

A date isn't a number here yet, so =ROUND(D1, 0) gives #TYPE.

FLOOR Rounds a number down, to a whole number or to a multiple of significance.

FLOOR(number, [significance])

number
A number or a cell holding one.
significance optional
The multiple to round to, such as 5 or 0.25. Leave it out to round to a whole number.

Example: =FLOOR(C3, 5) gives 85.

A negative number rounds away from zero, so =FLOOR(-2.5) is -3. A significance of 0 gives #DIV/0.

CEIL Rounds a number up, to a whole number or to a multiple of significance.

CEIL(number, [significance])

number
A number or a cell holding one.
significance optional
The multiple to round to, such as 5 or 0.25. Leave it out to round to a whole number.

Example: =CEIL(C3, 5) gives 90.

CEIL works the same way as CEILING.

CEILING Rounds a number up to a multiple of significance, 1 by default.

CEILING(number, [significance])

number
A number or a cell holding one.
significance optional
The multiple to round to, such as 5 or 0.25. Leave it out to round to a whole number.

Example: =CEILING(C3, 0.25) gives 86.25.

A negative number rounds toward zero, so =CEILING(-2.5) is -2. A significance of 0 gives 0.

ROUNDUP Rounds a number away from zero, to a whole number or to a number of decimal places.

ROUNDUP(number, [digits])

number
A number or a cell holding one.
digits optional
How many decimal places to keep. Leave it out for a whole number. A negative number works to the left of the decimal point, so -2 gives hundreds.

Example: =ROUNDUP(C3, 1) gives 86.3.

It rounds away from zero, so =ROUNDUP(-2.5) is -3. Use CEILING to round up to a multiple instead.

ROUNDDOWN Rounds a number toward zero, to a whole number or to a number of decimal places.

ROUNDDOWN(number, [digits])

number
A number or a cell holding one.
digits optional
How many decimal places to keep. Leave it out for a whole number. A negative number works to the left of the decimal point, so -2 gives hundreds.

Example: =ROUNDDOWN(C3, 1) gives 86.2.

It rounds toward zero, so =ROUNDDOWN(-2.5) is -2. Use FLOOR to round down to a multiple instead.

POW Raises a number to a power.

POW(base, exponent)

base
The number to raise.
exponent
The power to raise it to. A fraction takes a root, so 0.5 is the square root.

Example: =POW(C4, 2) gives 1024.

POW works the same way as POWER and the ^ operator.

SQRT Returns the square root of a number.

SQRT(number)

number
A number or a cell holding one.

Example: =SQRT(C6) gives 7.746.

A negative number gives #TYPE, where Excel shows #NUM!.

MOD Returns the remainder after dividing one number by another, with the divisor's sign.

MOD(number, divisor)

number
The number to divide.
divisor
The number to divide by. Zero gives #DIV/0.

Example: =MOD(C4, 5) gives 2.

The result takes the divisor's sign, so =MOD(-7, 3) is 2.

INT Rounds a number down to the nearest whole number.

INT(number)

number
A number or a cell holding one.

Example: =INT(C3) gives 86.

A negative number rounds down, away from zero, so =INT(-2.5) is -3. TRUNC cuts the decimals off instead.

TRUNC Cuts off a number's decimals, keeping as many decimal places as you ask for.

TRUNC(number, [digits])

number
A number or a cell holding one.
digits optional
How many decimal places to keep. Leave it out for a whole number. A negative number works to the left of the decimal point, so -2 gives hundreds.

Example: =TRUNC(C3, 1) gives 86.2.

It never rounds up, so =TRUNC(-2.57, 1) is -2.5.

SIGN Returns 1 for a positive number, -1 for a negative number and 0 for zero.

SIGN(number)

number
A number or a cell holding one.

Example: =SIGN(C1 - 10) gives -1.

POWER Raises a number to a power.

POWER(base, exponent)

base
The number to raise.
exponent
The power to raise it to. A fraction takes a root, so 0.5 is the square root.

Example: =POWER(1.05, 10) gives 1.63.

The ^ operator does the same, so =1.05^10 gives the same result. Zero to a negative power gives #DIV/0.

EXP Returns e raised to the power of a number.

EXP(number)

number
A number or a cell holding one.

Example: =EXP(1) gives 2.71828.

LN Returns the natural logarithm of a number.

LN(number)

number
A number or a cell holding one.

Example: =LN(C2) gives 7.09.

Zero or a negative number gives #TYPE, where Excel shows #NUM!.

LOG Returns the logarithm of a number in a base, 10 by default.

LOG(number, [base])

number
A number or a cell holding one.
base optional
The base of the logarithm. Leave it out for base 10.

Example: =LOG(8, 2) gives 3.

Zero or a negative number gives #TYPE, where Excel shows #NUM!.

LOG10 Returns the base 10 logarithm of a number.

LOG10(number)

number
A number or a cell holding one.

Example: =LOG10(C2) gives 3.08.

Zero or a negative number gives #TYPE, where Excel shows #NUM!.

PI Returns the number pi, 3.14159265358979.

PI()

Example: =PI() gives 3.14159.

MROUND Rounds a number to the nearest multiple of another number.

MROUND(number, multiple)

number
A number or a cell holding one.
multiple
The multiple to round to, such as 5 or 0.25.

Example: =MROUND(C3, 5) gives 85.

Halves round away from zero. A number and a multiple with different signs give #TYPE, where Excel shows #NUM!.

QUOTIENT Returns the whole number part of a division, dropping the remainder.

QUOTIENT(number, divisor)

number
The number to divide.
divisor
The number to divide by. Zero gives #DIV/0.

Example: =QUOTIENT(C2, 7) gives 171.

It drops the remainder, so =QUOTIENT(-7, 2) is -3. MOD gives the remainder.

GCD Returns the greatest common divisor of whole numbers.

GCD(number1, [number2], ...)

number1
A whole number, a cell or a range. Decimals are cut off first.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =GCD(C2, C6) gives 60.

A negative number gives #TYPE, where Excel shows #NUM!.

LCM Returns the least common multiple of whole numbers.

LCM(number1, [number2], ...)

number1
A whole number, a cell or a range. Decimals are cut off first.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =LCM(C4, C6) gives 480.

A negative number gives #TYPE, where Excel shows #NUM!. Any 0 makes the result 0.

PRODUCT Multiplies numbers, cells and ranges together.

PRODUCT(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =PRODUCT(C1, C4) gives 144.

With no numbers to multiply it returns 0, as Excel does.

SUMPRODUCT Multiplies ranges cell by cell and adds up the products.

SUMPRODUCT(range1, [range2], ...)

range1
A range of numbers.
range2 optional
More ranges the same size as range1. Each cell is multiplied by the cell in the same place in the others.

Example: =SUMPRODUCT(C1:C6, E1:E6) gives 1236.5.

Text and empty cells count as 0 and a ticked checkbox as 1, so the example adds up the paid amounts. Ranges of different sizes give #TYPE.

SUMSQ Adds up the squares of numbers, cells and ranges.

SUMSQ(number1, [number2], ...)

number1
A number, a cell or a range. Empty cells and words in a range are skipped.
number2 optional
More numbers, cells or ranges, as many as you need.

Example: =SUMSQ(3, 4) gives 25.

Text

LEN Counts the characters in text.

LEN(text)

text
Text, or a cell holding it.

Example: =LEN(A3) gives 9.

Spaces count. A number counts as its digits, so =LEN(86.25) is 5.

UPPER Converts text to uppercase.

UPPER(text)

text
Text, or a cell holding it.

Example: =UPPER(B1) gives FOOD.

LOWER Converts text to lowercase.

LOWER(text)

text
Text, or a cell holding it.

Example: =LOWER(A1) gives coffee.

TRIM Removes spaces from the start and end of text and shortens runs of spaces inside it to one.

TRIM(text)

text
Text, or a cell holding it.

Example: =TRIM(" Lunch with Sam ") gives Lunch with Sam.

Tabs and line breaks come off the ends only. CLEAN removes them everywhere.

CONCAT Joins text from values, cells and ranges into one piece of text.

CONCAT(text1, [text2], ...)

text1
Text, a number, a cell or a range to join.
text2 optional
More values, cells or ranges, as many as you need.

Example: =CONCAT(A1, " (", B1, ")") gives Coffee (Food).

Nothing goes between the pieces. TEXTJOIN puts a separator between them, and the & operator joins text too, as in =A1 & " " & B1.

LEFT Returns the first characters of text.

LEFT(text, [count])

text
The text to take characters from.
count optional
How many characters to take. Leave it out for one.

Example: =LEFT(A3, 5) gives Groce.

RIGHT Returns the last characters of text.

RIGHT(text, [count])

text
The text to take characters from.
count optional
How many characters to take. Leave it out for one.

Example: =RIGHT(D1, 2) gives 02.

MID Returns characters from the middle of text, starting at a position.

MID(text, start, count)

text
The text to take characters from.
start
The position of the first character to take, counting from 1.
count
How many characters to take.

Example: =MID(D1, 6, 2) gives 11.

A date reads as text like 2026-11-02 here, so the example takes its month. MONTH does that directly.

SUBSTITUTE Replaces every match of some text with new text, or only one of the matches.

SUBSTITUTE(text, old, new, [instance])

text
The text to change.
old
The text to find. Case matters.
new
The text to put in its place.
instance optional
Which match to replace, counting from 1. Leave it out to replace every match.

Example: =SUBSTITUTE(D1, "-", "/") gives 2026/11/02.

To replace characters by position instead, use REPLACE.

PROPER Capitalizes the first letter of each word in text.

PROPER(text)

text
Text, or a cell holding it.

Example: =PROPER("lunch with SAM") gives Lunch With Sam.

A letter that follows anything other than a letter is capitalized, as in Excel, so o'neil-smith becomes O'Neil-Smith and 2nd becomes 2Nd.

TEXT Formats a number or a date as text, such as $1,200.00 or Nov 2, 2026.

TEXT(value, format)

value
A number or a date to format.
format
The format, in quotes. Number formats use 0, #,##0 and decimals, with an optional leading $ or trailing %, such as "$#,##0.00" or "0.0%". Date formats use yyyy, yy, mmmm, mmm, mm, m, dddd, ddd, dd, d, hh, ss and AM/PM, such as "mmm d, yyyy".

Example: =TEXT(C2, "$#,##0.00") gives $1,200.00.

With "yyyy-mm" a date becomes a month key such as 2026-11, handy for adding up by month with SUMIF. A format it doesn't know gives #ARG.

TEXTJOIN Joins text from values, cells and ranges, with a separator between the pieces.

TEXTJOIN(delimiter, ignore_empty, text1, ...)

delimiter
The text to put between the pieces, such as ", ".
ignore_empty
TRUE to skip empty cells, FALSE to keep them as empty pieces.
text1
Text, a cell or a range to join. Add as many as you need.

Example: =TEXTJOIN(", ", TRUE, A1:A3) gives Coffee, Rent, Groceries.

CONCATENATE Joins text from values, cells and ranges into one piece of text, like CONCAT.

CONCATENATE(text1, [text2], ...)

text1
Text, a number, a cell or a range to join.
text2 optional
More values, cells or ranges, as many as you need.

Example: =CONCATENATE(A2, ", ", B2) gives Rent, Home.

CONCATENATE works the same way as CONCAT.

FIND Returns the position of text within other text, matching case.

FIND(find_text, within_text, [start])

find_text
The text to look for. Case matters.
within_text
The text to look in.
start optional
The position to start looking from, counting from 1. Leave it out to start at the beginning.

Example: =FIND("-", D1) gives 5.

When the text isn't there it gives #TYPE, where Excel shows #VALUE!.

SEARCH Returns the position of text within other text, ignoring case and reading * and ? as wildcards.

SEARCH(find_text, within_text, [start])

find_text
The text to look for, in any case. A ? matches any one character and * any run of characters. Put ~ before either to find it as itself.
within_text
The text to look in.
start optional
The position to start looking from, counting from 1. Leave it out to start at the beginning.

Example: =SEARCH("GROC", A3) gives 1.

When the text isn't there it gives #TYPE, where Excel shows #VALUE!.

REPLACE Replaces a number of characters at a position with new text.

REPLACE(text, start, count, new_text)

text
The text to change.
start
The position of the first character to replace, counting from 1.
count
How many characters to replace. 0 inserts new_text without removing anything.
new_text
The text to put in their place.

Example: =REPLACE(D1, 1, 4, "2027") gives 2027-11-02.

To replace matching text instead of a position, use SUBSTITUTE.

REPT Repeats text a number of times.

REPT(text, times)

text
The text to repeat.
times
How many times to repeat it. Decimals are cut off.

Example: =REPT("*", C4 / 10) gives ***.

The example draws a small bar in a cell, one star for every 10.

EXACT Returns TRUE when two pieces of text are identical, including case.

EXACT(text1, text2)

text1
The first text.
text2
The text to compare it with.

Example: =EXACT(A1, "coffee") gives FALSE.

The = operator ignores case, so =A1 = "coffee" is TRUE.

CHAR Returns the character for a code number.

CHAR(code)

code
A number from 1 to 255, or a larger Unicode code point.

Example: =CHAR(65) gives A.

Codes 128 to 159 follow Windows-1252 as in Excel, so =CHAR(128) is €. CHAR(10) is a line break.

CODE Returns the code number of the first character in text.

CODE(text)

text
Text, or a cell holding it.

Example: =CODE(A1) gives 67.

Empty text gives #ARG.

CLEAN Removes control characters, such as line breaks and tabs, from text.

CLEAN(text)

text
Text, or a cell holding it.

Example: =CLEAN("Coffee" & CHAR(10)) gives Coffee.

It removes the characters with codes 0 to 31, such as line breaks and tabs. TRIM removes extra spaces.

TEXTBEFORE Returns the text that comes before a delimiter.

TEXTBEFORE(text, delimiter, [instance], [match_mode], [match_end], [if_not_found])

text
The text to split.
delimiter
The text that marks where to split, such as "@" or ", ".
instance optional
Which delimiter to split at, counting from 1. A negative number counts from the end. Leave it out for the first.
match_mode optional
Use 1 to ignore case when matching the delimiter. Leave it out or use 0 to match case.
match_end optional
TRUE to count the end of the text as a delimiter (the start, for a negative instance). Leave it out for FALSE.
if_not_found optional
What to return when the delimiter isn't found. Leave it out to get #N/A.

Example: =TEXTBEFORE(D1, "-") gives 2026.

TEXTAFTER Returns the text that comes after a delimiter.

TEXTAFTER(text, delimiter, [instance], [match_mode], [match_end], [if_not_found])

text
The text to split.
delimiter
The text that marks where to split, such as "@" or ", ".
instance optional
Which delimiter to split at, counting from 1. A negative number counts from the end. Leave it out for the first.
match_mode optional
Use 1 to ignore case when matching the delimiter. Leave it out or use 0 to match case.
match_end optional
TRUE to count the end of the text as a delimiter (the start, for a negative instance). Leave it out for FALSE.
if_not_found optional
What to return when the delimiter isn't found. Leave it out to get #N/A.

Example: =TEXTAFTER(D1, "-", -1) gives 02.

Lookup

XLOOKUP Finds a value in one range and returns the value in the same position of another range.

XLOOKUP(search, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

search
The value to find. Text matches whatever its case, and a number matches the same number written as text.
lookup_range
The cells to search, such as A1:A6 or a column like [Item].
return_range
The cells to return from, as long as lookup_range. The value in the matching position comes back.
if_not_found optional
What to return when nothing matches. Leave it out to get #N/A, or write NA() to give a match_mode without a fallback.
match_mode optional
Use 0 (the default) for an exact match, -1 for an exact match or else the next smaller value, 1 for an exact match or else the next larger value, or 2 to read * and ? in search as wildcards.
search_mode optional
Use 1 (the default) to search from the top, or -1 from the bottom. 2 and -2 are Excel's binary search over data sorted smallest or largest first; Blackbear searches from the top, which finds the same match in sorted data.

Example: =XLOOKUP("Lunch", A1:A6, C1:C6) gives 14.75.

It returns the first match in search order. With match_mode -1 or 1, numbers compare with numbers and text with text, alphabetically and ignoring case, so =XLOOKUP(42, C1:C6, A1:A6, NA(), -1) finds the item with the largest amount up to 42.

VLOOKUP Finds a value in the first column of a table and returns the value from another column of that row.

VLOOKUP(search, range, col_index, [is_sorted])

search
The value to find in the first column of range.
range
The table to look in, a cell range or an array such as {1, "one"; 2, "two"}. Its first column holds the values to find.
col_index
Which column of range to return a value from, counting its first column as 1.
is_sorted optional
TRUE finds the largest value at or below search in values sorted smallest first, for bands such as rates or grades. FALSE, or leaving it out, finds an exact match.

Example: =VLOOKUP("Train", A1:C6, 3, FALSE) gives 32.

It returns the first row that matches, and #N/A when none does. Search text with * or ? matches as a wildcard pattern, as in COUNTIF. Excel looks for an approximate match when is_sorted is left out, and Blackbear for an exact one, so write TRUE to look up a band. A workbook imported from Excel gets the TRUE written in. XLOOKUP does the same job with separate search and result columns.

HLOOKUP Finds a value in the first row of a table and returns the value from another row of that column.

HLOOKUP(search, range, row_index, [is_sorted])

search
The value to find in the first row of range.
range
The table to look in, as a cell range such as A1:E3 or an array. Its first row holds the values to find.
row_index
Which row of range to return a value from, counting its first row as 1.
is_sorted optional
TRUE finds the largest value at or below search in values sorted smallest first, for bands such as rates or grades. FALSE, or leaving it out, finds an exact match.

Example: =HLOOKUP("Coffee", A1:E2, 2) gives Rent.

It returns the first column that matches, and #N/A when none does. Search text with * or ? matches as a wildcard pattern, as in COUNTIF. As with VLOOKUP, write TRUE as is_sorted for an approximate match: Blackbear looks for an exact one when it's left out, and a workbook imported from Excel gets the TRUE written in.

INDEX Returns the value at a given row and column of a range.

INDEX(range, row_num, [col_num])

range
A cell range such as A1:C6, a column such as [Amount], or an array such as {10, 20, 30}.
row_num
Which row of range to read, counting its first row as 1. In a single row, such as A1:E1, it picks the column instead, as in Excel.
col_num optional
Which column of range to read, counting its first column as 1. Leave it out to read the first column.

Example: =INDEX(A1:C6, 4, 3) gives 32.

Pair it with MATCH to find a row by its value, as in =INDEX(C1:C6, MATCH("Lunch", A1:A6, 0)). Excel also returns a whole row or column when row_num or col_num is 0; Blackbear doesn't yet.

MATCH Returns the position of a value in a range, counting from 1.

MATCH(search, lookup_range, [match_type])

search
The value to find. Text matches whatever its case.
lookup_range
The cells to search, such as A1:A6 or a column like [Item].
match_type optional
Use 0 for an exact match, 1 for the largest value at or below search in a range sorted smallest first, or -1 for the smallest value at or above it in one sorted largest first. Leave it out for an exact match.

Example: =MATCH("Train", A1:A6, 0) gives 4.

Excel looks for the largest value at or below search when match_type is left out, and Blackbear looks for an exact match; a workbook imported from Excel gets the 1 written in. With 1 the range should be sorted smallest first, and with -1 largest first; numbers compare with numbers, text with text ignoring case, and dates in date order. With 0 a date matches as text, and search text with * or ? matches as a wildcard pattern.

CHOOSE Returns the value at a given position in a list of values.

CHOOSE(index, value1, [value2], ...)

index
Which value to return, counting value1 as 1.
value1
The first value to choose from.
value2 optional
More values to choose from. Add as many as you need.

Example: =CHOOSE(2, "Low", "Medium", "High") gives Medium.

Only the chosen value is computed. An index outside the list returns #ARG.

ROWS Counts the rows in a range.

ROWS(range)

range
A cell range such as A1:C6, an array such as {1, 2; 3, 4}, or a column such as [Item], which counts every row of the sheet.

Example: =ROWS(A1:C6) gives 6.

COLUMNS Counts the columns in a range.

COLUMNS(range)

range
A cell range such as A1:C6, or an array. A column such as [Item] counts as one.

Example: =COLUMNS(A1:C6) gives 3.

Date

NOW Returns the current date and time.

NOW()

Example: =YEAR(NOW()) >= 2026 gives TRUE.

It returns your local date and time as text such as 2026-11-02T14:30:00, and changes every time the sheet computes.

TODAY Returns the current date.

TODAY()

Example: =TODAY() >= D1 gives TRUE.

It returns your local date as text such as 2026-11-02, and changes every time the sheet computes.

DATE Builds a date from a year, a month and a day.

DATE(year, month, day)

year
The year, such as 2026. A year below 1900 counts from 1900, so 26 means 1926.
month
The month, from 1 to 12. A larger or smaller month moves into the next or previous year.
day
The day of the month. A day past the end of the month moves into the next month.

Example: =DATE(YEAR(D1), MONTH(D1) + 1, 1) gives 2026-12-01.

Blackbear keeps dates as text such as 2026-11-02, where Excel keeps serial numbers, so a number is not read as a date. Adding a number to a date moves it by that many days, so =D1 + 7 is a week later.

TIME Builds a time of day from an hour, a minute and a second.

TIME(hour, minute, second)

hour
The hour, from 0 to 23. Hours past 23 wrap around midnight.
minute
The minute. Minutes past 59 carry into the hour.
second
The second. Seconds past 59 carry into the minute.

Example: =TIME(14, 30, 0) gives 14:30:00.

It returns text such as 14:30:00, where Excel returns a fraction of a day.

YEAR Returns the year of a date.

YEAR(date)

date
A date cell, or date text such as "2026-11-02". An empty cell gives an empty result.

Example: =YEAR(D1) gives 2026.

MONTH Returns the month of a date, from 1 to 12.

MONTH(date)

date
A date cell, or date text such as "2026-11-02". An empty cell gives an empty result.

Example: =MONTH(D1) gives 11.

DAY Returns the day of the month of a date, from 1 to 31.

DAY(date)

date
A date cell, or date text such as "2026-11-02". An empty cell gives an empty result.

Example: =DAY(D3) gives 4.

HOUR Returns the hour of a time, from 0 to 23.

HOUR(time)

time
A time such as "14:30:00", or a date and time such as "2026-11-02T14:30:00". A date on its own is midnight.

Example: =HOUR("2026-11-02T14:30:00") gives 14.

MINUTE Returns the minute of a time, from 0 to 59.

MINUTE(time)

time
A time such as "14:30:00", or a date and time such as "2026-11-02T14:30:00". A date on its own is midnight.

Example: =MINUTE("14:30:15") gives 30.

SECOND Returns the second of a time, from 0 to 59.

SECOND(time)

time
A time such as "14:30:00", or a date and time such as "2026-11-02T14:30:00". A date on its own is midnight.

Example: =SECOND("14:30:15") gives 15.

WEEKDAY Returns the day of the week of a date as a number.

WEEKDAY(date, [type])

date
A date cell, or date text such as "2026-11-02". An empty cell gives an empty result.
type optional
How to number the days. 1 counts Sunday as 1 through Saturday as 7, 2 counts Monday as 1 through Sunday as 7, and 3 counts Monday as 0. Leave it out for 1.

Example: =WEEKDAY(D1, 2) gives 1.

Excel's types 11 to 17, which start the week on Monday through Sunday, work too.

WEEKNUM Returns the week of the year that a date falls in.

WEEKNUM(date, [type])

date
A date cell, or date text such as "2026-11-02". An empty cell gives an empty result.
type optional
The day weeks start on. 1 starts them on Sunday and 2 on Monday, and 21 counts ISO weeks. Leave it out for 1.

Example: =WEEKNUM(D1) gives 45.

Week 1 is the week that holds January 1. Excel's types 11 to 17 work too.

ISOWEEKNUM Returns the ISO week of the year that a date falls in.

ISOWEEKNUM(date)

date
A date cell, or date text such as "2026-11-02". An empty cell gives an empty result.

Example: =ISOWEEKNUM(D1) gives 45.

ISO weeks start on Monday, and week 1 is the week that holds the year's first Thursday.

EDATE Returns the date a number of months before or after a date.

EDATE(start_date, months)

start_date
The date to count from.
months
How many months to move. A negative number moves back.

Example: =EDATE(D1, 1) gives 2026-12-02.

When the new month is shorter, the day becomes its last day, so a month after January 31 is February 28 or 29.

EOMONTH Returns the last day of the month a number of months before or after a date.

EOMONTH(start_date, months)

start_date
The date to count from.
months
How many months to move. Use 0 for the end of the same month.

Example: =EOMONTH(D1, 0) gives 2026-11-30.

DATEDIF Counts the whole years, months or days between two dates.

DATEDIF(start_date, end_date, unit)

start_date
The date to count from.
end_date
The date to count to. It can't come before start_date.
unit
What to count. Use "Y" for whole years, "M" for whole months or "D" for days. "YM" counts the months left after the years, "MD" the days left after the months, and "YD" the days left after the years.

Example: =DATEDIF("1990-06-15", D1, "Y") gives 36.

An end date before the start date returns #ARG, where Excel returns #NUM!.

DAYS Counts the days between two dates.

DAYS(end_date, start_date)

end_date
The date to count to.
start_date
The date to count from.

Example: =DAYS(D6, D2) gives 9.

The end date comes first, as in Excel. Subtracting one date from the other, as in =D6-D2, gives the same number.

NETWORKDAYS Counts the working days between two dates, Monday to Friday.

NETWORKDAYS(start_date, end_date, [holidays])

start_date
The date to count from.
end_date
The date to count to.
holidays optional
Dates to skip as well as weekends, as a range of date cells or a single date.

Example: =NETWORKDAYS(D2, D6) gives 7.

Both dates count when they fall on a working day. An end date before the start date gives a negative number.

WORKDAY Returns the date a number of working days before or after a date.

WORKDAY(start_date, days, [holidays])

start_date
The date to count from.
days
How many working days to move. A negative number moves back.
holidays optional
Dates to skip as well as weekends, as a range of date cells or a single date.

Example: =WORKDAY(D1, 5) gives 2026-11-09.

The start date itself doesn't count, and weekends and holidays are skipped.

DATEVALUE Reads a date written as text and returns it in the form 2026-03-05.

DATEVALUE(text)

text
A date written as text, such as "2026-03-05", "3/5/2026" or "Mar 5, 2026". Slashes read month first.

Example: =DATEVALUE("Mar 5, 2026") gives 2026-03-05.

A two-digit year such as 3/5/26 reads 00 to 29 as the 2000s and 30 to 99 as the 1900s.

YEARFRAC Returns the fraction of a year between two dates.

YEARFRAC(start_date, end_date, [basis])

start_date
The date to count from.
end_date
The date to count to.
basis optional
How to count days. 0 counts 30 day months in a 360 day year, US style, 1 counts actual days, 2 actual days over 360, 3 actual days over 365, and 4 counts 30 day months European style. Leave it out for 0.

Example: =YEARFRAC("2026-01-01", "2026-07-01") gives 0.5.

The order of the two dates doesn't matter.

Finance

PMT Returns the payment per period for a loan or savings plan.

PMT(rate, nper, pv, [fv], [type])

rate
The interest rate per period. For monthly payments at 5% a year, use 5%/12.
nper
How many payments there are, such as 36 for three years of monthly payments. It must not be 0.
pv
What the loan or investment is worth now. A loan you receive is positive.
fv optional
The balance to reach after the last payment. Leave it out for 0, a loan paid off.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.

Example: =PMT(5%/12, 36, 10000) gives -299.71.

Money you pay out comes back negative, so a loan you receive gives a negative payment. Write a rate as 5% or 0.05, and 5%/12 for a monthly rate.

IPMT Returns the interest part of one period's payment on a loan.

IPMT(rate, per, nper, pv, [fv], [type])

rate
The interest rate per period. For monthly payments at 5% a year, use 5%/12.
per
Which payment to look at, from 1 to nper.
nper
How many payments there are, such as 36 for three years of monthly payments. It must not be 0.
pv
What the loan or investment is worth now. A loan you receive is positive.
fv optional
The balance to reach after the last payment. Leave it out for 0, a loan paid off.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.

Example: =IPMT(0.05/12, 1, 36, 10000) gives -41.67.

IPMT and PPMT for the same period add up to PMT. The interest part shrinks with each payment as the balance goes down.

PPMT Returns the principal part of one period's payment on a loan.

PPMT(rate, per, nper, pv, [fv], [type])

rate
The interest rate per period. For monthly payments at 5% a year, use 5%/12.
per
Which payment to look at, from 1 to nper.
nper
How many payments there are, such as 36 for three years of monthly payments. It must not be 0.
pv
What the loan or investment is worth now. A loan you receive is positive.
fv optional
The balance to reach after the last payment. Leave it out for 0, a loan paid off.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.

Example: =PPMT(0.05/12, 1, 36, 10000) gives -258.04.

PPMT and IPMT for the same period add up to PMT. The principal part grows with each payment.

FV Returns the future value of regular payments at a fixed interest rate.

FV(rate, nper, pmt, [pv], [type])

rate
The interest rate per period. For monthly payments at 5% a year, use 5%/12.
nper
How many payment periods there are, such as 120 for ten years of monthly payments.
pmt
The amount paid in each period. Money you put in is negative.
pv optional
What is there at the start. Money you put in is negative. Leave it out to start from 0.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.

Example: =FV(0.04/12, 120, -200) gives 29449.96.

Money you put in is negative, so the value that comes back is positive. The example saves 200 a month for ten years at 4% a year.

PV Returns the present value of regular payments at a fixed interest rate.

PV(rate, nper, pmt, [fv], [type])

rate
The interest rate per period. For monthly payments at 5% a year, use 5%/12.
nper
How many payment periods there are, such as 120 for ten years of monthly payments.
pmt
The payment made each period. Money you pay out is negative.
fv optional
The balance to reach after the last payment. Leave it out for 0.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.

Example: =PV(0.05/12, 60, -300) gives 15897.21.

Use it to see how much you can borrow for a payment you can afford. The example pays 300 a month for five years at 5% a year.

NPER Returns the number of payment periods a loan or savings plan takes.

NPER(rate, pmt, pv, [fv], [type])

rate
The interest rate per period. For monthly payments at 5% a year, use 5%/12.
pmt
The payment made each period. Money you pay out is negative.
pv
What the loan or investment is worth now. A loan you receive is positive.
fv optional
The balance to reach after the last payment. Leave it out for 0, a loan paid off.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.

Example: =NPER(0.05/12, -300, 10000) gives 35.96.

The answer can have a fraction, so round it up to count the payments, the last one smaller. It gives #ARG when the payments never reach the target, such as a payment smaller than the interest.

RATE Returns the interest rate per period of a loan or savings plan.

RATE(nper, pmt, pv, [fv], [type], [guess])

nper
How many payments there are, such as 36 for three years of monthly payments. It must not be 0.
pmt
The payment made each period. Money you pay out is negative.
pv
What the loan or investment is worth now. A loan you receive is positive.
fv optional
The balance to reach after the last payment. Leave it out for 0, a loan paid off.
type optional
Use 1 when payments come at the start of each period. Leave it out, or use 0, when they come at the end.
guess optional
Where RATE starts looking, 0.1 when left out. Try another guess if RATE gives #ARG.

Example: =RATE(36, -300, 10000) gives 0.0042.

The rate is per period and comes back as a fraction, so 0.0042 is 0.42% a month. Multiply a monthly rate by 12 for a yearly one. It gives #ARG when no rate fits.

NPV Returns the net present value of cash flows at a discount rate.

NPV(rate, value1, [value2], ...)

rate
The discount rate for one period, such as 0.08 for 8% a year with yearly flows.
value1
A cash flow, a cell or range holding them in order, or an array such as {300, 400}. The first one comes one period from now.
value2 optional
More cash flows, in order.

Example: =NPV(0.08, F2:F5)+F1 gives 164.64.

The first flow is discounted a full period, as in Excel, so an amount paid today goes outside it, as the example adds F1. In a range or array only numbers count, so text, checkboxes and empty cells are skipped.

IRR Returns the internal rate of return of a series of cash flows.

IRR(values, [guess])

values
The cash flows in order, as a range, a column or an array such as {-10000, 3000, 4200, 6800}. The first one is today, and there must be at least one negative and one positive flow.
guess optional
Where IRR starts looking, 0.1 when left out. Try another guess if IRR gives #ARG.

Example: =IRR({-10000, 3000, 4200, 6800}) gives 0.1634.

In a range or array only numbers count, so text, checkboxes and empty cells are skipped. It gives #ARG when the flows all have one sign or no rate fits.

Logic

IF Returns one result when a condition is TRUE and another when it is FALSE.

IF(condition, then, [else])

condition
Something that is TRUE or FALSE, such as C1 > 100.
then
The result when the condition is TRUE.
else optional
The result when the condition is FALSE. Leave it out and the cell stays empty.

Example: =IF(C2 > 100, "Big", "Small") gives Big.

Excel shows FALSE when the condition is FALSE and there is no else. Blackbear leaves the cell empty. A comparison such as C1 > E6 reads an empty cell as 0, or as empty text against text. A number compared with text gives #TYPE, where Excel puts every number before every text.

IFERROR Returns a value, or a fallback when that value is an error.

IFERROR(value, fallback)

value
The formula or value to check.
fallback
What to return when value is an error, such as 0 or "".

Example: =IFERROR(VLOOKUP("Taxi", A1:C6, 3), 0) gives 0.

It catches every error, #N/A included. Use IFNA to catch only #N/A.

IFS Returns the value paired with the first condition that is TRUE.

IFS(condition1, value1, [condition2], [value2], ...)

condition1
Something that is TRUE or FALSE, such as C1 > 100.
value1
The result when condition1 is TRUE.
condition2 optional
More conditions, each followed by its value. They are checked in order.
value2 optional
The result when condition2 is the first TRUE condition.

Example: =IFS(C3>1000, "Large", C3>50, "Medium", TRUE, "Small") gives Medium.

The first TRUE condition wins. When none is TRUE the result is #N/A, so end with TRUE and a value to catch the rest.

SWITCH Returns the result paired with the first value that equals an expression.

SWITCH(expression, value1, result1, [value2], [result2], ..., [default])

expression
The value to compare, such as a cell.
value1
A value to compare it with.
result1
The result when expression equals value1.
value2 optional
More values to compare, each followed by its result.
result2 optional
The result when expression equals value2.
default optional
The result when no value matches. Leave it out to get #N/A.

Example: =SWITCH(B2, "Food", "Eating", "Home", "Bills", "Other") gives Bills.

Text matches whatever its case, and a number never equals text, so 1 and "1" differ.

IFNA Returns a value, or a fallback when that value is #N/A.

IFNA(value, fallback)

value
The formula or value to check.
fallback
What to return when value is #N/A.

Example: =IFNA(XLOOKUP("Taxi", A1:A6, C1:C6), 0) gives 0.

Other errors pass through. Use IFERROR to catch every error.

AND Returns TRUE when every condition is TRUE.

AND(logical1, [logical2], ...)

logical1
A condition, a TRUE or FALSE value, or a range of them. A number counts as TRUE unless it is 0.
logical2 optional
More conditions or ranges to test.

Example: =AND(E1, C1<10) gives TRUE.

In a range only checkboxes and numbers count. Empty cells and text are skipped, and with nothing left to test the result is #TYPE.

OR Returns TRUE when any condition is TRUE.

OR(logical1, [logical2], ...)

logical1
A condition, a TRUE or FALSE value, or a range of them. A number counts as TRUE unless it is 0.
logical2 optional
More conditions or ranges to test.

Example: =OR(B3="Travel", C3>50) gives TRUE.

In a range only checkboxes and numbers count. Empty cells and text are skipped, and with nothing left to test the result is #TYPE.

XOR Returns TRUE when an odd number of conditions are TRUE.

XOR(logical1, [logical2], ...)

logical1
A condition, a TRUE or FALSE value, or a range of them. A number counts as TRUE unless it is 0.
logical2 optional
More conditions or ranges to test.

Example: =XOR(E1, E3) gives TRUE.

Two TRUE values give FALSE. In a range only checkboxes and numbers count.

NOT Reverses a logical value, so TRUE becomes FALSE and FALSE becomes TRUE.

NOT(logical)

logical
The value to reverse. A number counts as TRUE unless it is 0.

Example: =NOT(E3) gives TRUE.

Text counts as TRUE unless it is empty, where Excel gives #VALUE! for text.

TRUE Returns the logical value TRUE.

TRUE()

Example: =TRUE() gives TRUE.

You can also write TRUE on its own, without the parentheses.

FALSE Returns the logical value FALSE.

FALSE()

Example: =FALSE() gives FALSE.

You can also write FALSE on its own, without the parentheses.

NA Returns the #N/A error, which marks a value as not available.

NA()

Example: =NA() gives #N/A.

Use it to mark a value as missing on purpose. ISNA and IFNA test for it.

Info

ISERROR Returns TRUE when a value is any error.

ISERROR(value)

value
The value or cell to test.

Example: =ISERROR(VLOOKUP("Taxi", A1:C6, 3)) gives TRUE.

It is TRUE for every error, #N/A included. ISERR leaves out #N/A, and ISNA tests for it alone.

ISBLANK Returns TRUE when a cell is empty.

ISBLANK(value)

value
The cell to test.

Example: =ISBLANK(E6) gives TRUE.

Text that is empty or only spaces counts as blank too, since a cleared cell can hold empty text. Excel counts only an empty cell.

ISNUMBER Returns TRUE when a value is a number.

ISNUMBER(value)

value
The value or cell to test.

Example: =ISNUMBER(C1) gives TRUE.

Dates are text in Blackbear, so a date gives FALSE where Excel gives TRUE. A checkbox gives FALSE.

ISTEXT Returns TRUE when a value is text.

ISTEXT(value)

value
The value or cell to test.

Example: =ISTEXT(A1) gives TRUE.

Dates are text in Blackbear, so a date gives TRUE where Excel gives FALSE.

VALUE Converts text that holds a number into that number.

VALUE(text)

text
Text holding a number, such as "12.5", "5%", "1,200" or "$1,200". A number passes through as it is.

Example: =VALUE("12.5") gives 12.5.

All of the text must be the number, so "42px" gives #TYPE. Thousands go in groups of three, and a currency sign ($, €, £ or ¥) may come before the number. Use DATEVALUE for dates.

N Converts a value to a number, giving 0 for text that isn't one.

N(value)

value
The value to convert. TRUE is 1, FALSE is 0, and an error passes through.

Example: =N(E1) gives 1.

Text that is all a number, such as "42" or "1,200", reads as that number, where Excel's N gives 0 for any text. Other text, a date included, gives 0.

T Returns text as it is, and empty text for any other value.

T(value)

value
The value to check. Text comes back as it is, and an error passes through.

Example: =T(A1) gives Coffee.

A number, a checkbox or an empty cell gives empty text. Dates are text in Blackbear, so a date comes back as it is.

ISERR Returns TRUE when a value is an error other than #N/A.

ISERR(value)

value
The value or cell to test.

Example: =ISERR(XLOOKUP("Taxi", A1:A6, C1:C6)) gives FALSE.

The lookup in the example finds nothing, which is #N/A, so ISERR gives FALSE. Use ISERROR to include #N/A.

ISNA Returns TRUE when a value is the #N/A error.

ISNA(value)

value
The value or cell to test.

Example: =ISNA(XLOOKUP("Taxi", A1:A6, C1:C6)) gives TRUE.

Lookups that find nothing give #N/A, and so does NA().

ISLOGICAL Returns TRUE when a value is TRUE or FALSE.

ISLOGICAL(value)

value
The value or cell to test.

Example: =ISLOGICAL(E1) gives TRUE.

A ticked or unticked checkbox holds TRUE or FALSE, so it counts. An empty cell does not.

ISEVEN Returns TRUE when a number is even.

ISEVEN(number)

number
The number or cell to test.

Example: =ISEVEN(C4) gives TRUE.

A number with a fraction is cut to a whole number first, so 4.5 counts as 4. Text that isn't a number gives #TYPE.

ISODD Returns TRUE when a number is odd.

ISODD(number)

number
The number or cell to test.

Example: =ISODD(7) gives TRUE.

A number with a fraction is cut to a whole number first, so 4.5 counts as 4. Text that isn't a number gives #TYPE.

ISNONTEXT Returns TRUE when a value is anything other than text.

ISNONTEXT(value)

value
The value or cell to test.

Example: =ISNONTEXT(C1) gives TRUE.

An empty cell isn't text, so it gives TRUE. Dates are text in Blackbear, so a date gives FALSE.

Column types in formulas

A formula reads a cell by its column's type. Set a column's type with Configure column on its header.

TypeHoldsIn formulas
TextAny text, such as names and notes.Formulas read it as text, and & joins it with other text.
NumberNumbers, shown with a prefix, suffix and decimal places you choose.Formulas read the number as typed. Its prefix, suffix and decimal places only change how it looks.
DateA day on the calendar.Formulas read it as a date written like 2026-11-02, which the date functions take.
SelectA choice from a list of options you set. Multi-select allows several.Formulas read the chosen option as text, so [Status] = "Done" checks for Done. Several options read as one text joined with commas, like Food, Home.
CheckboxA box to tick for yes or no.Formulas read a ticked box as TRUE and an unticked one as FALSE.
URLA web address that opens as a link.Formulas read the address as text.
TagsYour Blackbear tags, to group rows.Formulas can't read tags yet, so a tags cell reads as empty.
PersonThe people a row is assigned to, such as who owns it.Formulas can't read people yet, so a person cell reads as empty.

Errors

A formula that can't compute shows an error code in its cell. Select the cell to see what went wrong there. IFERROR catches any of them.

#REF Reference not found

The formula names a column, cell or page this sheet doesn't have.

A column name goes in brackets and must match its header exactly, capitals included: [Amount], not [amount]. A cell like C9 needs the sheet to have a ninth row, and 'Page'![Column] needs a page with that name. INDEX, VLOOKUP and HLOOKUP give it too when a row or column number is past the end of their range or array.

Excel shows #REF! for a reference that no longer exists and #NAME? for a name it doesn't know.

#TYPE Wrong kind of value

A value doesn't fit what the formula does with it: text where a number or a date belongs, a number compared with text, or a number the function can't take, like the square root of a negative number.

Look at the cells the formula reads. Text counts as a number only when all of it is one, so 12 kg doesn't, while 1,200 and $1,200 do. Join text with &, not +. A date is written YYYY-MM-DD, like 2026-11-02, and isn't a number yet, so ROUND and the other number functions give #TYPE on one.

Excel shows #VALUE! for the wrong kind of value and #NUM! for a number a function can't take. Comparing a number with text gives #TYPE here, where Excel puts every number before every text.

#DIV/0 Divided by zero

The formula divides by zero, or a function that divides found nothing to divide, like AVERAGEIF with no matching numbers.

Check the cell you divide by: an empty cell counts as 0. To show something else until it has a value, wrap the formula in IFERROR, like =IFERROR(C1 / C2, 0).

Excel shows #DIV/0! in these cases. One difference: AVERAGE with no numbers gives 0 here, where Excel gives #DIV/0!.

#CIRC Formulas in a loop

Formulas read each other in a circle, so none of them can finish. A formula that reads its own cell or column does this too.

Follow what the formula reads, and what those cells read, until you get back to where you started. Then change one formula in the loop to read something else.

Excel warns about a circular reference instead of showing a code in the cell.

#ARG Arguments not accepted

A function got the wrong number of arguments, or one it can't use, like a negative count or a format it doesn't support. IRR and RATE give it when they find no answer.

Compare the formula with the function's syntax in the function list. Arguments are separated by commas, and the ones in brackets are optional. Each argument's description says which values it takes.

Excel won't accept a formula with the wrong number of arguments, and shows #VALUE! or #NUM! for an argument it can't use.

#PARSE Formula can't be read

The text isn't a formula Blackbear can read, so nothing was computed. A function name Blackbear doesn't know gives it too.

Look for a missing ) or comma, a stray character, or text without quotes. Text in a formula goes in double quotes, like "Food", and a column name goes in brackets, like [Category]. Arguments are separated by commas, not semicolons; a semicolon only separates the rows of an array such as {1, 2; 3, 4}.

Excel won't accept a formula it can't read, and shows #NAME? for a function it doesn't know.

#N/A No value found

There is no value to give: a lookup or a search found no match, no condition in IFS was true, no case in SWITCH matched, or the formula uses NA() to mark a value as missing.

Check that the value you look for is in the range you search, spelled the same way and without extra spaces. Capitals don't matter. To show something else when nothing matches, use XLOOKUP's if_not_found argument or wrap the formula in IFNA, like =IFNA(MATCH("Tea", A1:A6, 0), 0).

Excel shows #N/A in the same cases.