- Home
- Functions
536 spreadsheet functions
Syntax, arguments and examples for every function SheetsBat calculates, with results worked out by the engine itself. Names and arguments match what spreadsheet users already know.
Math 86 functions
Arithmetic, rounding, sums with conditions, powers, logs, trigonometry, matrices and random numbers.
- ABSReturns the absolute value of a number (the number without its sign).
- ACOSReturns the arccosine of a number, in radians.
- ACOSHReturns the inverse hyperbolic cosine of a number.
- ACOTReturns the arccotangent of a number, in radians from 0 to pi.
- ACOTHReturns the inverse hyperbolic cotangent of a number.
- ADDReturns the sum of two numbers, like the + operator.
- AGGREGATEReturns a sum, average, count, percentile and more of a range, with options to skip hidden rows, errors and other subtotals.
- ARABICConverts a Roman numeral written as text to a number.
- ASINReturns the arcsine of a number, in radians.
- ASINHReturns the inverse hyperbolic sine of a number.
- ATANReturns the arctangent of a number, in radians.
- ATAN2Returns the angle, in radians, between the x axis and the line to the point (x_num, y_num).
- ATANHReturns the inverse hyperbolic tangent of a number.
- BASEConverts a number to text in another base (radix), padded with zeros to a minimum length.
- CEILINGRounds a number up to the nearest multiple of significance.
- CEILING.MATHRounds a number up to the nearest integer or multiple; mode chooses the direction for negative numbers.
- CEILING.PRECISERounds a number up to the nearest multiple, whatever the sign of the number or significance.
- COMBINReturns the number of combinations of a number of items taken a given number at a time.
- COMBINAReturns the number of combinations of items, with repetitions allowed.
- COSReturns the cosine of an angle given in radians.
- COSHReturns the hyperbolic cosine of a number.
- COTReturns the cotangent of an angle given in radians.
- COTHReturns the hyperbolic cotangent of a number.
- CSCReturns the cosecant of an angle given in radians.
- CSCHReturns the hyperbolic cosecant of a number.
- DECIMALConverts text that represents a number in another base to a decimal number.
- DEGREESConverts an angle from radians to degrees.
- DIVIDEReturns one number divided by another, like the / operator.
- EVENRounds a number away from zero to the nearest even integer.
- EXPReturns e raised to the power of a number.
- FACTReturns the factorial of a number (1*2*3*...*number).
- FACTDOUBLEReturns the double factorial of a number (every other integer multiplied down to 1 or 2).
- FLOORRounds a number down to the nearest multiple of significance.
- FLOOR.MATHRounds a number down to the nearest integer or multiple; mode chooses the direction for negative numbers.
- FLOOR.PRECISERounds a number down to the nearest multiple, whatever the sign of the number or significance.
- GCDReturns the greatest common divisor of whole numbers.
- INTRounds a number down to the nearest integer.
- ISO.CEILINGRounds a number up to the nearest multiple, whatever the sign of the number or significance.
- LCMReturns the least common multiple of whole numbers.
- LNReturns the natural logarithm of a number.
- LOGReturns the logarithm of a number to the base you choose (10 when left out).
- LOG10Returns the base 10 logarithm of a number.
- MDETERMReturns the determinant of a square matrix.
- MINUSReturns the difference of two numbers, like the - operator.
- MINVERSEReturns the inverse of a square matrix.
- MMULTReturns the matrix product of two arrays.
- MODReturns the remainder after dividing a number by a divisor; the result has the sign of the divisor.
- MROUNDRounds a number to the nearest multiple of another number.
- MULTINOMIALReturns the factorial of a sum of values divided by the product of their factorials.
- MULTIPLYReturns the product of two numbers, like the * operator.
- MUNITReturns the identity matrix of a given size.
- ODDRounds a number away from zero to the nearest odd integer.
- PIReturns the number pi, 3.14159265358979.
- POWERReturns a number raised to a power.
- PRODUCTMultiplies numbers and the numbers in ranges.
- QUOTIENTReturns the whole number part of a division.
- RADIANSConverts an angle from degrees to radians.
- RANDReturns a random number from 0 up to (not including) 1, new on every recalculation.
- RANDARRAYReturns an array of random numbers; you choose the size, the range and whether they are whole numbers.
- RANDBETWEENReturns a random whole number between two numbers (both included).
- ROMANConverts a number to Roman numerals as text; form 0 is classic and 1 to 4 are ever more concise.
- ROUNDRounds a number to a given number of digits, halves away from zero.
- ROUNDDOWNRounds a number toward zero to a given number of digits.
- ROUNDUPRounds a number away from zero to a given number of digits.
- SECReturns the secant of an angle given in radians.
- SECHReturns the hyperbolic secant of a number.
- SERIESSUMReturns the sum of a power series: a1*x^n + a2*x^(n+m) + ...
- SIGNReturns 1 for a positive number, -1 for a negative number and 0 for zero.
- SINReturns the sine of an angle given in radians.
- SINHReturns the hyperbolic sine of a number.
- SQRTReturns the square root of a number.
- SQRTPIReturns the square root of a number multiplied by pi.
- SUBTOTALReturns a subtotal (sum, average, count and more) of ranges, skipping other subtotals; codes 101 to 111 also skip hidden rows.
- SUMAdds numbers and the numbers in ranges.
- SUMIFAdds the cells that meet a condition.
- SUMIFSAdds the cells that meet all of several conditions.
- SUMPRODUCTMultiplies matching items of arrays or ranges and returns the sum of those products.
- SUMSQReturns the sum of the squares of the numbers.
- SUMX2MY2Returns the sum of the differences of the squares of matching values in two arrays.
- SUMX2PY2Returns the sum of the sums of the squares of matching values in two arrays.
- SUMXMY2Returns the sum of the squares of the differences of matching values in two arrays.
- TANReturns the tangent of an angle given in radians.
- TANHReturns the hyperbolic tangent of a number.
- TRUNCCuts a number down to a given number of digits by removing the rest.
- UMINUSReturns a number with its sign reversed.
- UNARY_PERCENTReturns a number divided by 100, like the % operator.
Statistical 111 functions
Averages, counts, spread, ranks, percentiles, forecasting, regression and probability distributions.
- AVEDEVReturns the average of the absolute deviations of the numbers from their mean.
- AVERAGEReturns the average (arithmetic mean) of the numbers.
- AVERAGEAReturns the average of the values, counting text as 0 and TRUE as 1.
- AVERAGEIFReturns the average of the cells that meet a condition.
- AVERAGEIFSReturns the average of the cells that meet all of several conditions.
- BETA.DISTReturns the beta distribution, optionally scaled to an interval from A to B.
- BETA.INVReturns the inverse of the cumulative beta distribution.
- BINOM.DISTReturns the binomial distribution probability of a number of successes in a number of trials.
- BINOM.DIST.RANGEReturns the probability that the number of successes falls between two values.
- BINOM.INVReturns the smallest number of successes for which the cumulative binomial distribution reaches a probability.
- CHISQ.DISTReturns the left tailed chi squared distribution.
- CHISQ.DIST.RTReturns the right tailed probability of the chi squared distribution.
- CHISQ.INVReturns the inverse of the left tailed chi squared distribution.
- CHISQ.INV.RTReturns the inverse of the right tailed chi squared distribution.
- CHISQ.TESTReturns the chi squared test of independence: the probability of the observed values given the expected ones.
- CONFIDENCE.NORMReturns the margin of error of a population mean using a normal distribution.
- CONFIDENCE.TReturns the margin of error of a population mean using Student's t distribution.
- CORRELReturns the correlation coefficient of two sets of numbers.
- COUNTCounts how many numbers are in a list or range.
- COUNTACounts the cells and values that are not empty.
- COUNTBLANKCounts the empty cells in a range, including cells holding empty text.
- COUNTIFCounts the cells in a range that meet a condition.
- COUNTIFSCounts the cells that meet all of several conditions.
- COVARIANCE.PReturns the population covariance of two sets of numbers.
- COVARIANCE.SReturns the sample covariance of two sets of numbers.
- DEVSQReturns the sum of squared deviations of the numbers from their mean.
- EXPON.DISTReturns the exponential distribution.
- F.DISTReturns the left tailed F probability distribution.
- F.DIST.RTReturns the right tailed F probability distribution.
- F.INVReturns the inverse of the left tailed F distribution.
- F.INV.RTReturns the inverse of the right tailed F distribution.
- F.TESTReturns the two tailed probability of an F test that two samples have the same variance.
- FISHERReturns the Fisher transformation of a value between -1 and 1.
- FISHERINVReturns the inverse of the Fisher transformation.
- FORECASTPredicts a value for x from the straight line that best fits known x and y values.
- FORECAST.ETSPredicts a future value with additive exponential triple smoothing (level, trend and season).
- FORECAST.ETS.CONFINTReturns the half width of the confidence interval around a FORECAST.ETS prediction.
- FORECAST.ETS.SEASONALITYReturns the length of the repeating pattern detected in a time series (1 when there is none).
- FORECAST.ETS.STATReturns a statistic of the exponential smoothing model: 1 alpha, 2 beta, 3 gamma, 4 MASE, 5 SMAPE, 6 MAE, 7 RMSE, 8 step size.
- FORECAST.LINEARPredicts a value for x from the straight line that best fits known x and y values.
- FREQUENCYCounts how many values fall into each bin and returns a vertical list with one more entry than there are bins.
- GAMMAReturns the gamma function value.
- GAMMA.DISTReturns the gamma distribution.
- GAMMA.INVReturns the inverse of the cumulative gamma distribution.
- GAMMALNReturns the natural logarithm of the gamma function.
- GAMMALN.PRECISEReturns the natural logarithm of the gamma function.
- GAUSSReturns the probability that a standard normal value lies between the mean and z standard deviations from it.
- GEOMEANReturns the geometric mean of positive numbers.
- GROWTHReturns values along the exponential growth curve that fits the known data.
- HARMEANReturns the harmonic mean of positive numbers.
- HYPGEOM.DISTReturns the hypergeometric distribution: the probability of a number of successes in a sample drawn without replacement.
- INTERCEPTReturns where the best fit straight line crosses the y axis.
- KURTReturns the kurtosis of the numbers (how peaked or flat their distribution is).
- LARGEReturns the k-th largest number in a list.
- LINESTFits a straight line (or a plane for several x columns) by least squares and returns its coefficients and optional statistics.
- LOGESTFits an exponential curve y = b * m^x to the data and returns its coefficients and optional statistics.
- LOGNORM.DISTReturns the lognormal distribution of x, where ln(x) is normally distributed.
- LOGNORM.INVReturns the inverse of the cumulative lognormal distribution.
- MAXReturns the largest number.
- MAXAReturns the largest value, counting text as 0 and TRUE as 1.
- MAXIFSReturns the largest number among the cells that meet all conditions.
- MEDIANReturns the middle number of the values (the average of the two middle ones for an even count).
- MINReturns the smallest number.
- MINAReturns the smallest value, counting text as 0 and TRUE as 1.
- MINIFSReturns the smallest number among the cells that meet all conditions.
- MODE.MULTReturns a vertical list of all values that occur most often.
- MODE.SNGLReturns the value that occurs most often.
- NEGBINOM.DISTReturns the probability of a number of failures before a given number of successes.
- NORM.DISTReturns the normal distribution for a mean and standard deviation.
- NORM.INVReturns the value at which the normal distribution reaches a probability.
- NORM.S.DISTReturns the standard normal distribution (mean 0, standard deviation 1).
- NORM.S.INVReturns the value at which the standard normal distribution reaches a probability.
- PEARSONReturns the Pearson correlation coefficient of two sets of numbers.
- PERCENTILE.EXCReturns the k-th percentile of the values, where k is strictly between 0 and 1.
- PERCENTILE.INCReturns the k-th percentile of the values, where k is from 0 to 1 inclusive.
- PERCENTRANK.EXCReturns the rank of a value in a data set as a percentage strictly between 0 and 1.
- PERCENTRANK.INCReturns the rank of a value in a data set as a percentage from 0 to 1 inclusive.
- PERMUTReturns the number of ordered arrangements of a number of items chosen from a set.
- PERMUTATIONAReturns the number of arrangements of items chosen from a set when items can repeat.
- PHIReturns the density of the standard normal distribution at a value.
- POISSON.DISTReturns the Poisson distribution probability of a number of events.
- PROBReturns the probability that values fall between two limits, given the probability of each value.
- QUARTILE.EXCReturns a quartile of the values using exclusive percentiles: 1, 2 or 3.
- QUARTILE.INCReturns a quartile of the values: 0 minimum, 1 first quartile, 2 median, 3 third quartile, 4 maximum.
- RANK.AVGReturns the rank of a number in a list; equal values get the average of their ranks.
- RANK.EQReturns the rank of a number in a list; equal values share the top rank.
- RSQReturns the square of the correlation coefficient (the R squared of a straight line fit).
- SKEWReturns the skewness of a sample (how lopsided the distribution is).
- SKEW.PReturns the skewness of a whole population.
- SLOPEReturns the slope of the straight line that best fits the data.
- SMALLReturns the k-th smallest number in a list.
- STANDARDIZEReturns how many standard deviations a value is from the mean.
- STDEV.PReturns the standard deviation of a whole population.
- STDEV.SEstimates the standard deviation from a sample.
- STDEVAEstimates the standard deviation from a sample, counting text as 0 and TRUE as 1.
- STDEVPAReturns the population standard deviation, counting text as 0 and TRUE as 1.
- STEYXReturns the standard error of the predicted y values of a straight line fit.
- T.DISTReturns the left tailed Student's t distribution.
- T.DIST.2TReturns the two tailed Student's t distribution.
- T.DIST.RTReturns the right tailed Student's t distribution.
- T.INVReturns the left tailed inverse of the Student's t distribution.
- T.INV.2TReturns the two tailed inverse of the Student's t distribution.
- T.TESTReturns the probability from a Student's t test that two samples come from populations with the same mean.
- TRENDReturns values along the least squares straight line that fits the known data.
- TRIMMEANReturns the mean of the values after leaving out a percentage of the smallest and largest ones.
- VAR.PReturns the variance of a whole population.
- VAR.SEstimates the variance from a sample.
- VARAEstimates the variance from a sample, counting text as 0 and TRUE as 1.
- VARPAReturns the population variance, counting text as 0 and TRUE as 1.
- WEIBULL.DISTReturns the Weibull distribution.
- Z.TESTReturns the one tailed probability of a z test for a hypothesised population mean.
Logical 13 functions
IF and its relatives, AND, OR, NOT, SWITCH, LET and LAMBDA helpers for decisions inside formulas.
- ANDTRUE when every argument is TRUE.
- FALSEThe logical value FALSE.
- IFReturns one value when a condition is TRUE and another when it is FALSE.
- IFERRORReturns a value of your choice when a formula gives an error, otherwise the formula result.
- IFNAReturns a value of your choice when a formula gives #N/A, otherwise the formula result.
- IFSChecks conditions in order and returns the value of the first one that is TRUE.
- LAMBDACreates a reusable function with parameters. Call it right away, store it in a name or pass it to MAP, REDUCE and friends.
- LETNames the results of calculations so a formula can reuse them.
- NOTReverses a logical value.
- ORTRUE when any argument is TRUE.
- SWITCHCompares a value with a list of cases and returns the result of the first match, or a default.
- TRUEThe logical value TRUE.
- XORTRUE when an odd number of arguments are TRUE.
Text 56 functions
Join, split, search, replace, clean and format text, including regular expressions.
- ARRAYTOTEXTReturns the values of an array or range as one text.
- ASCChanges full width (double byte) letters, digits and katakana to half width (single byte) characters.
- BAHTTEXTConverts a number to Thai text and adds the suffix Baht.
- CHARReturns the character for a code number from 1 to 255.
- CLEANRemoves nonprinting characters (codes 0 to 31) from text.
- CODEReturns the code number of the first character of a text.
- CONCATJoins texts and the values of whole ranges into one text.
- CONCATENATEJoins several texts into one.
- DBCSChanges half width (single byte) letters, digits and katakana to full width (double byte) characters.
- DOLLARFormats a number as currency text, rounded to the given number of decimals.
- EXACTChecks whether two texts are exactly the same, including upper and lower case.
- FINDReturns the position of one text inside another, matching upper and lower case exactly.
- FINDBSame as FIND, counting bytes; without a double byte language set as default each character counts as one byte.
- FIXEDRounds a number to a number of decimals and returns it as text, with or without thousands separators.
- JISChanges half width (single byte) characters to full width (double byte) characters.
- JOINJoins values and ranges with a delimiter between them.
- LEFTReturns the first characters of a text.
- LEFTBSame as LEFT, counting bytes; without a double byte language set as default each character counts as one byte.
- LENReturns the number of characters in a text.
- LENBSame as LEN, counting bytes; without a double byte language set as default each character counts as one byte.
- LOWERConverts text to lowercase.
- MIDReturns characters from the middle of a text, given a start position and a length.
- MIDBSame as MID, counting bytes; without a double byte language set as default each character counts as one byte.
- NUMBERVALUEConverts text to a number using the decimal and group separators you give, whatever the locale.
- PHONETICReturns the phonetic (furigana) text of a cell; here that is the text of the cell itself.
- PROPERCapitalizes the first letter of each word and makes the other letters lowercase.
- REGEXEXTRACTReturns the parts of a text that match a regular expression pattern.
- REGEXMATCHChecks whether any part of a text matches a regular expression pattern (case sensitive).
- REGEXREPLACEReplaces the parts of a text that match a regular expression pattern.
- REGEXTESTChecks whether any part of a text matches a regular expression pattern.
- REPLACEReplaces part of a text, given a start position and a number of characters, with other text.
- REPLACEBSame as REPLACE, counting bytes; without a double byte language set as default each character counts as one byte.
- REPTRepeats text a given number of times.
- RIGHTReturns the last characters of a text.
- RIGHTBSame as RIGHT, counting bytes; without a double byte language set as default each character counts as one byte.
- SEARCHReturns the position of one text inside another, ignoring case. Accepts the wildcards ? and *.
- SEARCHBSame as SEARCH, counting bytes; without a double byte language set as default each character counts as one byte.
- SPLITSplits text at a delimiter into a row of cells.
- SUBSTITUTEReplaces existing text with new text, everywhere or only in one occurrence.
- TReturns the value if it is text, otherwise empty text.
- TEXTFormats a number as text with a number format code, like "$#,##0.00" or "yyyy-mm-dd".
- TEXTAFTERReturns the text that comes after a delimiter.
- TEXTBEFOREReturns the text that comes before a delimiter.
- TEXTJOINJoins texts and ranges with a delimiter between them, optionally skipping empty values.
- TEXTSPLITSplits text into columns and rows at the delimiters you give.
- TO_DATEReturns a number as a date value (the number itself; text and other values are returned unchanged).
- TO_DOLLARSReturns a number as a currency value (the number itself; other values are returned unchanged).
- TO_PERCENTReturns a number as a percentage value (the number itself; other values are returned unchanged).
- TO_PURE_NUMBERReturns a value as a plain number without date, percent or currency meaning; numeric text is converted.
- TO_TEXTConverts a value to text.
- TRIMRemoves spaces from the start and end of text and turns runs of spaces inside it into single spaces.
- UNICHARReturns the Unicode character for a code number.
- UNICODEReturns the Unicode code point of the first character of a text.
- UPPERConverts text to uppercase.
- VALUEConverts text that looks like a number, date, time, percentage or currency into a number.
- VALUETOTEXTReturns any value as text; the strict format puts quotes around text.
Date and time 25 functions
Build dates and times, add months or workdays, and measure the time between two dates.
- DATEBuilds a date from a year, a month and a day; months and days past the end roll over.
- DATEDIFCounts the whole days, months or years between two dates.
- DATEVALUEConverts a date written as text into a date serial number.
- DAYReturns the day of the month (1 to 31) of a date.
- DAYSReturns the number of days between two dates.
- DAYS360Returns the days between two dates on a calendar of twelve 30 day months.
- EDATEReturns the date a number of months before or after a date, on the same day of the month where possible.
- EOMONTHReturns the last day of the month a number of months before or after a date.
- HOURReturns the hour (0 to 23) of a time.
- ISOWEEKNUMReturns the ISO week number of the year (weeks start on Monday, week 1 contains the first Thursday).
- MINUTEReturns the minute (0 to 59) of a time.
- MONTHReturns the month (1 to 12) of a date.
- NETWORKDAYSCounts the working days (Monday to Friday) between two dates, both included, leaving out holidays.
- NETWORKDAYS.INTLCounts the working days between two dates, both included, with a choice of weekend days and holidays.
- NOWReturns the current date and time.
- SECONDReturns the second (0 to 59) of a time.
- TIMEBuilds a time of day from hours, minutes and seconds.
- TIMEVALUEConverts a time written as text into a fraction of a day.
- TODAYReturns the current date.
- WEEKDAYReturns the day of the week of a date as a number (by default 1 for Sunday to 7 for Saturday).
- WEEKNUMReturns the week number of a date in its year (the week containing January 1 is week 1).
- WORKDAYReturns the date a number of working days (Monday to Friday) before or after a date, skipping holidays.
- WORKDAY.INTLReturns the date a number of working days before or after a date, with a choice of weekend days and holidays.
- YEARReturns the year of a date.
- YEARFRACReturns the fraction of a year between two dates using a day count basis.
Lookup and reference 19 functions
Find values in tables and lists with XLOOKUP, INDEX and MATCH, and work with references.
- ADDRESSBuilds a cell address as text from a row and column number.
- AREASReturns the number of areas (separate ranges) in a reference.
- CHOOSEReturns the value in a list at the given position.
- COLUMNReturns the column number of a reference, or of the cell with the formula when left out.
- COLUMNSReturns the number of columns in a range or array.
- FORMULATEXTReturns the formula in a cell as text.
- GETPIVOTDATAReturns data from a pivot table. Refer to the pivot table cells directly instead.
- HLOOKUPLooks for a value in the first row of a table and returns the value in the same column from another row.
- INDEXReturns the value or reference at a given row and column of a range or array.
- INDIRECTReturns the reference written in a text value, such as "B3" or "Sheet2!A1:A10".
- LOOKUPFinds a value in a sorted row or column and returns the value at the same position in another row or column.
- MATCHReturns the position of a value in a row or column.
- OFFSETReturns a reference shifted a number of rows and columns from a starting reference, optionally with a new size.
- ROWReturns the row number of a reference, or of the cell with the formula when left out.
- ROWSReturns the number of rows in a range or array.
- TRANSPOSESwaps the rows and columns of a range or array.
- VLOOKUPLooks for a value in the first column of a table and returns the value in the same row from another column.
- XLOOKUPSearches a range or array for a match and returns the matching item from a second range or array.
- XMATCHReturns the position of an item in a row or column, with exact, approximate, wildcard and binary search options.
Financial 55 functions
Loans, interest, depreciation, investment returns, bonds and securities.
- ACCRINTReturns the interest accrued on a security that pays periodic interest.
- ACCRINTMReturns the interest accrued on a security that pays interest at maturity.
- AMORDEGRCReturns the depreciation of an asset for an accounting period with the French declining balance system.
- AMORLINCReturns the straight line depreciation of an asset for an accounting period, prorated in the first period.
- COUPDAYBSReturns the days from the start of the coupon period to the settlement date.
- COUPDAYSReturns the number of days in the coupon period that contains the settlement date.
- COUPDAYSNCReturns the days from the settlement date to the next coupon date.
- COUPNCDReturns the next coupon date after the settlement date.
- COUPNUMReturns the number of coupons payable between the settlement date and maturity.
- COUPPCDReturns the coupon date on or before the settlement date.
- CUMIPMTReturns the total interest paid on a loan between two periods.
- CUMPRINCReturns the total principal paid on a loan between two periods.
- DBReturns the depreciation of an asset for a period with the fixed declining balance method.
- DDBReturns the depreciation of an asset for a period with the double declining balance method or another factor.
- DISCReturns the discount rate of a security.
- DOLLARDEConverts a price written as a whole number and a fraction (like 1.02 meaning 1 and 2/16) to a decimal number.
- DOLLARFRConverts a decimal price to a whole number and a fraction written after the decimal point.
- DURATIONReturns the Macaulay duration in years of a security that pays periodic interest, for 100 face value.
- EFFECTReturns the effective annual interest rate from a nominal rate and the compounding periods per year.
- FVReturns the future value of an investment with constant payments and a constant rate.
- FVSCHEDULEReturns the future value of a principal after applying a series of interest rates.
- INTRATEReturns the interest rate of a fully invested security.
- IPMTReturns the interest part of one payment of a loan or investment.
- IRRReturns the internal rate of return of cash flows at regular periods.
- ISPMTReturns the interest paid in one period of a loan with equal principal repayments.
- MDURATIONReturns the modified duration in years of a security that pays periodic interest, for 100 face value.
- MIRRReturns the modified internal rate of return, with separate rates for financing and reinvestment.
- NOMINALReturns the nominal annual interest rate from an effective rate and the compounding periods per year.
- NPERReturns the number of payment periods needed for a loan or investment.
- NPVReturns the net present value of future cash flows (at the end of each period) at a discount rate.
- ODDFPRICEReturns the price per 100 face value of a security with an odd (short or long) first period.
- ODDFYIELDReturns the yield of a security with an odd (short or long) first period.
- ODDLPRICEReturns the price per 100 face value of a security with an odd (short or long) last period.
- ODDLYIELDReturns the yield of a security with an odd (short or long) last period.
- PDURATIONReturns the number of periods an investment needs to grow to a target value at a constant rate.
- PMTReturns the payment per period of a loan or investment with a constant rate and constant payments.
- PPMTReturns the principal part of one payment of a loan or investment.
- PRICEReturns the price per 100 face value of a security that pays periodic interest.
- PRICEDISCReturns the price per 100 face value of a discounted security.
- PRICEMATReturns the price per 100 face value of a security that pays interest at maturity.
- PVReturns the present value of a series of equal future payments.
- RATEReturns the interest rate per period of a loan or investment, found by iteration.
- RECEIVEDReturns the amount received at maturity for a fully invested security.
- RRIReturns the equal interest rate per period that grows a present value into a future value.
- SLNReturns the straight line depreciation of an asset for one period.
- SYDReturns the sum of years' digits depreciation of an asset for a period.
- TBILLEQReturns the bond equivalent yield of a Treasury bill.
- TBILLPRICEReturns the price per 100 face value of a Treasury bill.
- TBILLYIELDReturns the yield of a Treasury bill.
- VDBReturns the declining balance depreciation of an asset between two periods, switching to straight line when that is larger.
- XIRRReturns the internal rate of return of cash flows that happen on given dates.
- XNPVReturns the net present value of cash flows that happen on given dates.
- YIELDReturns the yield of a security that pays periodic interest.
- YIELDDISCReturns the annual yield of a discounted security.
- YIELDMATReturns the annual yield of a security that pays interest at maturity.
Information 24 functions
Test what a cell contains, its type, and whether a value is an error.
- CELLReturns information about a cell: its address, row, column, contents, type, number format, width and more.
- ERROR.TYPEReturns a number for the kind of error a value is (1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!...), or #N/A when it is not an error.
- INFOReturns information about the working environment, such as "recalc", "release", "system", "numfile", "origin", "directory" or "osversion".
- ISBLANKChecks whether a cell is empty.
- ISEMAILChecks whether a value is a valid email address.
- ISERRChecks whether a value is any error except #N/A.
- ISERRORChecks whether a value is any error.
- ISEVENChecks whether a number is even (the fraction is dropped first).
- ISFORMULAChecks whether a cell contains a formula.
- ISLOGICALChecks whether a value is TRUE or FALSE.
- ISNAChecks whether a value is the #N/A error.
- ISNONTEXTChecks whether a value is anything other than text (blank cells count as not text).
- ISNUMBERChecks whether a value is a number.
- ISODDChecks whether a number is odd (the fraction is dropped first).
- ISOMITTEDTRUE when a LAMBDA argument was left out.
- ISREFChecks whether a value is a reference to cells.
- ISTEXTChecks whether a value is text.
- ISURLChecks whether a value looks like a web address.
- NConverts a value to a number: numbers stay, TRUE is 1, anything else is 0.
- NAReturns the #N/A error, meaning no value is available.
- SHEETReturns the number of a sheet (its position in the workbook), for the current sheet, a reference or a sheet name.
- SHEETSReturns the number of sheets in a reference, or in the workbook when left out.
- SPARKLINEDraws a small chart (line, column, win/loss or bar) inside the cell from a row or column of numbers.
- TYPEReturns a number for the type of a value: 1 number, 2 text, 4 logical, 16 error, 64 array, 128 lambda.
Engineering 54 functions
Number base conversions, complex numbers, bitwise operations, Bessel functions and unit conversion.
- BESSELIReturns the modified Bessel function In(x).
- BESSELJReturns the Bessel function Jn(x).
- BESSELKReturns the modified Bessel function Kn(x).
- BESSELYReturns the Bessel function Yn(x), also called the Weber or Neumann function.
- BIN2DECConverts a binary number to decimal.
- BIN2HEXConverts a binary number to hexadecimal.
- BIN2OCTConverts a binary number to octal.
- BITANDReturns the bitwise AND of two numbers.
- BITLSHIFTReturns a number shifted left by a number of bits (multiplied by a power of 2).
- BITORReturns the bitwise OR of two numbers.
- BITRSHIFTReturns a number shifted right by a number of bits (divided by a power of 2, rounded down).
- BITXORReturns the bitwise exclusive OR of two numbers.
- COMPLEXBuilds a complex number as text from its real and imaginary parts.
- CONVERTConverts a number from one measurement unit to another, like feet to meters or Fahrenheit to Celsius.
- DEC2BINConverts a decimal number to binary.
- DEC2HEXConverts a decimal number to hexadecimal.
- DEC2OCTConverts a decimal number to octal.
- DELTAReturns 1 when two numbers are equal and 0 otherwise.
- ERFReturns the error function integrated between zero (or a lower limit) and an upper limit.
- ERF.PRECISEReturns the error function integrated between zero and a limit.
- ERFCReturns the complementary error function integrated between a value and infinity.
- ERFC.PRECISEReturns the complementary error function integrated between a value and infinity.
- GESTEPReturns 1 when a number is greater than or equal to a step value and 0 otherwise.
- HEX2BINConverts a hexadecimal number to binary.
- HEX2DECConverts a hexadecimal number to decimal.
- HEX2OCTConverts a hexadecimal number to octal.
- IMABSReturns the absolute value (modulus) of a complex number.
- IMAGINARYReturns the imaginary part of a complex number.
- IMARGUMENTReturns the angle (argument) of a complex number, in radians.
- IMCONJUGATEReturns the complex conjugate of a complex number.
- IMCOSReturns the cosine of a complex number.
- IMCOSHReturns the hyperbolic cosine of a complex number.
- IMCOTReturns the cotangent of a complex number.
- IMCSCReturns the cosecant of a complex number.
- IMCSCHReturns the hyperbolic cosecant of a complex number.
- IMDIVReturns the quotient of two complex numbers.
- IMEXPReturns e raised to the power of a complex number.
- IMLNReturns the natural logarithm of a complex number.
- IMLOG10Returns the base 10 logarithm of a complex number.
- IMLOG2Returns the base 2 logarithm of a complex number.
- IMPOWERReturns a complex number raised to a power.
- IMPRODUCTReturns the product of complex numbers.
- IMREALReturns the real part of a complex number.
- IMSECReturns the secant of a complex number.
- IMSECHReturns the hyperbolic secant of a complex number.
- IMSINReturns the sine of a complex number.
- IMSINHReturns the hyperbolic sine of a complex number.
- IMSQRTReturns the square root of a complex number.
- IMSUBReturns the difference of two complex numbers.
- IMSUMReturns the sum of complex numbers.
- IMTANReturns the tangent of a complex number.
- OCT2BINConverts a octal number to binary.
- OCT2DECConverts a octal number to decimal.
- OCT2HEXConverts a octal number to hexadecimal.
Database 12 functions
Sum, count and average records in a list that match a criteria range.
- DAVERAGEAverages the numbers in a field of the records that match the criteria.
- DCOUNTCounts the cells with numbers in a field of the records that match the criteria.
- DCOUNTACounts the non empty cells in a field of the records that match the criteria.
- DGETReturns the single value of a field from the one record that matches the criteria.
- DMAXReturns the largest number in a field of the records that match the criteria.
- DMINReturns the smallest number in a field of the records that match the criteria.
- DPRODUCTMultiplies the numbers in a field of the records that match the criteria.
- DSTDEVEstimates the standard deviation of a sample from a field of the records that match the criteria.
- DSTDEVPCalculates the standard deviation of a whole population from a field of the records that match the criteria.
- DSUMAdds the numbers in a field of the records that match the criteria.
- DVAREstimates the variance of a sample from a field of the records that match the criteria.
- DVARPCalculates the variance of a whole population from a field of the records that match the criteria.
Web 12 functions
Links, images, URL encoding and data from the web.
- ENCODEURLEncodes text so it can be used safely inside a web address.
- FILTERXMLReads values from XML using an XPath. It is turned off for safety, so this returns #BLOCKED!.
- GOOGLEFINANCEGets current or past stock market data. Live web data is not available yet, so this returns #BLOCKED!.
- HYPERLINKCreates a link that opens a web page or a place in the workbook when clicked.
- IMAGEShows a picture from a web address inside the cell.
- IMPORTDATAImports a CSV or TSV file from a web address. Live web data is not available yet, so this returns #BLOCKED!.
- IMPORTFEEDImports an RSS or Atom feed. Live web data is not available yet, so this returns #BLOCKED!.
- IMPORTHTMLImports a table or list from a web page. Live web data is not available yet, so this returns #BLOCKED!.
- IMPORTRANGEImports a range from another spreadsheet. Live web data is not available yet, so this returns #BLOCKED!.
- IMPORTXMLImports data from a web page using an XPath query. Live web data is not available yet, so this returns #BLOCKED!.
- STOCKHISTORYGets the price history of a stock. Live web data is not available yet, so this returns #BLOCKED!.
- WEBSERVICEReads data from a web service. Web access is turned off for safety, so this returns #BLOCKED!.
Array 31 functions
Dynamic arrays: filter, sort, deduplicate, reshape and combine ranges with results that spill.
- ARRAY_CONSTRAINReturns the top left part of an array with at most the given number of rows and columns.
- ARRAYFORMULAReturns the result of a formula that works on whole ranges, so it can spill.
- BYCOLApplies a LAMBDA to each column of an array and returns one result per column.
- BYROWApplies a LAMBDA to each row of an array and returns one result per row.
- CHOOSECOLSReturns the given columns of an array (negative numbers count from the end).
- CHOOSEROWSReturns the given rows of an array (negative numbers count from the end).
- DROPRemoves a number of rows or columns from the start (positive) or end (negative) of an array.
- EXPANDExpands an array to the given number of rows and columns, filling new cells with a pad value.
- FILTERReturns the rows (or columns) of a range or array that meet a condition.
- FLATTENPuts the values of one or more ranges into a single column, reading row by row.
- GROUPBYGroups rows by the values of one or more columns and aggregates the values of each group.
- HSTACKPlaces arrays side by side; shorter columns are padded with #N/A.
- MAKEARRAYBuilds an array of the given size by calling a LAMBDA with each row and column number.
- MAPApplies a LAMBDA to each value of one or more arrays and returns the results.
- PERCENTOFReturns the sum of a subset of values as a fraction of the sum of all values.
- PIVOTBYSummarizes data by row and column fields and aggregates the values at each intersection.
- QUERYRuns a query (select, where, group by, order by, limit, offset, label) over a range with optional header rows.
- REDUCEReduces an array to one value by applying a LAMBDA to an accumulator and each value.
- SCANApplies a LAMBDA to each value with a running accumulator and returns every intermediate result.
- SEQUENCEReturns a list of sequential numbers in an array of the given size.
- SORTSorts the rows or columns of a range or array.
- SORTBYSorts a range or array by the values of other ranges or arrays.
- SORTNReturns the first n rows of a range after sorting, with options for ties.
- TAKEReturns a number of rows or columns from the start (positive) or end (negative) of an array.
- TOCOLReturns the values of an array as a single column.
- TOROWReturns the values of an array as a single row.
- TRIMRANGERemoves empty rows and columns from the edges of a range.
- UNIQUEReturns the unique rows (or columns) of a range or array.
- VSTACKStacks arrays on top of each other; shorter rows are padded with #N/A.
- WRAPCOLSWraps a row or column of values into columns of the given length.
- WRAPROWSWraps a row or column of values into rows of the given length.
Compatibility 38 functions
Older function names kept so workbooks made with earlier spreadsheet versions keep working.
- BETADISTReturns the cumulative beta distribution (older form of BETA.DIST).
- BETAINVReturns the inverse of the beta distribution (older name of BETA.INV).
- BINOMDISTReturns the binomial distribution (older name of BINOM.DIST).
- CHIDISTReturns the right tailed chi squared probability (older name of CHISQ.DIST.RT).
- CHIINVReturns the inverse of the right tailed chi squared distribution (older name of CHISQ.INV.RT).
- CHITESTReturns the chi squared test of independence (older name of CHISQ.TEST).
- CONFIDENCEReturns the margin of error of a population mean (older name of CONFIDENCE.NORM).
- COVARReturns the population covariance (older name of COVARIANCE.P).
- CRITBINOMReturns the smallest number of successes reaching a cumulative probability (older name of BINOM.INV).
- EXPONDISTReturns the exponential distribution (older name of EXPON.DIST).
- FDISTReturns the right tailed F distribution (older name of F.DIST.RT).
- FINVReturns the inverse of the right tailed F distribution (older name of F.INV.RT).
- FTESTReturns the result of an F test (older name of F.TEST).
- GAMMADISTReturns the gamma distribution (older name of GAMMA.DIST).
- GAMMAINVReturns the inverse of the gamma distribution (older name of GAMMA.INV).
- HYPGEOMDISTReturns the hypergeometric probability (older form of HYPGEOM.DIST).
- LOGINVReturns the inverse of the lognormal distribution (older name of LOGNORM.INV).
- LOGNORMDISTReturns the cumulative lognormal distribution (older form of LOGNORM.DIST).
- MODEReturns the value that occurs most often (older name of MODE.SNGL).
- NEGBINOMDISTReturns the negative binomial probability (older form of NEGBINOM.DIST).
- NORMDISTReturns the normal distribution (older name of NORM.DIST).
- NORMINVReturns the inverse of the normal distribution (older name of NORM.INV).
- NORMSDISTReturns the cumulative standard normal distribution (older form of NORM.S.DIST).
- NORMSINVReturns the inverse of the standard normal distribution (older name of NORM.S.INV).
- PERCENTILEReturns the k-th percentile of the values (older name of PERCENTILE.INC).
- PERCENTRANKReturns the percentage rank of a value in a data set (older name of PERCENTRANK.INC).
- POISSONReturns the Poisson distribution (older name of POISSON.DIST).
- QUARTILEReturns a quartile of the values (older name of QUARTILE.INC).
- RANKReturns the rank of a number in a list (older name of RANK.EQ).
- STDEVEstimates the standard deviation from a sample (older name of STDEV.S).
- STDEVPReturns the population standard deviation (older name of STDEV.P).
- TDISTReturns the one or two tailed Student's t distribution (older form of T.DIST.RT and T.DIST.2T).
- TINVReturns the two tailed inverse of the Student's t distribution (older name of T.INV.2T).
- TTESTReturns the probability of a Student's t test (older name of T.TEST).
- VAREstimates the variance from a sample (older name of VAR.S).
- VARPReturns the population variance (older name of VAR.P).
- WEIBULLReturns the Weibull distribution (older name of WEIBULL.DIST).
- ZTESTReturns the one tailed probability of a z test (older name of Z.TEST).
No function matches your search.
Try any formula in a blank sheet
No account needed. Type = and start writing, with autocomplete and hints for every argument.