Difference between revisions of "Template:Calc functions"

Revision as of 17:08, 25 September 2008

Calc functions listed by category

These pages are a complete rewrite of the Function Help for Calc, with links to other relevant topics. The aim is to have more detail and support than the Help pages for other major spreadsheets.

You may

• navigate directly to the functions from this page, or
• select a function category, to find a one line description of each function and explanatory notes on the category, or
• see a list of functions in alphabetic order.

Array functions

The Array functions operate on and return entire tables of data.

· FREQUENCY, GROWTH, LINEST, LOGEST, MDETERM, MINVERSE, MMULT, MUNIT, SUMPRODUCT, SUMX2MY2, SUMX2PY2, SUMXMY2, TRANSPOSE, TREND

Complex Number functions

The Complex Number functions operate on (mathematical) complex numbers such as 2 + 3i.

· COMPLEX, IMABS, IMAGINARY, IMPOWER, IMARGUMENT, IMCOS, IMDIV, IMEXP, IMCONJUGATE, IMLN, IMLOG10, IMLOG2, IMPRODUCT, IMREAL, IMSIN, IMSUB, IMSUM, IMSQRT

Database functions

The Database functions are used to extract information from Calc data tables, where data is organised in rows.

· DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DAVERAGE, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP

Date & Time functions

The Date & Time functions are used to manipulate dates and times.

· TODAY, NOW, YEAR, MONTH, DAY, WEEKDAY, HOUR, MINUTE, SECOND, DATE, DATEVALUE, TIME, TIMEVALUE, EOMONTH, EDATE, WORKDAY, NETWORKDAYS, DAYS, DAYS360, DAYSINMONTH, DAYSINYEAR, WEEKS, WEEKSINYEAR, WEEKNUM, WEEKNUM_ADD, MONTHS, YEARS, ISLEAPYEAR, EASTERSUNDAY, YEARFRAC

Financial functions

The Financial functions provide common business calculations.

· AMORDEGRC, AMORLINC, DB, DDB, SLN, SYD, VDB

· CUMIPMT, CUMIPMT_ADD, CUMPRINC, CUMPRINC_ADD, FV, FVSCHEDULE, IPMT, IRR, ISPMT, MIRR, NPER, NPV, PMT, PPMT, PV, RATE, RRI, XIRR, XNPV

· ACCRINT, ACCRINTM, DISC, DURATION, DURATION_ADD, EFFECTIVE, EFFECT_ADD, INTRATE, MDURATION, NOMINAL, NOMINAL_ADD, ODDFPRICE, ODDFYIELD, ODDLPRICE, ODDLYIELD, PRICE, PRICEDISC, PRICEMAT, RECEIVED, TBILLEQ, TBILLPRICE, TBILLYIELD, YIELD, YIELDDISC, YIELDMAT

· COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPNCD, COUPNUM, COUPPCD

· DOLLARDE, DOLLARFR

Information functions

The Information functions return information about cells, such as whether they contain text or a formula, etc.

· CELL, CURRENT, FORMULA, ISBLANK, ISERR, ISERROR, ISEVEN, ISEVEN_ADD, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD, ISODD_ADD, ISREF, ISTEXT, N, NA, TYPE

Logical functions

The Logical functions allow boolean logic decisions.

· AND, FALSE, IF, NOT, OR, TRUE

Mathematical functions

The Mathematical functions include trigonometric, hyperbolic, logarithmic and summation functions.

· COS, SIN, TAN, COT, ACOS, ACOT, ASIN, ATAN, ATAN2, DEGREES, RADIANS, PI

· COSH, SINH, TANH, COTH, ACOSH, ACOTH, ASINH, ATANH

· TRUNC, ROUND, ROUNDDOWN, ROUNDUP, CEILING, FLOOR, EVEN, ODD, MROUND, INT, QUOTIENT, MOD

· EXP, POWER, LOG, LN, LOG10

· ABS, COMBIN, COMBINA, CONVERT, CONVERT_ADD, COUNTBLANK, COUNTIF, DELTA, ERF, ERFC, FACT, FACTDOUBLE, GCD, GCD_ADD, GESTEP, ISEVEN, ISODD, LCM, LCM_ADD, MULTINOMIAL, PRODUCT, RAND, RANDBETWEEN, SERIESSUM, SIGN, SQRT, SQRTPI, SUBTOTAL, SUM, SUMIF, SUMSQ

· BESSELI, BESSELJ, BESSELK, BESSELY

Number Conversion functions

The Number Conversion functions convert numbers between forms.

· BIN2DEC, BIN2HEX, BIN2OCT, DEC2BIN, DEC2HEX, DEC2OCT, HEX2BIN, HEX2DEC, HEX2OCT, OCT2BIN, OCT2DEC, OCT2HEX

· ARABIC, BAHTTEXT, DECIMAL, ROMAN

Spreadsheet functions

The 'Spreadsheet' functions find values in tables, or cell references.

· ADDRESS, CHOOSE, HLOOKUP, INDEX, INDIRECT, LOOKUP, MATCH, OFFSET, VLOOKUP

· AREAS, COLUMN, COLUMNS, ERRORTYPE, INFO, ROW, ROWS, SHEET, SHEETS

· DDE, HYPERLINK, STYLE

Statistical functions

The Statistical functions provide statistical and probability calculations.

· AVEDEV, AVERAGE, AVERAGEA, COUNT, COUNTA, GEOMEAN, HARMEAN, MEDIAN, MODE, TRIMMEAN

· DEVSQ, KURT, SKEW, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA

· B, BETADIST, BETAINV, BINOMDIST, CHIDIST, CHIINV, CHITEST, CONFIDENCE, CRITBINOM, EXPONDIST, FINV, FISHER, FISHERINV, FDIST, FTEST, GAMMAINV, GAMMALN, GAMMADIST, GAUSS, HYPGEOMDIST, LOGINV, LOGNORMDIST, NEGBINOMDIST, NORMINV, NORMDIST, NORMSINV, NORMSDIST, PERMUT, PERMUTATIONA, PHI, POISSON, PROB, STANDARDIZE, TDIST, TINV, TTEST, WEIBULL, ZTEST

· CORREL, COVAR, FORECAST, INTERCEPT, PEARSON, RSQ, SLOPE, STEYX

· LARGE, MAX, MAXA, MIN, MINA, PERCENTILE, PERCENTRANK, QUARTILE, RANK, SMALL

Text functions

The Text functions manipulate or return text.

· BASE, CHAR, CLEAN, CODE, CONCATENATE, DOLLAR, EXACT, FIND, FIXED, LEFT, LEN, LOWER, MID, PROPER, REPLACE, REPT, RIGHT, SEARCH, SUBSTITUTE, T, TEXT, TRIM, UPPER, VALUE

See also

Functions listed alphabetically