Knowledgebase: Saica
Supported Spread Sheet Functions
Posted by Jason Standley, Last modified by Jason Standley on 10 October 2017 03:28 PM

                                    Database and List Management Functions

DAVERAGE

Indicates the average of the values that meet the specified criteria.

DCOUNT

Counts the number of cells containing numbers that meet the specified criteria.

DCOUNTA

Counts nonblank cells containing numbers or text that meet the specified criteria.

DGET

Returns a single value that meets the specified criteria.

DMAX

Extracts the highest value that meets the specified criteria.

DMIN

Extracts the lowest value that meets the specified criteria.

DPRODUCT

Returns the product of the values that meet the specified criteria.

DSTDEV

Estimates the standard deviation of a population, based on a sample of selected entries from the database.

DSTDEVP

Returns the calculation of the standard deviation of a population, based on the sum of the whole population.

DSUM

Returns the total of the values that meet the specified criteria.

DVAR

Estimates the variance of a sample population based on the values that meet the specified criteria.

DVARP

Returns the calculation of the true variance of an entire population based on the values that meet the specified criteria.

                                     Date and Time Functions

NOW

Returns the current date and time in the form of a serial number.

TODAY

Returns the current date as a serial number.

DATEVALUE

Converts date text to a DATEVALUE serial number.

DAY

Returns the corresponding day of the month serial number or date text from 1 to 31.

HOUR

Returns the hour as a serial number integer between 0 and 23.

MINUTE

Returns the serial number that corresponds to the minute.

MONTH

Returns the corresponding serial number of the month of a date between 1 and 12.

SECOND

Returns the seconds portion of a serial time value.

TIMEVALUE

Returns the decimal number for a given time.

YEAR

Returns the corresponding year as a serial number in the form of an integer.

WEEKDAY

Returns the corresponding day of the week as a serial number.

DAYS360

Returns the number of days between two set dates based on a 360-day year.

DATE

Returns the serial number that represents a date.

TIME

Returns the decimal value of a given time.

                                    Financial Functions

IRR

Returns the internal rate of return for a series of cash flows represented by numbers in the form of values.

NPV

Calculates the net present value of an investment from the discount rate and several future payments and income.

MIRR

Returns a modified internal rate of return for several periodic cash flows.

SLN

Returns the straight-line depreciation on an asset.

FV

Returns the future value of an investment that makes payments as a lump sum or as a series of equal periodic payments.

NPER

Returns the total number of periods for an investment. This is based on a periodic constant payment and a constant interest rate.

PMT

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

PV

Returns the present value based on an investment.

RATE

Returns per period the interest of an annuity.

ISPMT

Calculates the interest paid during a defined period of an investment.

SYD

Based on a specified period, SYD returns the sum-of-years' digits depreciation of an asset.

DB

Returns the asset depreciation for a period using the fixed declining balance method.

DDB

Returns the asset depreciation for a period using the double-declining balance method or another specified method.

IPMT

Returns the interest for a period of time based on an investment with periodic constant payments and a constant interest rate.

PPMT

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

VDB

For a period you specify, returns the depreciation of an asset.

                                    Information Functions

NA

An alternative representation of the error value #N/A.

 ERROR TYPE

Returns the corresponding number value associated with an error type in Microsoft Excel.

ISBLANK

Returns TRUE if the cell is empty, FALSE if it contains data.

ISERR

Returns TRUE if value contains any error value except #N/A, FALSE if it does not.

ISERROR

Returns TRUE if value contains any error value (including #N/A), FALSE if it does not.

ISLOGICAL

Returns TRUE if value is a logical value, FALSE if it is not.

ISNA

Returns TRUE if value is #N/A, FALSE if it is not.

ISNONTEXT

Returns TRUE if value is not text, FALSE if it is.

ISNUMBER

Returns TRUE if value is a number, FALSE if it is not.

ISREF

Returns TRUE if value is a reference, FALSE if it is not.

ISTEXT

Returns TRUE if value is text, FALSE if it is not.

N

Returns a value converted to a number.

TYPE

Determines the type of value in a cell.

                                    Logical Functions

FALSE

Returns the value FALSE. May be typed directly into the cell as "FALSE".

TRUE

Returns the value TRUE. May be typed directly into the cell as "TRUE".

NOT

Returns the reverse value of its arguments; TRUE becomes FALSE and FALSE becomes TRUE.

AND

Returns TRUE if all the arguments are TRUE in the formula, and FALSE if any one argument is FALSE.

OR

Returns FALSE if all arguments are FALSE, and TRUE if at least one argument is TRUE.

IF

Returns a value if one condition is TRUE and returns another value if the condition is FALSE.

                                    Lookup and Reference Functions

COLUMN

Returns the column number(s) based on a given reference.

ROW

Returns the row number based on a reference.

AREAS

Returns the number of areas based on a reference.

COLUMNS

Returns the number of columns based on an array or reference.

ROWS

Returns the number of rows in a reference or array.

TRANSPOSE

Returns a horizontal range of cells as vertical or vice versa.

HYPERLINK

Creates a shortcut to jump to a document stored on a network server.

INDIRECT

Returns the contents of a cell using its reference.

INDEX

Returns the value of an element selected by the row number and column letter indexes.

LOOKUP

Looks in the first row or column of a range or array, and returns the specified value from the same position in the last row or column of the range or array.

MATCH

Returns the relative position of an item in an array that matches a specified value in a specified order, or the position of an item.

ADDRESS

Given specified row and column numbers, creates a cell address as text.

CHOOSE

Returns an item from a list of values..

HLOOKUP

Searches for a specified value in an array or a table's top row.

VLOOKUP

Searches for a value in the leftmost column of a table and returns a value from the same row in a column number that you specify.

OFFSET

Returns a reference to a range that is a specific number of rows and columns from a cell or range of cells.

                                    Math and Trigonometry Functions

PI

Returns the approximate number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.

RAND

Returns an evenly distributed random number greater than or equal to 0 and less than 1. A new random number is returned every time the worksheet is calculated.

ABS

Returns the absolute value of a number.

ACOS

Returns the arccosine of a number in radians in the range 0 to pi.

ACOSH

Returns the inverse hyperbolic cosine of a number.

ASIN

Returns the arcsine of a number in radians in the range -pi/2 to pi/2.

ASINH

Returns the inverse hyperbolic sine of a number.

ATAN

Returns the arctangent of a number in radians in the range -pi/2 to pi/2

ATANH

Returns the inverse hyperbolic tangent of a number.

COS

Returns the cosine of the given angle.

COSH

Returns the hyperbolic cosine of a number.

DEGREES

Converts radians into degrees.

EVEN

Returns a number rounded up to the next even integer for positive integers and rounded down to the next even integer for negative numbers.

EXP

Returns e (2.71828182845804) raised to the power of a specified number.

FACT

Returns the factorial of a number.

INT

Rounds a number down to the nearest integer.

LN

Returns the natural (base e) logarithm of a number.

LOG10

Returns the base-10 logarithm of a number.

MDETERM

Returns the matrix determinant of an array.

MINVERSE

Returns the inverse matrix for the matrix stored in an array.

ODD

Returns a number rounded up away from zero to the nearest odd integer.

RADIANS

Converts degrees to radians.

SIGN

Determines the sign of a number. Returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.

SIN

Returns the sine of a given angle.

SINH

Returns the hyperbolic sine of a number.

SQRT

Returns a positive square root.

TAN

Returns the tangent of the given angle.

TANH

Returns the hyperbolic tangent of a number.

LOG

Returns the logarithm of a number of the base you specify.

ROMAN

Converts an Arabic numeral to Roman, as text.

TRUNC

Truncates a number to an integer by removing the fractional part of a number.

PRODUCT

Multiplies all the numbers given as arguments and returns the product.

SUM

Adds all the numbers in a range of cells.

SUMSQ

Returns the sum of the squares of the arguments.

ATAN2

Returns the four-quadrant arctangent of the specified x- and y- coordinates in radians between -pi and pi excluding -pi. A positive result represents a counterclockwise angle from the x-axis, a negative result represents a clockwise angle.

CEILING

Returns a number rounded up, away from zero, to the nearest multiple of significance.

COMBIN

Returns the number of combinations for a given number of items.

FLOOR

Returns a number rounded down, toward zero, to the nearest multiple of significance.

MMULT

Returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.

MOD

Returns the remainder of a division operation (modulus).

POWER

Returns the result of a specified number raised to a specified power.

ROUND

Round a number to a specified number of digits.

ROUNDDOWN

Rounds a number down, towards zero.

ROUNDUP

Rounds a number up, away from zero.

SUMX2MY2

Returns the sum of the difference of squares of corresponding values in two arrays.

SUMX2PY2

Returns the sum of the sum of squares of corresponding values in two arrays.

SUMXMY2

Returns the sum of squares of differences of corresponding values in two arrays.

SUMIF

Adds the cells specified by a certain criteria.

SUMPRODUCT

Multiplies corresponding components in the given arrays, and returns the sum of those products.

                                    Statistical Functions

COUNTBLANK

Counts the empty cells in a specified range.

FISHER

Returns the Fisher transformation at x.

FISHERINV

Returns the inverse of the Fisher transformation at y.

GAMMALN

Returns the natural logarithm of the gamma function.

NORMSDIST

Returns the standard normal cumulative distribution function.

NORMSINV

Returns the inverse of the standard normal cumulative distribution function.

GROWTH

Calculates predicted exponential growth by using existing data.

LINEST

Calculates a straight line that best fits your data using the least squares method.

LOGEST

Calculates an exponential curve that fits your data and returns an array of values that describes the curve.

TREND

Returns the y-values along a linear trendline that best fits the values in a data set.

AVEDEV

Retuns the average of the absolute deviations of data points from their mean.

AVERAGE

Returns the average of its arguments.

AVERAGEA

Returns the average of the values in its list of arguments including text and logical values.

COUNT

Counts the number of cells that contain numbers (including dates and formulas that evaluate to numbers) within the list of arguments.

COUNTA

Counts the number of cells that are not empty.

DEVSQ

Returns the sum of the squares of deviations of a data set from their sample mean.

GEOMEAN

Returns the geometric mean of an array or range of positive data.

HARMEAN

Returns the harmonic mean of a data set.

KURT

Returns the Kurtosis of a data set.

MAX

Returns the largest value in a set of values.

MAXA

Returns the largest value in a set of values including text and logical values.

MEDIAN

Returns the median of the given numbers.

MIN

Returns the smallest value in a set of values.

MINA

Returns the smallest value in a set of values including text and logical values.

MODE

Returns the most frequently occuring, or repetitive, number in an array or range of data.

SKEW

Returns the skew of a distribution.

STDEV

Estimates standard deviation based on a sample.

STDEVA

Estimates standard deviation based on a sample. Includes text and logical values.

STDEVP

Estimates standard deviation based on a sample assuming that the arguments represent the total population.

STDEVPA

Estimates standard deviation based on a sample assuming that the arguments represent the total population. Includes text and logical values.

VAR

Returns an estimate for the variance of a population based on a sample data set.

VARA

Returns an estimate for the variance of a population based on a sample data set and may include text or logical values.

VARP

Calculates variance based on the entire population.

VARPA

Calculates variance based on the entire population and may include text or logical values.

CHIDIST

Returns the one-tailed probability of the chi-squared (X^2) distribution; the area in the right tail under the chi-squared distribution curve.

CHIINV

Returns the inverse of the one-tailed probability of the chi-squared (X^2) distribution.

CHITEST

Returns the test for independence of the characteristics in a table.

CORREL

Returns the correlation coefficient between two data sets.

COUNTIF

Counts the number of cells in a range that meet a given criteria.

COVAR

Returns the covariance, the average of products of deviations, for each data point pair.

FREQUENCY

Calculates how often values occur within a range of values and then returns a vertical array of numbers.

FTEST

Returns the result of an F-test.

INTERCEPT

Calculates the point at which a line will intersect the y-axis by using existing x and y values.

LARGE

Returns the k-th largest value in a data set.

PEARSON

Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.

PERCENTILE

Returns the k-th percentile of values in a range.

PERMUT

Returns the number of permutations for a given number of objects that can be selected from a range of numbers.

QUARTILE

Returns the quartile of a data set.

RSQ

Returns the r^2 value of a linear regression line.

SLOPE

Returns the slope of a regression line through data points in KNOWN_Y'S and KNOWN_X'S.

SMALL

Returns the k-th smallest value in a data set.

STEYX

Returns the standard error of the predicted y value for each x in the regression.

TINV

Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.

TRIMMEAN

Returns the mean of the interior of a data set.

PERCENTRANK

Returns the rank of a value in a data set set as a percentage of the data set.

RANK

Returns the rank of a number in a list of numbers.

ZTEST

Returns the two-tailed P-value of a z-test.

CONFIDENCE

Returns the confidence interval for a population mean.

CRITBINOM

Returns the minimum number yields a binomial distribution less than or equal to the specified criteria

EXPONDIST

Returns the exponential distribution.

FDIST

Returns the F probability distribution.

FINV

Returns the inverse of the F probability distribution.

FORECAST

Calculates or predicts a future value by using existing values.

GAMMAINV

Returns the inverse of the gamma cumulative distribution.

LOGINV

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is normally distributed with parameters mean and standard deviation.

LOGNORMDIST

Returns the cumulative lognormal distribution of x, where ln(x) is normally distributed with parameters mean and standard deviation.

NEGBINOMDIST

Returns the negative binomial distribution.

NORMINV

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

POISSON

Returns the Poisson distribution.

STANDARDIZE

Returns a normalized value from a distribution characterized by MEAN and STANDARD_DEV.

TDIST

Returns the percentage points (probability) for the student t-distribution, where a numeric value (x) is a calculated value of t for which the percentage points are to be computed.

PROB

Returns the probability that values in a range are between two specified limits.

BETADIST

Returns the cumulative beta probability density function.

BETAINV

Returns the inverse of the cumulative beta probability density function.

BINOMDIST

Returns the individual term binomial distribution probability.

GAMMADIST

Returns the gamma distribution.

HYPGEOMDIST

Returns the hypergeometric distribution.

NORMDIST

Returns the normal cumulative distribution for the specified mean and standard deviation.

TTEST

The probability associated with t-test.

WEIBULL

Returns the Weibull distribution.

                                    Text Functions

CHAR

Returns the character specified by a number.

CLEAN

Removes all nonprintable characters from text.

CODE

Returns a numeric code from the first character in a text string. The opposite of the CHAR function.

LEN

Returns the number of characters in a text string.

LENB

Returns the number of characters in a text string expressed in bytes.

LOWER

Converts all letters in a text string to lowercase.

PROPER

Capitalizes the first letter of each word in a text string or sentence.

T

Returns the text referred to by a value.

TRIM

Removes all spaces from text except single spaces between words.

UPPER

Converts text to uppercase.

VALUE

Converts a text string that represents a number to a number.

DOLLAR

Converts a number to text using Currency format, with the decimals rounded to the specified place.

USDOLLAR

Converts a number to text using US Dollar format, with the decimals rounded to the specified place.

LEFT

Returns the first character(s) in a text string.

LEFTB

Returns the first character(s) in a text string based on a specified number of bytes

RIGHT

Returns the last character(s) in a text string.

RIGHTB

Returns the last character(s) in a text string based on a specified number of bytes.

FIXED

Rounds a number to a specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

CONCATENATE

Joins several text strings into one text string.

EXACT

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

REPT

Repeats specified text a given number of times.

TEXT

Converts a value to text in a specific number format.

FIND

Locates one text string within another text string, and returns the number of the starting position of of FIND_TEXT from the leftmost character of WITHIN_TEXT.

FINDB

Returns the position of specified text within another specified text string based on the number of bytes each character uses from the first character of WITHIN_TEXT.

SEARCH

Returns the number of the character at which a specific character or text string is first found, reading from left to right.

SEARCHB

Returns the number of the character at which a specific character or text string is first found in bytes, reading from left to right.

MID

Returns a specific number of characters from a text string starting at the position you specify.

MIDB

Returns a group of characters based on a specified number of bytes from a text string starting at the position you specify.

SUBSTITUTE

Substitutes NEW_TEXT for OLD_TEXT in a string.

REPLACE

Replaces part of a text string with a different text string based on the number of characters you specify.

REPLACEB

Replaces part of a text string with a different text string based on the number of characters you specify in terms of bytes.

 

Thank you!

Support


Comments (0)