In the following sections, a separate syntax line is included for each database or list function that doesn't conform to the following general syntax:
=Dfunction(database,field,criteria)
· The database argument refers to the range encompassing the list or database.
· The field argument refers to a particular column in the list. If you omit the field argument, the function operates on the entire list. You can refer to the column number within the list, the name of the column heading (in quotes), or a cell reference referring to either the label or the column number.
· The criteria argument specifies the basis on which you want the function to select particular cells. The criteria includes a duplicate list of column headings from the list, with words, phrases, numeric values, or formulas listed underneath those headings.
DAVERAGE
Indicates the average of the values that meet the specified criteria.
DCOUNT
Counts the number of cells that contain 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. If duplicates are found, DGET returns #NUM.
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 multiplying the values that meet the specified criteria.
DSTDEV
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 variance of an entire population, based on the values that meet the specified criteria.
GETPIVOTDATA
Returns a value of data stored in a PivotTable. The syntax is as follows:
=GETPIVOTDATA(PivotTable,name)
Date and Time Functions
DATE
DATE
Returns the DATEVALUE serial number.
=DATE(year,month,day)
DATEVALUE
Converts date text to a DATEVALUE serial number.
=DATEVALUE(date_text)
DAY
Returns the corresponding day of the month serial number, from 1 to 31.
=DAY(serial_number)
DAYS360
Returns the number of days between dates that you specify, based on a 360-day year (twelve 30 day months).
=DAYS360(start_date,end_date)
EDATE
Returns the value or serial number of the date specified by you and the number of months before or after the specified date. Use EDATE to calculate the maturity date or date due that falls on the same day of the month as the date of issue.
=EDATE(start_date,months)
EOMONTH
Returns the serial number for the last day of the month (as specified by months) before or after start_date. Used to calculate maturity dates or dates due that fall on the last day of the month.
=EOMonth(start_date,months)
HOUR
Returns the hour as a serial number integer between 0 (12:00 A.M.) and 23 (11:00 P.M.).
=HOUR(serial_number)
MINUTE
Returns the serial number integer from 0 to 59 that corresponds to the minute.
=MINUTE(serial_number)
MONTH
Returns the corresponding serial number of the month between 1 and 12.
=MONTH(serial_number)
NETWORKDAYS
Returns the number of working days between two dates. Excludes weekends and specified holidays.
=NETWORKDAYS(start_date,end_date,holidays)
NOW
Returns the current date and time in the form of a serial number. When entered in a cell, Excel formats the number as a date and time. There are no arguments for this function.
=NOW()
SECOND
Returns the corresponding serial number of seconds as an integer between 0 and 59.
=SECOND(serial_number)
TIME
Returns the corresponding serial number of time as a decimal between 0 and 0.99999999.
=TIME(hour,minute,second)
TIMEVALUE
Returns the serial number represented by text as time.
=TIMEVALUE(time_text)
TODAY
Returns the current date as a serial number. When entered in a cell, Excel formats the number as a date. There are no arguments for this function.
=TODAY()
WEEKDAY
Returns the corresponding day of the week (from 1 to 7) as a serial number.
=WEEKDAY(serial_number,return_type)
WORKDAY
Returns a number representing the date that is the number of days (specified by days) before or after start_date. You can exclude dates with the holidays argument.
=WORKDAY(start_date,days,holidays).
YEAR
Returns the corresponding year as a serial number in the form of an integer from 19009999.
=YEAR(serial_number)
YEARFRAC
Returns the calculated fraction of the year represented by whole numbers between two dates.
=YEARFRAC(start_date,end_date,basis)
DDE and External Data Functions
CALL
Calls up the procedure in a dynamic link library or code resource.
Syntax 1, with register:
=CALL(register_ID,argument1,...)
Syntax 2, used alone:
=CALL(module_text,procedure,type_text,argument1,...)
REGISTER.ID
Supplies the registered ID of the dynamic link library or code resource.
=REGISTER.ID(module_text,procedure,type_text)
SQL.REQUEST
Runs a query from a worksheet and connects an external data source.
=SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_names_logical)
Engineering Functions
NOTE
You must have the Analysis ToolPak installed and enabled to use (see) these functions.
BESSELI
Returns the Bessel function in modified form for imaginary arguments.
=BESSELI(x,n)
BESSELJ
Returns the actual Bessel function, where x is the value at which to evaluate the function, and n is the order of the Bessel function.
=BESSELJ(x,n)
BESSELK
Returns the Bessel function in modified form for imaginary arguments.
=BESSELK(x,n)
BESSELY
Returns the Bessel function, also known as the Weber or Neumann function, where x is the value at which to evaluate the function, and n is the order of the function.
=BESSELY(x,n)
BIN2DEC
Converts a binary number to decimal form.
=BIN2DEC(number)
BIN2HEX
Converts a binary number to hexadecimal.
=BIN2HEX(number,places)
BIN2OCT
Converts a binary number to octal.
=BIN2OCT(number,places)
COMPLEX
Converts real and imaginary coefficients into a complex number of the form x+yi or x+yj.
=COMPLEX(real_num,I_num,suffix)
CONVERT
Converts from one measurement system to another.
=CONVERT(number,from_unit,to_unit)
DEC2BIN
Converts decimal numbers to binary.
=DEC2BIN(number,places)
DEC2HEX
Converts decimal numbers to hexadecimal.
=DEC2HEX(number,places)
DEC2OCT
Converts decimal numbers to octal.
=DEC2OCT(number,places)
DELTA
Tests whether numbers or values are equal.
=DELTA(number1,number2)
ERF
Returns the integrated error function between lower_limit and upper_limit.
=ERF(lower_limit,upper_limit)
ERFC
Returns a complementary ERF function integrated between x and infinity, where x is the lower bound for integrating ERF.
=ERF(x)
GESTEP
Returns 1 if number is greater than or equal to a specified step or threshold, otherwise returns 0.
=GESTEP(number,step)
HEX2BIN
Converts hexadecimal numbers to binary.
=HEX2BIN(number,places)
HEX2DEC
Converts hexadecimal numbers to decimal.
=HEX2DEC(number)
HEX2OCT
Converts hexadecimal numbers to octal.
=HEX2OCT(number)
IMABS
Returns the absolute value (modulus) of a complex number in x+yi or x+yj text format. The formula takes the square root of (x2+y2).
=IMABS(inumber)
IMAGINARY
Returns the coefficient of a complex number in x+yi or x+yj in text format.
=IMAGINARY(inumber)
IMARGUMENT
Returns the theta argument and angle expressed in radians.
=IMARGUMENT(inumber)
IMCONJUGATE
Returns the complex conjugate of a complex number in x+yi or x+yj text format.
=IMCONJUGATE(inumber)
IMCOS
Returns the cosine of a complex number x+yi or x+yj in text format.
=IMCOS(numbers)
IMDIV
Returns the quotient of complex numbers x+yi or x+yj in text format.
=IMDIV(number1,number2)
IMEXP
Returns the exponential of complex numbers x+yi or x+yj in text format.
=IMEXP(inumber)
IMLN
Returns the natural logarithm of complex numbers x+yi or x+yj in text format.
=IMLN(inumber)
IMLOG10
Returns the common logarithm (base 10) of complex numbers x+yi or x+yj in text format.
=IMLOG10(inumber)
IMLOG2
Returns the base 2 logarithm of complex numbers in x+yi or x+yj in text format.
=IMLOG2(inumber)
IMPOWER
Returns a complex number raised to a power in x+yi or x+yj text format.
=IMPOWER(inumber,number)
IMPRODUCT
Returns the product from 2 to 29 in complex numbers x+yi or x+yj text format.
=IMPRODUCT(inumber1,inumber2,...)
IMREAL
Returns real coefficients of complex numbers x+yi or x+yj in text format.
=IMREAL(inumber)
IMSIN
Returns the sine of complex numbers x+yi or x+yj in text format.
=IMSIN(inumber)
IMSQRT
Returns the square root of complex numbers x+yi or x+yj in text format.
=IMSQRT(inumber)
IMSUB
Returns the difference of two complex numbers x+yi or x+yj in text format.
=IMSUB(inumber1,inumber2)
IMSUM
Returns the sum of two complex numbers x+yi or x+yj in text format.
=IMSUM(inumber1,inumber2,...)
OCT2BIN
Converts an octal number to binary.
=OCT2BIN(number,places)
OCT2DEC
Converts an octal number to decimal.
=OCT2DEC(number)
OCT2HEX
Converts an octal number to hexadecimal.
=OCT2HEX(number,places)
SQRTPI
Returns the square root of a positive number multiplied by Pi. This value cannot be less than zero. (Also found under "Math and Trigonometry Functions.")
=SQRTPI(number*pi)
Financial Functions
ACCRINT
Returns accrued interest for security that pays periodic interest.
=ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)
ACCRINTM
Returns accrued interest for security that pays interest at maturity.
=ACCRINTM(issue,maturity,rate,par,basis)
AMORDEGRC
Returns appreciation for each accounting period.
=AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)
AMORLINC
Returns depreciation for each accounting period.
=AMORLINC(cost,date_purchased,first_period,salvage,period,rate,basis)
COUPDAYBS
Returns the number of days from start date of the coupon period to the settlement.
=COUPDAYBS(settlement,maturity,frequency,basis)
COUPDAYS
Returns the number of days in the coupon period that includes the settlement date.
=COUPDAYS(settlement,maturity,frequency,basis)
COUPDAYSNC
Returns the number of days from the settlement date to the next coupon date.
=COUPDAYSNC(settlement,maturity,frequency,basis)
COUPNCD
Returns the number of the next coupon date after the settlement date.
=COUPNCD(settlement,maturity,frequency,basis)
COUPNUM
Returns the total number of coupons payable between the settlement and maturity date, rounded up to the nearest whole coupon.
=COUPNUM(settlement,maturity,frequency,basis)
COUPPCD
Returns the number of the previous coupon date before the settlement date.
=COUPPCD(settlement,maturity,frequency,basis)
CUMIPMT
Returns the cumulative interest on a loan between start and stop dates.
=CUMIPMT(rate,nper,pv,start_period,end_period,type)
CUMPRINC
Returns the cumulative principal amount between start and stop dates.
=CUMPRINC(rate,nper,pv,start_period,end_period,type)
DB
Returns the asset depreciation for a period using the fixed declining balance method.
=DB(cost,salvage,life,period,month)
DDB
Returns the asset depreciation for a specified period using the double-declining balance method, or another method you specify.
=DDB(cost,salvage,life,period,factor)
DISC
Returns the security discount rate.
=DISC(settlement,maturity,pr,redemption,basis)
DOLLARDE
Converts a fraction dollar price into a decimal dollar price.
=DOLLARDE(fractional_dollar,fraction)
DOLLARFR
Converts a decimal dollar price into a fraction dollar price.
=DOLLARFR(decimal_dollar,fraction)
DURATION
Returns the duration for an assumed par value of $100 using the Macauley method.
=DURATION(settlement,maturity,coupon,yield,frequency,basis)
EFFECT
Returns the effective interest rate annually, give the nominal annual interest rate and the number of compounding periods per year.
EFFECT(nominal_rate,npery)
FV
Returns the future value of periodic payments and a constant interest rate.
=FV(rate,nper,pmt,pv,type)
FVSCHEDULE
Returns the future value of the initial principal after applying several compound interest rates.
=FVSCHEDULE(principal,schedule)
INTRATE
Returns the interest rate of a fully invested security.
=INTRATE(settlement,maturity,investment,redemption,basis)
IPMT
Returns the interest payment for a period of time based on an investment with periodic constant payments and a constant interest rate.
=IPMT(rate,per,nper,pv,fv,type)
IRR
Returns the internal rate of return for a series of cash flows represented by numbers in the form of values.
=IRR(values,guess)
MDURATION
Returns a modified duration of a security with an assumed par value of $100.
=MDURATION(settlement,maturity,coupon,yield,frequency,basis)
MIRR
Returns a modified internal rate of return for several periodic cash flows.
=MIRR(values,finance_rate,reinvest_rate)
NOMINAL
Returns the nominal annual interest rate given an effective rate and a number of compounding periods per year.
=NOMINAL(effective_rate,npery)
NPER
Returns the number of periods for an investment based on periodic constant payments and a constant interest rate.
=NPER(rate,pmt,pv,fv,type)
NPV
Calculates the net present value of an investment with the discount rate and several future payments and income.
=NPV(rate,value1,value2,...)
ODDFPRICE
Returns the value of a security based on a per $100 face value and an odd first period.
=ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yield,redemption,frequency,basis)
ODDFYIELD
Returns the security yield with an odd first period.
=ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,basis)
ODDLPRICE
Returns the per $100 face value of a security having an odd last coupon period.
=ODDLPRICE(settlement,maturity,last_interest,rate,yield,redemption,frequency,basis)
ODDLYIELD
Returns the security yield that has an odd last period.
=ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption,frequency,basis)
PMT
Calculates the loan payment for a loan based on constant payments and constant interest rates.
=PMT(rate,nper,pv,fv,type)
PPMT
Returns the principal payment for a specific period of an investment based on periodic constant payments and a constant interest rate.
=PPMT(rate,per,nper,pv,fv,type)
PRICE
Returns the value of a security based on price per $100 face value and periodic payments.
=PRICE(settlement,maturity,rate,yield,redemption,frequency,basis)
PRICEDISC
Returns the value of a discounted security based on a price per $100 face value.
=PRICEDISC(settlement,maturity,discount,redemption,basis)
PRICEMAT
Returns the value of a security that pays interest at maturity and price per $100 face value.
=PRICEMAT(settlement,maturity,issue,rate,yield,basis)
PV
Based on an investment, returns the present value.
=PV(rate,nper,pmt,fv,type)
RATE
Returns per period the interest of an annuity.
=RATE(nper,pmt,pv,fv,type,guess)
RECEIVED
Based on a fully invested security, returns the amount received at maturity.
=RECEIVED(settlement,maturity,investment,discount,basis)
SLN
Based on one period, returns the straight-line depreciation on an asset.
=SLN(cost,salvage,life)
SYD
Based on a specified period, returns the sum-of-years-digits depreciation of an asset.
=SYD(cost,salvage,life,per)
TBILLEQ
For a treasury bill, returns the bond equivalent yield.
=TBILLEQ(settlement,maturity,discount)
TBILLPRICE
For a treasury bill, returns the price per $100 face value.
=TBILLPRICE(settlement,maturity,discount)
TBILLYIELD
For a treasury bill, returns the yield.
=TBILLYIELD(settlement,maturity,pr)
VDB
For a period you specify, returns the depreciation of an asset.
=VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
XIRR
For a schedule of cash flows that are not necessarily periodic, returns the internal rate of return.
=XIRR(values,dates,guess)
XNPV
For a schedule of cash flows that are not necessarily periodic, returns the present value.
=XNPV(rate,values,dates)
YIELD
Based on a yield that pays periodic interest, returns the yield of the security.
=YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
YIELDDISC
For a discounted security, returns the annual yield.
=YIELDDISC(settlement,maturity,pr,redemption,basis)
YIELDMAT
Based on a security that pays interest at maturity, returns the annual yield.
=YIELDMAT(settlement,maturity,issue,rate,pr,basis)
Information Functions
CELL
Returns information about a cell's location, formatting, or contents in the upper-left cell in a reference.
=CELL(info_type,reference)
COUNTBLANK
Counts the number of empty cells in a specified range.
=COUNTBLANK(range)
ERROR.TYPE
Returns the corresponding number value associated with an error type in Microsoft Excel. Returns #N/A if no error exists.
=ERROR.TYPE(error_val)
INFO
Returns operating environment information.
=INFO(type_text)
ISBLANK
Returns TRUE if value refers to an empty cell.
=ISBLANK(value)
ISERR
Returns TRUE if value refers to any error value in Microsoft Excel except #NA.
=ISERR(value)
ISERROR
Returns TRUE if value refers to any error value in Microsoft Excel.
=ISERROR(value)
ISEVEN
Returns TRUE or FALSE if the number is even or odd, TRUE being even and FALSE being odd.
=ISEVEN(number)
ISLOGICAL
Returns TRUE if the value is logical.
=ISLOGICAL(value)
ISNA
Returns TRUE if value is associated with the error type #NA.
=ISNA(value)
ISNONTEXT
Returns TRUE if value refers to any item that isn't text. Returns TRUE if value refers to a blank cell.
=ISNONTEXT(value)
ISNUMBER
Returns TRUE if value refers to a number.
=ISNUMBER(value)
ISODD
Returns TRUE if number is odd and FALSE if the number is even.
=ISODD(number)
ISREF
Returns TRUE if value refers to a reference.
=ISREF(value)
ISTEXT
Returns TRUE if value refers to text.
=ISTEXT(value)
N
Returns value converted to a number.
=N(value)
NA
Returns the error value associated with #NA.
=NA()
TYPE
Returns the type of value, for example, number = 1, text = 2, logical value = 4, error value = 16, and array = 64. Use TYPE when the behavior of another function depends on the type of value in a particular cell.
=TYPE(value)
Logical Functions
AND
Returns TRUE if all arguments in the formula are true, and FALSE if any one argument is false.
=AND(logical1,logical2,...)
FALSE
Returns the value FALSE. There are no arguments associated with this function.
=FALSE()
IF
Returns a value if one condition is true, and returns another value if the condition is false.
=IF(logical_test,value_if_true,value_if_false)
NOT
Returns the reverse value of its arguments.
=NOT(logical)
OR
Returns TRUE if any one argument is true. Returns FALSE if all arguments are false.
=OR(logical1,logical2,...)
TRUE
Returns the value TRUE. There are no arguments associated with this function.
=TRUE()
ADDRESS
Given specified row and column numbers, creates a cell address as text.
=ADDRESS(row_num,column_num,abs_num,A1,sheet_text)
AREAS
Returns the number of areas in reference.
=AREAS(reference)
CHOOSE
Returns the index number from a list of arguments.
=CHOOSE(index_num,value1,value2,...)
COLUMN
Based on a given reference, returns the column number.
=COLUMN(reference)
COLUMNS
Based on an array or reference, returns the number of columns.
=COLUMNS(array)
HLOOKUP
Searches for a specified value in an array or tables, based on the value found in the first row.
=HLOOKUP(lookup_value,table_array,row_index_number,range_lookup)
HYPERLINK
Creates a shortcut or jump that switches to another location within the workbook, or opens a document stored on a local drive, a network server, an intranet, or the Internet.
=HYPERLINK(link_location,cell_contents)
INDEX (Array Form)
Based on a table or array, returns the value of an element selected by the row number and column letter indexes.
=INDEX(array,row_num,column_num)
INDEX (Reference Form)
Based on the intersection of a particular row and column, returns the reference of the cell.
=INDEX(reference,row_num,column_num,area_num)
INDIRECT
Returns the reference based on a text string.
=INDIRECT(ref_text,A1)
LOOKUP (Vector Form)
Based on a range of one row or one column, returns the value from the same position in a second row or column.
=LOOKUP(lookup_value,lookup_vector,result_vector)
LOOKUP (Array Form)
Looks in the first row or column of an array, and returns the specified value from the same position in the last row or column of the array.
=LOOKUP(lookup_value,array)
MATCH
Returns the position of an item in an array that matches a specified value and order.
=MATCH(lookup_value,lookup_array,match_type)
OFFSET
Returns a reference to a range that is a specific number of rows and columns from a cell or range of cells.
=OFFSET(reference,rows,columns,height,width)
ROW
Based on a reference, returns the row number.
=ROW(reference)
ROWS
Based on a reference or array, returns the number of rows.
=ROWS(array)
TRANSPOSE
Returns a horizontal range of cells as vertical or vice versa.
=TRANSPOSE(array)
VLOOKUP
Looks for a value in the left most column of a table and returns a value from the column number you specify.
=VLOOKUP(lookup_value,table_array,column_index_num,range_lookup)
ABS
Returns the absolute value of number.
=ABS(number)
ACOS
Returns the arcosine of number. The arccosine is the angle whose cosine is number.
=ACOS(number)
ACOSH
Returns the inverse hyperbolic cosine of number.
=ACOSH(number)
ASIN
Returns the arcsine of number.
=ASIN(number)
ASINH
Returns the inverse hyperbolic sine of number.
=ASINH(number)
ATAN
Returns the arctangent of number.
=ATAN(number)
ATAN2
Returns the arctangent of the specified x and y coordinates.
=ATAN2(x_num,y_num)
ATANH
Returns the inverse hyperbolic tangent of a number.
=ATANH(number)
CEILING
Returns number rounded up to the nearest integer, or to the nearest multiple of significance (for example, to the nearest nickel).
=CEILING(number,significance)
COMBIN
Returns the number of combinations for a given number of items.
=COMBIN(number,number_chosen)
COS
Returns the cosine of a given angle.
=COS(number)
COSH
Returns the hyperbolic cosine of number.
=COSH(number)
COUNTIF
Counts the number of cells in a specified range that meet the criteria you specify.
=COUNTIF(range,criteria)
DEGREES
Converts radians into degrees.
=DEGREES(angle)
EVEN
Returns number rounded up to the nearest integer.
=EVEN(number)
EXP
Returns e raised to the power of number.
=EXP(number)
FACT
Returns the factorial of number.
=FACT(number)
FACTDOUBLE
Returns the double factorial of number.
=FACTDOUBLE(number)
FLOOR
Rounds number down, toward zero, to the nearest multiple of significance.
=FLOOR(number,significance)
GCD
Returns the greatest common divisor of two or more integers.
=GCD(number1,number2,...)
INT
Rounds number down to the nearest integer.
=INT(number)
LCM
Returns the least common multiple of integers.
=LCM(number1,number2,...)
LN
Returns the natural logarithm of a number.
=LN(number)
LOG
Returns the logarithm of a number to the base you specify.
=LOG(number,base)
LOG10
Returns the base 10 logarithm of a number.
=LOG10(number)
MDETERM
Returns the matrix determinant of an array.
=MDETERM(array)
MINVERSE
Returns the inverse matrix for the matrix stored in an array.
=MINVERSE(array)
MMULT
Returns the matrix product of two arrays.
=MMULT(array1,array2)
MOD
Returns the remainder after number is divided by divisor.
=MOD(number,divisor)
MROUND
Returns a number rounded to the desired multiple.
=MROUND(number,multiple)
MULTINOMIAL
Returns the ratio of the factorial of a sum of values to the product of factorials.
=MULTINOMIAL(number1,number2,...)
ODD
Returns number rounded to the nearest odd integer.
=ODD(number)
PI
Returns the number 3.14159265358979, the mathematical constant Pi, accurate to 15 digits. There are no arguments associated with this function.
=PI()
POWER
Returns the result of a number raised to a power.
=POWER(number,power)
PRODUCT
Multiplies all the numbers given as arguments and returns the product.
=PRODUCT(number1,number2,...)
QUOTIENT
Returns the integer portion of a division. Use this function to discard the remainder of a division.
=QUOTIENT(numerator,denominator)
RADIANS
Converts degrees to radians.
=RADIANS(angle)
RAND
Returns an evenly distributed number greater than or equal to 0 and less than 1. There are no arguments associated with this function.
=RAND()
RANDBETWEEN
Returns a random number between the numbers you specify.
=RANDBETWEEN(bottom,top)
ROMAN
Converts an Arabic numeral to Roman, as text.
=ROMAN(number,form)
ROUND
Rounds a number to a specified number of digits.
=ROUND(number,num_digits)
ROUNDDOWN
Rounds a number down toward 0.
=ROUNDDOWN(number,num_digits)
ROUNDUP
Rounds a number up away from 0.
=ROUNDUP(number,num_digits)
SERIESSUM
Returns the sum of a power series based on the formula.
=SERIESSUM(x,n,m,coefficients)
SIGN
Determines the sign (positive or negative) of number.
=SIGN(number)
SIN
Returns the sine of the given angle.
=SIN(number)
SINH
Returns the hyperbolic sine of number.
=SINH(number)
SQRT
Returns the positive square root of number.
=SQRT(number)
SQRTPI
Returns the square root of (number*pi).
=SQRTPI(number)
SUBTOTAL
Returns a subtotal from a list or database.
=SUBTOTAL(function_num,ref1,ref2,...)
SUM
Adds the numbers in a range of cells.
=Sum(number1,number2,...)
SUMIF
Adds the cells specified by criteria.
=SUMIF(range,criteria,sum_range)
SUMPRODUCT
Multiplies corresponding components in the given array, and returns the sum of those products.
=SUMPRODUCT(array1,array2,array3,...)
SUMSQ
Returns the sum of the squares of the arguments.
=SUMSQ(number1,number2,...)
SUMX2MY2
Returns the sum of the difference of squares of corresponding values in two arrays.
=SUMX2MY2(array_x,array_y)
SUMX2PY2
Returns the sum of the sum of squares in corresponding values in two arrays.
=SUMX2PY2(array_x,array_y)
SUMXMY2
Returns the sum of squares of differences of corresponding values in two arrays.
=SUMXMY2(array_x,array_y)
TAN
Returns the tangent of the given angle.
=TAN(number)
TANH
Returns the hyperbolic tangent of number.
=TANH(number)
TRUNC
Truncates number to an integer, removing the fractional part of the number. Note that this function does not round number.
=TRUNC(number,num_digits)
AVEDEV
Returns the average of the absolute deviations of data points from their mean.
=AVEDEV(number1,number2,...)
AVERAGE
Returns the average of the arguments.
=AVERAGE(number1,number2,...)
AVERAGEA
Calculates the average of the values in the list of arguments.
=AVERAGEA(value1,value2,...)
BETADIST
Returns the cumulative beta probability density function.
=BETADIST(x,alpha,beta,A,B)
BETAINV
Returns the inverse of the cumulative beta probability density function.
=BETAINV(probability,alpha,beta,A,B)
BINOMDIST
Returns the individual term binomial distribution probability.
=BINOMDIST(number_s,trials,probability_s,cumulative)
CHIDIST
Returns the one-tailed probability of the chi-squared distribution.
=CHIDIST(x,degrees_freedom)
CHINV
Returns the inverse of the one-tailed probability of the chi-squared distribution.
=CHINV(probability,degrees_freedom)
CHITEST
Returns the test for independence.
=CHITEST(actual_range,expected_range)
CONFIDENCE
Returns the confidence interval - the range on either side of a sample mean - for the population mean.
=CONFIDENCE(alpha,standard_dev,size)
CORREL
Returns the correlation coefficient of array1 and array2 cell ranges.
=CORREL(array1,array2)
COUNT
Counts the number of cells that contain numbers within the list of arguments.
=COUNT(value1,value2,...)
COUNTA
Counts the number of cells that are not empty within the list of arguments.
=COUNTA(value1,value2,...)
COVAR
Returns covariance, the average of the products of deviations for each data point pair.
=COVAR(array1,array2)
CRITBINOM
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
=CRITBINOM(trials,probability_s,alpha)
DEVSQ
Returns the sum of squares of deviations of data points from their sample mean.
=DEVSQ(number1,number2,...)
EXPONDIST
Returns the exponential distribution.
=EXPONDIST(x,lambda,cumulative)
FDIST
Returns the F probability distribution.
=FDIST(x,degrees_freedom1,degrees_freedom2)
FINV
Returns the inverse of the F probability distribution.
=FINV(probability,degrees_freedom1,degrees_freedom2)
FISHER
Returns the Fisher transformation at x.
=FISHER(x)
FISHERINV
Returns the inverse of the Fisher transformation.
=FISHERINV(y)
FORECAST
Calculates or predicts a future value by using existing values.
=FORECAST(x,known_y's,known_x's)
FREQUENCY
Calculates how often values occur within a range of values, and then returns a vertical array of numbers. This function returns an array of values and therefore must be entered as an array formula (using Ctrl+Shift+Enter).
=FREQUENCY(data_array,bins_array)
FTEST
Returns the result of an Ftest.
=FTEST(array1,array2)
GAMMADIST
Returns the gamma distribution.
=GAMMADIST(x,alpha,beta,cumulative)
GAMMAINV
Returns the inverse of the gamma cumulative distribution.
=GAMMAINV(probability,alpha,beta)
GAMMALN
Returns the natural logarithm of the gamma function.
=GAMMALN(x)
GEOMEAN
Returns the geometric mean of an array or range of positive data.
=GEOMEAN(number1,number2,...)
GROWTH
Calculates predicted exponential growth by using existing data.
=GROWTH(known_y's,known_x's,new_x's,const)
HARMEAN
Returns the harmonic mean of a data set.
=HARMEAN(number1,number2,...)
HYPGEOMDIST
Returns the hypergeometric distribution.
=HYPERGEOMDIST(sample_s,number_sample,population_s,number_population)
INTERCEPT
Calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.
=INTERCEPT(known_y's,known_x's)
KURT
Returns the Kurtosis of a data set.
=KURT(number1,number2,...)
LARGE
Returns the k-th largest value in a data set.
=LARGE(array,k)
LINEST
Calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and returns an array that describes the line.
=LINEST(known_y's,known_x's,const,stats)
LOGEST
In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve.
=LOGEST(known_y's,known_x's,const,stats)
LOGINV
Returns the inverse of the lognormal cumulative distribution function of x, wherein (x) is normally distributed with parameters mean and standard_dev.
=LOGINV(probability,mean,standard_dev)
LOGNORMDIST
Returns the cumulative lognormal distribution of x, wherein (x) is normally distributed with parameters mean and standard_dev.
=LOGNORMDIST(x,mean,standard_dev)
MAX
Returns the largest value in a set of values.
=MAX(number1,number2,...)
MAXA
Returns the largest value in a list of arguments.
=MAXA(value1,value2,...)
MEDIAN
Returns the median of a given set numbers.
=MEDIAN(number1,number2,...)
MIN
Returns the smallest number in a set of values. Nonnumeric values are ignored.
=MIN(number1,number2,...)
MINA
Returns the smallest value in a list of arguments including text and logical values such as "No Grade", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=MINA(value1,value2,...)
MODE
Returns the most frequently occurring, or repetitive, value in an array or range of data.
=MODE(number1,number2,...)
NEGBINOMDIST
Returns the negative binomial distribution.
=NEGBINOMDIST(number_f,number_s,probability_s)
NORMDIST
Returns the normal cumulative distribution for the specified mean and standard deviation.
=NORMDIST(x,mean,standard_dev,cumulative)
NORMINV
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.
=NORMINV(probability,mean,standard_dev)
NORMSDIST
Returns the standard normal cumulative distribution function.
=NORMSDIST(z)
NORMSINV
Returns the inverse of the standard normal cumulative distribution.
=NORMSINV(probability)
PEARSON
Returns the Pearson product moment correlation coefficient, r, a dimensionless index ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.
=PEARSON(array1,array2)
PERCENTILE
Returns the k-th percentile of values in a range.
=PERCENTILE(array,k)
PERCENTRANK
Returns the rank of a value in a data set as a percentage of the data set.
=PERCENTRANK(array,x,significance)
PERMUT
Returns the number of permutations for a given number of objects that can be selected from number objects.
=PERMUT(number,number_chosen)
POISSON
Returns the Poisson distribution.
=POISSON(x,mean,cumulative)
PROB
Returns the probability that values in a range are between two specified limits.
=PROB(x_range,prob_range,lower_limit,upper_limit)
QUARTILE
Returns the quartile of a data set.
=QUARTILE(array,quart)
RANK
Returns the rank of a number in a list of numbers.
=RANK(number,ref,order)
RSQ
Returns the square of the Pearson product moment correlation coefficient through data points in known_y's and known_x's.
=RSQ(known_y's,known_x's)
SKEW
Returns the skewness of a distribution.
=SKEW(number1,number2,...)
SLOPE
Returns the slope of the regression line through data points in known_y's and known_x's.
=SLOPE(known_y's,known_x's)
SMALL
Returns the k-th smallest value in a data set.
=SMALL(array,k)
STANDARDIZE
Returns a normalized value from a distribution characterized by mean and standard_dev.
=STANDARDIZE(x,mean,standard_dev)
STDEV
Estimates standard deviation based on a sample. Nonnumeric values are ignored.
=STDEV(number1,number2,...)
STDEVA
Estimates standard deviation based on a sample including text and logical values such as "None", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=STDEVA(value1,value2,...)
STDEVP
Calculates standard deviation based on the entire population given as arguments. Nonnumeric values are ignored.
=STDEVP(number1,number2,...)
STDEVPA
Calculates standard deviation based on the entire population given as arguments including text and logical values such as "None", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=STDEVPA(value1,value2,...)
STEYX
Returns the standard error of the predicted y value for each x in the regression.
=STEYX(known_y's,known_x's)
TDIST
Returns the student's t-distribution.
TDIST(x,degrees_freedom,tails)
TINV
Returns the inverse of the student's t-distribution for the specified degrees of freedom.
=TINV(probability,degrees_freedom)
TREND
Returns values along a linear trend.
=TREND(known_y's,known_x's,new_x's,const)
TRIMMEAN
Returns the mean of the interior of a data set.
=TRIMMEAN(array,percent)
TTEST
Returns the probability associated with the student's t-test.
=TTEST(array1,array2,tails,type)
VAR
Estimates variance based on a sample, with from 1 to 30 arguments.
=VAR(number1,number2,...)
VARA
Estimates variance based on a sample including text and logical values such as "None", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=VARA(value1,value2,...)
VARP
Calculates variance based on the entire population. Nonnumeric values are ignored.
=VARP(number1,number2,...)
VARPA
Calculates variance based on the entire population. including text and logical values such as "None", TRUE, and FALSE. TRUE evaluates to 1. All other nonnumeric values evaluate to 0.
=VARPA(value1,value2,...)
WEIBULL
Returns the Weibull distribution.
=WEIBULL(x,alpha,beta,cumulative)
ZTEST
Returns the two-tailed P-value of a z-test.
=ZTEST(array,x,sigma)
CHAR
Returns the character specified by a number.
=CHAR(number)
CLEAN
Removes all nonprintable characters from text.
=CLEAN(text)
CODE
Returns a numeric code from the first character in a text string.
=CODE(text)
CONCATENATE
Joins several text strings into one text string.
=CONCATENATE(text1,text2,...)
DOLLAR
Converts a number to text using Currency format, with the decimals rounded to the specified place. The format used is $#,##0.00_);($#,##0.00).
=DOLLAR(number,decimals)
EXACT
Compares two text strings and returns TRUE if they're exactly the same, and FALSE otherwise. EXACT is case sensitive.
=EXACT(text1,text2)
FIND
Finds one text string with another text string, and returns the number of the starting position of find_text, from the leftmost character of within_text. FIND is case sensitive and cannot include wildcard characters.
=FIND(find_text,within_text,start_num)
FINDB
Finds one text string with another text string, and returns the number of the starting position of find_text, from the leftmost character of within_text. FINDB is case sensitive and cannot include wildcard characters. This function is for use with double-byte characters.
=FINDB(find_text,within_text,start_num)
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.
=FIXED(number,decimals,no_commas)
LEFT
Returns the first character or characters in a text string.
=LEFT(text,num_char)
LEN
Returns the number of characters in a text string.
=LEN(text)
LOWER
Converts all uppercase letters in a text string to lowercase.
=LOWER(text)
MID
Returns a specific number of characters from a text string, starting at the position you specify.
=MID(text,start_num,num_char)
PROPER
Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter.
=PROPER(text)
REPLACE
Replaces part of a text string with a different text string based on the number of characters you specify.
=REPLACE(old_text,start_num,num_chars,new_text)
REPLACEB
Replaces part of a text string with a different text string, based on the number of bytes you specify.
=REPLACEB(old_text,start_num,num_bytes,new_text)
REPT
Repeats text a given number of times.
=REPT(text,number_times)
RIGHT
Returns the last character or characters in a text string.
=RIGHT(text,num_chars)
SEARCH
Returns the number of the character at which a specific character or text string is first found, reading from left to right. SEARCH is not case sensitive and can include wildcard characters.
=SEARCH(find_text,within_text,start_num)
SEARCHB
Returns the number of the double-byte character at which a specific character or text string is first found, reading from left to right. SEARCHB is not case sensitive and can include wildcard characters.
=SEARCHB(find_text,within_text,start_num)
SUBSTITUTE
Substitutes new_text for old_text in a text string.
=SUBSTITUTE(text,old_text,new_text,instance_num)
T
Returns the text referred to by value.
=T(value)
TEXT
Converts a value to text in a specific number format.
=TEXT(value,format_text)
TRIM
Removes all spaces from text except for single spaces between words.
=TRIM(text)
UPPER
Converts text to uppercase.
=UPPER(text)
VALUE
Converts a text string that represents a number to a number.
=VALUE(text)
** I Shall try in future Important functions are discus in my Home page with Example .
