Documentation/Formulas

Formula Reference

SheetRocks supports 150+ spreadsheet functions. Click any function to see detailed documentation with examples.

Math Functions

Returns the absolute value of a number

Returns the arccosine, or inverse cosine, of a number

Returns the arcsine, or inverse sine, of a number

Returns the arctangent, or inverse tangent, of a number

Rounds a number up to the nearest integer or to the nearest multiple of significance

Returns the cosine of the given angle

Returns e raised to the power of a given number

Returns the factorial of a number

Rounds a number down to the nearest multiple of significance

Rounds a number down to the nearest integer

Returns the natural logarithm of a number

Returns the logarithm of a number to a specified base

Returns the base-10 logarithm of a number

Returns the remainder after a number is divided by a divisor

Returns the value of pi (π)

Returns the result of a number raised to a power

Returns a random number greater than or equal to 0 and less than 1.

Returns the number after rounding it to a certain number of decimal places.

Returns a number rounded to a certain number of decimal places. ROUNDDOWN rounds all numbers down.

Returns a number rounded up to a certain number of decimal places. ROUNDUP rounds all numbers up.

Determines the sign of a number

Returns the sine of the given angle

Returns the square root of a number

Returns the sum of all numbers in the provided arguments

Returns the tangent of the given angle

Truncates a number to an integer by removing the fractional part

Statistical Functions

Returns the average of the absolute deviations of data points from their mean

Returns the average of given arguments. Text is ignored.

Returns the correlation coefficient between two data sets

Returns the count of numeric values in a dataset.

Returns the count of values in a list of arguments including text, logical values, error values, and empty text.

Returns the count of cells that meet a specified criteria

Returns the count of criteria_ranges based on the given criterions.

Calculates, or predicts, a future value by using existing values

Returns the k-th largest value in a data set

Returns the largest number from the provided arguments

Returns the median of the given numbers

Returns the smallest number from the provided arguments

Returns the most frequently occurring value in a data set

Returns the k-th percentile of values in a range

Returns the rank of a number from the list of other numeric values

Returns the k-th smallest value in a data set

Returns the estimated standard deviation based on a sample

Calculates standard deviation based on the entire population

Returns the sum of a range depending on criteria

Returns the sum of products for a list of arguments

Returns the estimated variance for a sample of data

Calculates variance based on the entire population

Financial Functions

Returns the cumulative interest paid between start_period and end_period

Returns the cumulative principal paid on a loan between start_period and end_period

Returns the depreciation of an asset for a specified period using the fixed-declining balance method

Returns the discount rate for a security

Returns the effective annual interest rate

Returns the future value of an investment.

Returns the future value of an initial principal after applying a series of compound interest rates

Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

Returns the internal rate of return for a series of cash flows

Returns the annual nominal interest rate

Returns the number of periods for an investment

Calculates the net present value of a series of cash flows at a given discount rate

Calculates the payment for a loan based on constant payments and a constant interest rate.

Returns the payment on the principal for a given period for an investment based on periodic, constant payments and a constant interest rate.

Returns the present value of an investment.

Returns the interest rate per period of a loan or investment.

Returns the straight-line depreciation of an asset for one period

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic

Returns the net present value for a schedule of cash flows that is not necessarily periodic

Text Functions

Removes all nonprintable characters from text

Returns the concatenation of a list of arguments.

Returns TRUE if the text ends with search text, returns FALSE otherwise.

Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise

Returns the position of the first occurrence from within_text.

Formats a number as text with a fixed number of decimals

Returns a substring from the left side of a supplied string.

Returns the length of a given string.

Returns a string after converting it to lowercase

Returns a substring from the middle of a given string.

Capitalizes the first letter in each word of a text value

Returns true if the text matches the regular expression pattern; otherwise returns false.

Replaces part of a text string with a different text string

Repeats text a given number of times

Returns a substring from the right side of a given string.

Returns the start position of the find_text string from the first character of the text string.

Returns TRUE if the text starts with search text, returns FALSE otherwise.

Returns the text after replacing one or more text strings with another text string

Returns the combination of two or more one-dimensional arrays with or without a delimiter.

Removes all spaces from a text string except for single spaces between words.

Returns the string after converting all letters to uppercase

Returns a numeric value for a text that appears in a recognized format (i.e. a number, date, or time format).

Date/Time Functions

Constructs a date from year, month, and day values.

Calculates the number of days, months, or years between two dates

Extracts the date component from a datetime value, removing the time portion. The result is in workbook time zone.

Returns the day of the month from a date value.

Returns the day of the week as a text string

Returns the number of days between two dates

Returns a date on the same day of the month, n months in the past or future.

Returns last day of a month as a serial number after adding a specified number of months before or after another date.

Returns the hour component of a specific time as a number between 0-23.

Returns the ISO-8601 week number (1–53) of the year for a given date.

Returns the minutes of a time value

Returns the month from a date value (1-12).

Returns the number of work days between two dates, excluding saturday and sunday.

Returns the current date and time as a date value in UTC.

Returns the seconds of a time value

Returns the created time with individual hour, minute and second components.

Returns the decimal number of the time represented by a string. The decimal number is a value ranging from 0 (inclusive) to 1 (exclusive).

Returns the current date as a date value in UTC with time stripped.

Returns the number that corresponds to the week of provided date.

Returns the week number of a specific date

Returns the date that is the specified number of working days before or after the start date.

Returns the year from a date value.

Calculates the fraction of the year represented by the number of whole days between two dates

Logical Functions

Returns true if all arguments evaluate to true

Returns true if a range contains a specified value

Returns the first value if logical_expression is TRUE; otherwise returns the second value.

Returns a custom result when a formula generates an error, and a normal result when no error is detected.

Returns the value corresponding to the first TRUE result when evaluating multiple tests.

Returns true if a provided number is between two other numbers either inclusively or exclusively; otherwise returns false.

Returns true if the value is blank, empty, or null; otherwise returns false.

Returns TRUE if the value is any error value

Returns TRUE if the number is even, FALSE if odd

Returns TRUE if the value is the #N/A error value

Returns TRUE if the value is a number

Returns TRUE if the number is odd, FALSE if even

Returns TRUE if the value is text

Returns FALSE if logical expression is TRUE and TRUE if logical expression is FALSE.

Returns true if any argument evaluates to true

Data Functions

Uses index to return a value from the list of value arguments

Returns the column number of the current cell context.

Drops the first N rows and optionally first N columns from an array. Returns an error if the result would be empty.

Returns a filtered array containing only rows that match the criteria

Groups data by a key and applies an aggregator function to each group

Returns the corresponding value from the specified row of table which matches exactly or approximately to the first row of the table.

Horizontally stacks arrays by appending columns

Returns a cell value from a list or table based on its column and row numbers.

Returns the last N rows and optionally last N columns from an array. Returns an error if the result would be empty.

Processes each row in an array with a formula including a COL or CELL keyword and returns it as a 2D array.

Returns the position of the item/lookup_value in the lookup_array or range.

Selects specific columns from an array using column numbers or header names

Returns the 1-indexed row number of the current cell

Returns the list of generated sequential numbers in an array.

Sorts the values in a range or array.

Returns the first N rows and optionally first N columns from an array. Returns an error if the result would be empty.

Returns the result after transposing the rows and columns of an array or range.

Returns a list of unique values in a list or range.

Returns a value from a table by looking up a value in the first column and returning a value from the specified column

Vertically stacks arrays by appending rows

Special Functions

Returns the value of the inner formula and only executes once, turning the cell into a value

Returns initial_value until the range changes, then returns value_after_change and converts the cell to a static value

Converts a table with headers and rows into a JSON object array. There should be a "label" column and each row is rendered as a hoverable tag with that label.