Meeting Excel's Built-in Functions

In this section, I'll introduce you to Excel's ten main categories of built-in functions: Database, Date and Time, Engineering, Financial, Logical, Information, Lookup and Reference, Math and Trigonometry, Statistical, and Text. We'll look at the functions that are most widely useful, with examples where they'll be helpful.

Database Functions

Excel includes 12 database functions you use to identify values in an Excel table that match your specified criteria. (A table is a flat-form database you create on a worksheet; Chapter 10 explains how to create tables and gives you examples of using the database functions.) Table 6–1 explains the database functions.

Table 6–1. Excel's Database Functions

Database
Function		 What It Returns
DAVERAGE The average of the values in a column that match the criteria you specify.
DCOUNT The count of cells in the database field that match the criteria you specify.
DCOUNTA The count of nonblank cells in the database field that match the criteria you specify.
DGET The database record that matches the criteria you specify for a particular field.
DMAX The largest number in the database field that matches the criteria you specify.
DMIN The smallest number in the database field that matches the criteria you specify.
DPRODUCT The result of multiplying the values of the records that match the criteria you specify.
DSTDEV The standard deviation of the selected database entries from a sample.
DSTDEVP The standard deviation based on the entire population of the selected entries.
DSUM The value that results from adding the values in the fields that match the criteria you specify.
DVAR The variance based on a sample of database entries.
DVARP The variance based on the entire population of database entries.

Date and Time Functions

Table 6–2 explains Excel's date and time functions, which you'll find useful in many worksheets.

Table 6–2. Excel's Date and Time Functions

Date and Time Function What It Returns
DATE The serial number of the specified date. For example, =DATE(2011,11,18) returns 40865, the serial date for 18 November 2011. Excel displays this value as a date unless you set the cell format to General.
TIME The serial number of the specified time. For example, =TIME(11,59,59) returns 0.499988426, the serial number for 11:59.59 AM. Excel displays this value as time unless you set the cell format to General.
DATEVALUE The serial number of the specified date that's formatted as text. For example, =DATEVALUE("12 December 2011") returns 40889, the serial date for 12 December 2011.
TIMEVALUE The serial number of the specified time that's formatted as text. For example, =TIMEVALUE("3:25.00 PM") returns 0.502372685, the serial number for 3:25 PM.
DAY A serial number between 1 and 31, representing the day of the month for the specified serial date. For example, =DAY(40789) returns 3, indicating that the date falls on the third day in the month.
MONTH A serial number between 1 and 12, representing the month in which the specified serial date falls. For example, =MONTH(40789) returns 9, indicating that the date is in September.
YEAR The four-digit number for the specified serial date. For example, =YEAR(40789) returns 2011, indicating that the date is in 2011.
YEARFRAC A number showing how many years are between the specified start date and the specified end date. For example, =YEARFRAC(DATE(2011,1,1),DATE (2012,3,1)) returns 1.166666667, the number of years between 1 January 2011 and 1 March 2012.
DAYS360 A number showing the number of days between the specified start date and the specified end date, assuming there are 360 days in a year (this is an accounting technique rather than a straightforward denial of conventional timekeeping). For example, =DAYS360(DATE(2011,1,1),DATE(2012,3,1)) returns 420, the number of days between 1 January 2011 and 1 March 2012 (with that 360-day year).
HOUR A serial number between 0 and 23 representing the hour for the time given. For example, =HOUR(0.25) returns 6, representing 6 AM., which is one quarter of the way through the 24 hours in a day.
MINUTE A serial number between 0 and 59 representing the minute for the time given. For example, =MINUTE(0.55) returns 12, being the minutes in 1:12 PM, which is the time 55% through a day.
SECOND A serial number between 0 and 59 representing the second for the time given. For example, =SECOND(0.61) returns 24, being the seconds in 2:38.24 PM, which is the time 61% through a day.
TODAY The current date in date format. For example, =TODAY() returns 6/12/2011 on 12 June 2011.
NOW The current date and time in date and time format. For example, =NOW() returns a date and time such as 6/2/2011 03:15.
WEEKNUM A serial number between 1 and 52, showing the week of the year the specified date occurs in. For example, =WEEKNUM(40789) returns 36, indicating that the date falls in the 36th week of the year.
WEEKDAY A serial number between 1 (Sunday) and 7 (Saturday) showing the weekday for the specified date. For example, =WEEKDAY(40789) returns 7, indicating that the day is a Saturday.
WORKDAY A serial number giving the date that is the specified number of workdays before or after the specified start date, including the number of holidays specified. For example, =WORKDAY(DATE(2011,6,12),40,2) returns 40760, the serial number of 5 August 2011, the workday 40 days after 12 June 2011 if there are two holidays.
NETWORKDAYS A number giving the net number of work days (“net workdays”) between the specified starting date and the specified ending date, minus the number of holidays specified. For example, =NETWORKDAYS(DATE(2011,6,12),DATE (2011,10,20),2) returns 94, the net number of workdays between 12 June 2011 and 20 October 2011, assuming that two holidays fall in that period.
EDATE The serial number of the date that is the specified number of months before or after the given start date. For example, =EDATE(40789,4) returns 40911, the serial date four months after the serial date 40789.
EOMONTH The serial number of the last day of the month that is the specified number of months before or after the given start date. For example, =EOMONTH(DATE(2011,7,14),6) returns 40939, the serial number for 31 January 2012, the last day of the month that is six months after 14 July 2011.

Engineering Functions

Table 6–3 explains Excel's engineering functions, which range from calculating Bessel functions to converting numbers to and from binary, octal, and hexadecimal.

Table 6–3. Excel's Engineering Functions

Engineering Function What It Returns
BESSELI The modified Bessel function In(x), the contour integral. For example, =BESSELI(1.8,1) returns 1.31716723.
BESSELJ The Bessel function Jn(x). This is also called cylindrical harmonics. For example, =BESSELJ(1.5,2) returns 0.232087679.
BESSELK The modified Bessel function Kn(x). This is also called the Basset function or Macdonald function. For example, =BESSELK(1.8,3) returns 0.95783635.
BESSELY The Bessel function Yn(x), also called the Neumann function or the Weber function. For example, =BESSELY(3.5,1) returns 0.410188417.
BIN2DEC The decimal (base 10) equivalent of the specified binary (base 2) number. For example, =BIN2DEC(1001101)returns 77. The binary number can be up to 10 characters long; longer numbers cause #NUM! errors.
BIN2HEX The hexadecimal (base 16) equivalent of the specified binary (base 2) number, optionally specifying the number of characters to use. For example, =BIN2HEX(111011111) returns 1DF, while =BIN2HEX(111011111,4) returns 01DF. The binary number can be up to 10 characters long; longer numbers cause #NUM! errors.
BIN2OCT The octal (base 8) equivalent of the specified binary (base 2) number, optionally specifying the number of characters to use. For example, =BIN2OCT(1011,4) returns 0013.
COMPLEX The complex number produced by real and imaginary coefficients. For example, =COMPLEX(4,5) returns 4+5i.
CONVERT The equivalent value in the specified destination measuring system of the value in the specified source measuring system. You can convert Weight and Mass, Distance, Pressure, Force, Energy, Power, Magnetism, Temperature, Liquid, and Metric measurements; see the Help file for details. For example, =CONVERT(200,"kg","lbm") converts 200Kg to its equivalent weight in pounds (440.9245244).
DEC2BIN The binary (base 2) equivalent of the specified decimal (base 10) number, optionally specifying the number of characters to use. For example, =DEC2BIN(100) returns 1100100, while =DEC2BIN(100,10) returns 0001100100.
DEC2HEX The hexadecimal (base 16) equivalent of the specified decimal (base 10) number, optionally specifying the number of characters to use. For example, =DEC2HEX(-90) returns FFFFFFFFA6.
DEC2OCT The octal (base 8) equivalent of the specified decimal (base 10) number, optionally specifying the number of characters to use. For example, =DEC2OCT(100,6) returns 000144.
DELTA 1 if the two numbers are equal, 0 if they are not. For example, =DELTA(B1,B2) returns 1 if the values in cells B1 and B2 are equal; otherwise, it returns 0.
ERF The error function integrated between the specified lower limit and specified upper limit. If you omit the upper limit, the error function between zero and the specified lower limit. For example, =ERF(0.5,2) returns 0.474822387, the error function integrated between 0.5 and 2.
ERFC The complementary error function integrated between the specified lower bound and infinity. For example, =ERFC(2) returns 0.004677735.
GESTEP 1 if the specified number is greater than or equal to the specified threshold, 0 if it is not. For example, =GESTEP(5,D1) returns 1 if 5 is greater than or equal to the value in cell D1; otherwise, it returns 0.
HEX2BIN The binary (base 2) equivalent of the specified hexadecimal (base 16) number, optionally specifying the number of characters to use. For example, =HEX2DEC("AF",8) returns 10101111.
HEX2DEC The decimal (base 10) equivalent of the specified hexadecimal (base 16) number. For example, =HEX2DEC("FFFF0000") returns 4294901760.
HEX2OCT The octal (base 8) equivalent of the specified hexadecimal (base 16) number, optionally specifying the number of characters to use. For example, =DEX2OCT("AF00") returns 127400.
IMABS The modulus (absolute value) of a complex number. For example, =IMABS("6+2i") returns 6.708203932.
IMAGINARY The imaginary coefficient of a complex number. For example, =IMAGINARY("8+3i") returns 3.
IMARGUMENT The theta argument, an angle expressed in radians, for the specified complex number. For example, =IMARGUMENT("4+5i") returns 0.896055385.
IMCONJUGATE The complex conjugate of the specified complex number. For example, =IMCONJUGATE("4+5i") returns 4-5i.
IMCOS The cosine of the specified complex number. For example, =IMCOS(A22) returns the cosine of the value in cell A22.
IMDIV The quotient of the specified two complex numbers. For example, =IMDIV("10+25i","-240+280i") calculates the quotient of the two numbers given.
IMEXP The exponential of the specified complex number. For example, =IMEXP("2+i") returns 3.99232404844127+6.21767631236797i.
IMLN The natural logarithm of the specified complex number. For example, =IMLN("8+10i") returns 2.5499332139121+0.896055384571344i.
IMLOG10 The base-10 logarithm of the specified complex number. For example, =IMLOG10("8+10i") returns 1.10742192402385+0.389151908999031i.
IMLOG2 The base-2 logarithm of the specified complex number. For example, =IMLOG2("8+10i") returns 3.67877600230904+1.29273465968293i.
IMPOWER The specified complex number raised to the specified power. For example, =IMPOWER("4+2i",3) returns 16+88i.
IMPRODUCT The product of the specified complex numbers. For example, =IMPRODUCT("3+4i","2+3i") returns -6+17i.
IMREAL The real coefficient of the specified complex number. For example, =IMREAL("3+4i") returns 3.
IMSIN The sine of the specified complex number. For example, =IMSIN("3+4i") returns 3.85373803791938-27.0168132580039i.
IMSQRT The square root of the specified complex number. For example, =IMSQRT("3+4i") returns 2+i.
IMSUB The difference between the two specified complex numbers. For example, =IMSUB("3+4i","4+5i") returns -1-i.
IMSUM The sum of the specified complex numbers. For example, =IMSUM("3+4i","4+5i") returns 7+9i.
OCT2BIN The binary (base 2) equivalent of the specified octal (base 8) number, optionally specifying the number of characters to use. For example, =OCT2BIN(45,8) returns 00100101.
OCT2DEC The decimal (base 10) equivalent of the specified octal (base 8) number. For example, =OCT2DEC(45) returns 37.
OCT2HEX The hexadecimal (base 16) equivalent of the specified octal (base 8) number. For example, =OCT2HEX(45,8) returns 00000025.

Financial Functions

Table 6–4 introduces you to Excel's financial functions, which cover most common financial calculations and various more specialized calculations.

Table 6–4. Excel's Financial Functions

Financial Function What It Returns
ACCRINT The interest accrued on a security that pays interest periodically. For example, =ACCRINT(DATE(2010,8,1),DATE(2010,9,1),DATE(2010,8,15),10%,5000,4) calculates the interest on a security with an issue date of 1 August 2010, a first interest date of 1 September 2010, a settlement date of 15 August 2010, an interest rate of 10% (I know—dream on), a par value of $5000, and a frequency of four coupon payments per year.
ACCRINTM The interest accrued on a security that pays interest on maturity rather than along the way. For example, =ACCRINTM(DATE(2011,1,15),DATE(2012,2,15),10%,10000) calculates the interest on a security with an issue date of 15 January 2011, a maturity date of 15 February 2012, a rate of 10%, and a par value of $10,000.
AMORDEGRC An asset's prorated linear depreciation for each accounting period (in the French accounting system). For example, =AMORDEGRC(5000,DATE(2010,12,3),DATE(2011,3,30),2000,2,12.5%) calculates the prorated linear depreciation of an asset bought on 3 December 2010 for $5000, putting the end of the first period on 30 March 2011, using a salvage value of $2000, two periods, and a 12.5% depreciation rate.
AMORLINC An asset's prorated linear depreciation for each accounting period (in the French accounting system). For example, =AMORLINC(4000,DATE(2010,12,3),DATE(2011,6,30),1500,1,18%) calculates the prorated linear depreciation of an asset bought on 3 December 2010 for $4000, putting the end of the first and only period on 30 June 2011, using a salvage value of $1500 and an 18% depreciation rate.
COUPDAYBS The number of days from the coupon period's beginning to the settlement date. For example, =COUPDAYBS(DATE(2011,2,19),DATE(2013,2,19),4) returns the number of days for a bond with a settlement date of 19 February 2011, a maturity date of 19 February 2013, and quarterly coupon payments.
COUPDAYS The number of days in the coupon period containing the settlement date. For example, =COUPDAYS(DATE(2011,3,1),DATE(2011,12,1),2) returns the number of days from the start of the coupon period to the settlement date for a bond with a settlement date of 1 March 2011, a maturity date of 1 December 2011, and semiannual coupons.
COUPDAYSNC The number of days to the next coupon date from the settlement date. For example, =COUPDAYSNC(DATE(2011,7,4),DATE(2012,4,8),2) calculates the number of days for a bond with a settlement date of 4 July 2011, a maturity date of 8 April 2012, and semiannual coupons.
COUPNCD The next coupon date after the settlement date. For example, =COUPNCD(DATE(2011,11,12), DATE(2012,12,13),4) returns the next coupon date after the settlement date of 12 November 2011 for a bond with a maturity date of 13 December 2012 and quarterly coupons.
COUPNUM The number of coupons payable between the settlement date and the maturity date. For example, =COUPNUM(DATE(2011,1,5),DATE(2013,5,8),2) returns the number of coupons payable between the settlement date of 5 January 2011 and the maturity date of 8 May 2013 for a bond with a semiannual coupon.
COUPPCD The previous coupon date before the settlement date. For example, =COUPPCD(DATE(2011,1,8),DATE(2014,1,8),4) returns the previous coupon date before the settlement date of 8 January 2011 for a bond with a maturity date of 8 January 2014 and quarterly coupons.
CUMIPMT The cumulative interest paid between the two periods specified. For example, =CUMIPMT(1%,60,10000,1,12,1) calculates the cumulative interest paid at a monthly rate of 1% (making 12% per year), on a loan that uses 60 periods (five years), with a value of $10,000. The function calculates the cumulative interest paid in the periods 1 to 12 (the first year), assuming the payment is at the beginning of the month (the final 1) rather than the end of the month (which would be represented by 0).
CUMPRINC The cumulative principal paid on a loan between the two periods specified. For example, =CUMPRINC(0.5%,24,1500,1,6,0) calculates the cumulative principal paid on a $1500 loan for the first 6 months of a 24-month period. The loan uses a rate of 0.5% a month, and the payments are made at the end of the month (the 0 at the end) rather than at the start of the month (which would be represented by 1).
DB The depreciation calculated using the fixed-declining balance method. For example, =DB(1000,250,6,2) calculates the second-year depreciation on an asset that costs $1000, has a salvage value of $250, and depreciates over six years.
DDB The depreciation calculated using the double-declining balance method or another method. For example, =DDB(2000,500,8,4,2) calculates the fourth-year depreciation on an asset that costs $2000 and has a salvage value of $500. The asset's value depreciates over eight years using the double-declining balance method (represented by the 2 at the end).
DISC The discount rate for a security. For example, =DISC(DATE(2011,1,11),DATE (2012,2,20),96.5,100,3) calculates the discount rate for a security with a settlement date of 11 January 2011, a maturity date of 20 January 2012, a price of $96.50 per $100, a redemption rate of $100 per $100 of face value, using the Actual/365 day count basis.
DOLLARDE The decimal dollar price equivalent of the specified fractional dollar price. For example, =DOLLARDE(1.08,32) calculates the decimal dollar price of 1 dollar and 8/32, returning 1.25.
DOLLARFR The fractional dollar price equivalent of the specified decimal dollar price. For example, =DOLLARFR(1.25,32) calculates the fractional dollar price equivalent of $1.25 in thirty-seconds, returning 1.08.
DURATION The annual duration of a security that pays interest periodically. For example, =DURATION(DATE(2011,1,20),DATE(2012,6,12),7%,10%,2) calculates the annual duration of a security with a settlement date of 20 January 2011, a maturity date of 12 June 2011, an annual coupon rate of 7%, and an annual yield of 10%.
EFFECT The effective annual interest rate. For example, =EFFECT(6%,4) calculates the effective annual interest rate for a 6% interest rate that uses four compounding periods per year.
FV The future value of an investment. For example, =FV(8%/12,60,-500) calculates the future value of an investment into which you put $500 a month for 60 months and receive 8% interest over 12 months.
FVSCHEDULE The future value of an investment to which compound interest rates have been applied. For example, =FVSCHEDULE(100,{0.09,0.1,0.11,0.12}) calculates the future value of an investment of $100 that receives 9% interest the first year, 10% interest the second year, 11% interest the third year, and 12 percent interest the fourth year.
INTRATE The interest rate for a security that is fully vested. For example, =INTRATE(DATE (2011,1,20),DATE(2012,6,15),10000,11000) returns the interest rate on a $10,000 security with a settlement date of 20 January 2011, a maturity date of 15 June 2012, and a redemption value of $11,000.
IPMT The interest payments for an investment for a specified period. For example, =IPMT(4%/12,1,24,-1000) calculates the interest period for the first month of a 24-month investment that has a value of $1000 and that receives a princely 4% divided over 12 months.
IRR The internal rate of return for cash flows. For example, =IRR({-50000,10000,12000,19000,28000}) calculates the internal rate of return for a venture than costs $50,000 up front and brings in $10,000 the first year, $12,000 the second year, $19,000 the third year, and $28,000 the fourth year.
ISPMT The interest paid for an investment over a specified period. For example, =ISPMT(7%/12,6,5*12,5000) calculates the payment in the sixth month of a five-year (5*12 months) investment of $5,000 that earns 7% interest divided by 12 months.
MDURATION The Macauley modified duration for a security that has an assumed par value of $100. For example, =MDURATION(DATE(2011,2,18), DATE(2014,2,28),7%,9%,2) calculates the Macauley modified duration for a security that has a settlement date of 18 February 2011, a maturity date of 28 February 2014, a 7% annual coupon rate, a 9% annual yield, and semiannual coupon payments.
MIRR The modified internal rate of return for cash flows. For example, =MIRR({-50000,10000,12000,19000,28000},3.5%,9%) calculates the modified internal rate of return for a business than costs $50,000 to buy and brings in $10,000 the first year, $12,000 the second year, $19,000 the third year, and $28,000 the fourth year. You borrow money at 3.5% interest per year and manage to reinvest your cash flows at 9%.
NOMINAL The annual nominal interest rate. For example, =NOMINAL(4.6%,4) calculates the annual nominal interest rate for an effective interest rate of 4.6% with four compounding periods per year.
NPER The number of periods for an investment that uses constant payments and a constant rate of interest. For example, =NPER(6.5%/12,-250,5000) calculates the number of periods to pay off $5000 at $250 per month, given an interest rate of 6.5% annually (divided by 12 for the months).
NPV The net present value of an investment. For example, =NPV(8%/12,{500,750,1000}) calculates the net present value of an investment that brings in $500 in the first period, $750 in the second period, and $1000 in the third period, using an 8% discount rate divided by 12 months.
ODDFPRICE The price per $100 face value of a security that uses an odd first period. For example, =ODDFPRICE(DATE(2009,11,9),DATE(2016,4,1),DATE(2009,9,1), DATE(2010,2,1),0.08,0.065,100,2) calculates the price per $100 face value of a security that has a settlement date of 9 November 2009, a maturity date of 1 April 2016, an issue date of 1 September 2009, and a first coupon date of 1 February 2010. The security has an interest rate of 8%, an annual yield of 0.065, a redemption value of $100 per $100 face value, and 2 coupon payments per year.
ODDFYIELD The yield of a security that uses an odd first period. For example, =ODDFYIELD (DATE(2010,10,1),DATE(2021,3,1),DATE(2010,9,15),DATE(2011,3,1),6%,86,100,2,0) calculates the yield of a security that has a settlement date of 1 October 2010, a maturity date of 1 March 2021, an issue date of 15 September 2010, and a first coupon date of 1 March 2011. The security has a 6% interest rate, a price of $86, and a redemption value of $100. The coupon payments are semiannual, and the calculation uses the US 30/360 day count basis.
ODDLPRICE The price per $100 face value of a security that uses an odd last period. For example, =ODDLPRICE(DATE(2011,6,6),DATE(2012,8,4),DATE(2011,5,16), 7%,7.25%,100,4,0) calculates the price per $100 face value of a security that has a settlement date of 6 June 2011, a maturity date of 4 August 2012, and a last interest date of 16 May 2011. The security has an interest rate of 7%, an annual yield of 7.25%, and a redemption value of $100. The coupon frequency is quarterly, with payments made on a 30/360 basis.
ODDLYIELD The yield of a security that uses an odd last period. For example, =ODDLYIELD(DATE(2011,7,11),DATE(2012,11,11),DATE(2011,5,4),4.25%,97.5,100,2) calculates the yield on a security that has a settlement date of 11 July 2011, a maturity date of 11 November 2011, and a last interest date of 4 May 2011. The security draws a 4.25% interest rate, has a price of $97.50 and a redemption value of $100, and pays twice a year.
PMT The payment for a loan that uses constant payments and a fixed interest rate. For example, =PMT(8%/12,36,-5000) calculates the monthly payment required to pay off a loan of $5000 in monthly payments over a 36–month period with an interest rate of 8%.
PPMT The payment for a particular period on the principal for an investment that uses constant payments and a fixed interest rate. For example, =PPMT(7.5%/12,1,24,-2000) calculates the payment for the first month of a 24-month plan on a $2000 investment running at 7.5% interest annually.
PRICE The price per $100 face value of a security that pays interest periodically. For example, =PRICE(DATE(2011,3,4),DATE(2012,6,5),6.5%,7.4%,100,4) calculates the price per $100 face value of a security that has a settlement date of 4 March 2011 and a maturity date of 5 June 2012. The security has a 6.5% interest rate, a 7.4% yield, a $100 redemption value, and quarterly frequency.
PRICEDISC The price per $100 face value of a discounted security. For example, =PRICEDISC(DATE(2011,8,4),DATE(2012,9,17),5%,100) calculates the price per $100 face value of a security with a settlement date of 4 August 2011 and a maturity date of 17 September 2012. The security has a 5% discount rate and a $100 redemption value.
PRICEMAT The price per $100 face value of a security that pays interest when it reaches maturity. For example, =PRICEMAT(DATE(2011,8,14),DATE(2013,5,5),DATE (2011,6,15),8.5%,8%) calculates the price per $100 face value of a security with a settlement date of 14 August 2011, a maturity date of 5 May 2013, and an issue date of 15 June 2011. The security has an interest rate of 8.5% and an annual yield of 8%.
PV The present value of an investment—how much a series of future payments is worth altogether now. For example, =PV(10%/12,36,-250) calculates how much 36 monthly payments of $250 is worth at 10% interest.
RATE The interest rate per period of an investment. For example, =RATE(60,-380,14000) calculates the interest rate per month on a $14,000 investment that receives $380 per month for 60 months.
RECEIVED The value at maturity of a fully invested security. For example, =RECEIVED (DATE(2011,7,17),DATE(2013,8,4),10000,4%) calculates the value at maturity of a security worth $10,000 with a settlement date of 17 July 2011, a maturity date of 4 August 2013, and a discount rate of 4%.
SLN The straight-line depreciation for an asset over one period. For example, =SLN(3000,300,3) calculates the depreciation per period of an asset that costs $3,000 new and has a salvage value of $300 after 3 years. (That's right—it's a high-end computer.)
SYD The sum-of-years' digits depreciation for an asset over a specified period. For example, =SYD(3000,300,3,1) shows how much you lose on the previous example's $3000 computer (with a salvage value of $300 after 3 years) in the first year.
TBILLEQ A percentage showing the bond-equivalent yield of a treasury bill. For example, =TBILLEQ(DATE(2011,12,1),DATE(2012,8,1),8.2%) shows the bond-equivalent yield of a treasury bill with a settlement date of 1 December 2011, a maturity date of 1 August 2012, and an 8.2% discount rate. The maturity date must be one year or less after the settlement date.
TBILLPRICE A treasury bill's price per $100 face value. For example, =TBILLPRICE(DATE (2011,9,10),DATE(2012,7,12),7.2%) calculates the price per $100 face value of a treasury bill with a settlement date of 10 September 2011, a maturity date of 12 July 2012, and a discount rate of 7.2 percent. The maturity date must be one year or less after the settlement date.
TBILLYIELD A percentage showing a treasury bill's yield. For example, =TBILLYIELD(DATE (2011,8,20),DATE(2012,8,19),94.25) calculates the yield for a treasury bill with a settlement date of 20 August 2011, a maturity date of 19 August 2012, and a price per $100 face value of $94.25.
VDB The depreciation for an asset using the double-declining balance method or a variable declining balance. For example, =VDB(8000,2000,10,2,3,3) calculates the depreciation for an asset with an initial cost of $8000 and a salvage value of $2000 after 10 years. The function calculates the depreciation for the second to third year of the asset's life, using a depreciation factor of 3.
XIRR The internal rate of return (IRR) for a schedule of cash flows with variable dates rather than regular dates. For example, =XIRR(E43:E46,F43:F46) calculates the internal rate of return for the cash flows in the range E43:E46 and the dates in the range F43:F46.
XNPV The net present value (NPV) for cash flows that use a variable schedule (or a periodic schedule—but for periodic cash flows, NPV is usually a better choice). For example, =XNPV(10%,{1000,900},F43:F44) calculates the net present value for the cash flows $1000 and $900 and their corresponding dates in the range F43:F44, using a 10% discount rate.
YIELD The yield on a security that has periodic interest payments, such as a bond. For example, =YIELD(DATE(2011,8,10),DATE(2014,7,12),7.25%,94.75,100,4) calculates the yield on a security that has a settlement date of 10 August 2010, a maturity date of 12 July 2014, and an interest rate of 7.25%. The security's price is $94.75, its redemption value is $100, and its frequency is quarterly.
YIELDDISC A discounted security's annual yield. For example, =YIELDDISC(DATE (2011,3,15),DATE(2012,9,14),94.5,100) calculates the annual yield on a security with a settlement date of 15 March 2011, a maturity date of 14 September 2012, a price of $94.50, and a redemption value of $100.
YIELDMAT The annual yield of a security that pays interest when it matures. For example, =YIELDMAT(DATE(2011,5,10),DATE(2013,4,30),DATE(2011,3,31),8%,93.2) calculates the annual yield of a security with a settlement date of 10 May 2011, a maturity date of 30 April 2013, an issue date of 31 March 2011, an interest rate of 8 %, and a price of $93.2 per $100 face value.

NOTE: For a security or investment, the issue date is when the seller makes the instrument available. The settlement date is when the buyer purchases a coupon for the financial instrument. The maturity date is when the instrument comes to term and the coupon expires.

Logical Functions

When you need to evaluate logical conditions, use Excel's logical functions, which you'll find explained in Table 6–5. For many worksheets, after using a logical function to find out the situation, you'll want to use other functions to make Excel take suitable actions.

Table 6–5. Excel's Logical Functions

Logical Function What It Returns
AND TRUE if all the specified arguments are TRUE; otherwise FALSE. For example, =AND(C1="Minneapolis",D1=2012) returns TRUE if cell C1 contains the text “Minneapolis” and D1 contains the value 2012.
FALSE FALSE. You use this function to generate a FALSE value—for example, for testing. Simply enter =FALSE() in the cell that you need to provide a FALSE value.
IF The first of the specified values if the condition is TRUE, the second of the specified values if the condition is FALSE. For example, =IF(HOUR(NOW())<12,"AM","PM") displays “AM” if the current hour is less than 12 and “PM” otherwise.
IFERROR The specified error value if the expression causes an error; otherwise, the expression itself. For example, =IFERROR(E4,"Enter a value in cell E3.") displays the message “Enter a value in cell E3” if cell E4 contains an error.
NOT FALSE from a TRUE value, or TRUE from a FALSE value. For example, =NOT(TRUE) returns FALSE.
OR TRUE if any of the specified conditions is TRUE; FALSE if all the conditions are FALSE. For example, =OR(F6="Minneapolis",F6="Little Rock", F6="Sioux Falls") returns TRUE if one of the three city names appears in cell F6; otherwise, it returns FALSE.
TRUE TRUE. You use this function to generate a TRUE value—for example, for testing. Simply enter =TRUE() in the cell that you need to provide a TRUE value.

Information Functions

When you need to return information about the contents or formatting of a particular cell or range, use the information functions that Table 6–6 explains.

Table 6–6. Excel's Information Functions

Information Function What It Returns
CELL The details of the contents, location, or formatting of the first cell in the range you specify. You can return various pieces of information. For example, =CELL("address", Tax_Rate) returns the address of the cell with the defined name Tax_Rate.
ERROR.TYPE A number giving the error type the cell contains: 1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!, 4 for #REF!, 5 for #NAME?, 6 for #NUM!, and 7 for #N/A. For example, =IF(ERROR.TYPE(F3)=2,"Enter a number other than zero in cell F2.") displays a message in cell F3 if it contains a divide-by-zero error.
INFO Information about Excel, the operating system, or the computer. For example, =INFO("system") returns “mac” if Excel is running on a Mac or “pcdos” if Excel is running on a PC. =INFO("release") returns the version of Excel—for instance, 14.0 for Excel 2011.
ISBLANK TRUE if the cell is blank; FALSE if it has contents. For example, =ISBLANK(A24) returns TRUE if cell A24 is blank.
ISERR TRUE if the cell contains any error except #N/A; otherwise FALSE. For example, =ISERR(B3) returns TRUE if cell B3 contains any error except #N/A.
ISERROR TRUE if the cell contains any error (including #N/A); otherwise FALSE. For example, ISERROR(B3) returns TRUE if cell B3 contains any error.
ISEVEN TRUE if the cell contains an even number (ignoring any decimal places); otherwise FALSE. For example, =ISEVEN(4.7) returns TRUE.
ISLOGICAL TRUE if the cell contains a logical value; otherwise FALSE. For example, =ISLOGICAL(B10) returns TRUE if cell B10 contains the value TRUE or the value FALSE.
ISNA TRUE if the cell contains #N/A; otherwise FALSE. For example, =ISNA(C8) returns TRUE if cell C8 contains #N/A.
ISNONTEXT TRUE if the cell is blank or contains anything except text, FALSE if the cell contains text. For example, =ISNONTEXT(B1) returns FALSE if cell B1 contains text.
ISNUMBER TRUE if the cell contains a number; otherwise FALSE. For example, =ISNUMBER(B1) returns TRUE if cell B1 contains a number.
ISODD TRUE if the cell contains an odd number; otherwise FALSE. For example, =ISODD(A14) returns TRUE if cell A14 contains an odd number.
ISREF TRUE if the value is a valid reference to a cell, range, or defined name; otherwise FALSE. For example, =ISREF("Gross_Income") returns TRUE if the workbook contains a range named Gross_Income.
ISTEXT TRUE if the cell contains text; otherwise FALSE. For example, ISTEXT(A1) returns TRUE if cell A1 contains text.
N A number converted from the specified value. Specifically, a number returns the same number; a date returns the equivalent serial date; TRUE returns 1, and FALSE returns 0; an error returns its error value (explained the ERROR.TYPE entry earlier in this table); and anything else returns 0. Because Excel automatically converts values, you seldom need to use this function; Excel provides it for compatibility with other spreadsheet applications.
NA #N/A. You use this function to enter the error value deliberately in the cell. For example, enter =NA() in cell A1 causes it to show the error value #N/A.
TYPE A number representing the data type the cell contains: 1 for a number, 2 for text, 4 for a logical value, 16 for an error value, and 64 for an array. For example, =TYPE(B2) returns 2 if cell B2 contains text.

Lookup and Reference Functions

For returning information from lists and tables (flat-file databases), Excel includes the lookup and reference functions explained in Table 6–7.

Table 6–7. Excel's Lookup and Reference Functions

Lookup and Reference Function What It Returns
ADDRESS The cell reference denoted by the row number and the column number.
AREAS The number of different areas contained in a reference. An area can be either a single cell or a range of contiguous cells (not a range of noncontiguous cells).
CHOOSE The value (in a set of values) that you specify by index number.
COLUMN The column number of the reference you provide.
COLUMNS The number of columns in the reference or array.
GETPIVOTDATA Data from a PivotTable report.
HLOOKUP The value from the specified row in the data table. Horizontal lookup matches the field in the top row of the table, then returns the value from the specified row further down the same column.
HYPERLINK Contains a hyperlink.
INDEX A value from a table or a reference to such a value.
INDIRECT A reference specified by a text string in a cell. You can use INDIRECT to change the reference in the cell without changing the formula.
LOOKUP A value from either a one-column or one-range row or from an array.
MATCH The position of a matching item in an array (rather than the item's contents).
OFFSET A reference to the range offset from a specified cell or range by the number of rows and columns you specify.
ROW The row number of the specified reference.
ROWS The number of rows in the specified reference or array.
TRANSPOSE Cells transposed from a vertical range to a horizontal range, or vice versa. You must use TRANSPOSE as an array formula.
VLOOKUP The value from the specified column in the data table. Vertical lookup matches the field in the first column of the table, then returns the value from the specified column further across the same row.

Mathematical and Trigonometric Functions

Table 6–8 explains the mathematical and trigonometric functions that Excel offers, providing examples only for those that are less straightforward.

Table 6–8. Excel's Mathematical and Trigonometric Functions

Mathematical and Trigonometric Function What It Returns
ABS The absolute value of the specified number.
ACOS The arccosine of the specified number.
ACOSH The inverse hyperbolic cosine of the specified number.
AGGREGATE An aggregate in the specified list or database.
ASIN The arcsine of the specified number.
ASINH The inverse hyperbolic sine of the specified number.
ATAN The arctangent of the specified number.
ATAN2 The arctangent from the specified x and y coordinates.
CEILING The specified number rounded to the nearest integer or to the nearest significant multiple.
CEILINGPRECISE The specified number rounded up to the nearest integer or the nearest significant multiple.
COMBIN The number of combinations for the specified number of items.
COS The cosine of the specified angle.
COSH The hyperbolic cosine of the specified number.
DEGREES The number of degrees for the specified number of radians.
EVEN The next integer up from the specified positive number, or the next integer down from the specified negative number.
EXP e raised to the power of the specified number.
FACT The factorial of the specified number (for example, 1x2x3x4 if you specify 4).
FACTDOUBLE The double factorial of the specified number.
FLOOR The specified number rounded down to the next significant multiple.
FLOOR.PRECISE The specified number rounded down to the nearest integer or the nearest significant multiple.
GCD The greatest common divisor for the specified numbers. For example, =GCD(240,100) returns 20.
INT The specified number rounded down to the nearest integer.
LCM The least common multiple of the specified numbers. For example, =LCM(3,4) returns 12.
LN The natural logarithm of the specified number.
LOG The logarithm of the specified number to the base you enter. For example, =LOG(12,2) returns the logarithm of 12 with base 2.
LOG10 The base-10 logarithm of the specified number.
MDETERM The matrix determinant of the specified array.
MINVERSE The inverse matrix for the specified array.
MMULT The matrix product of the two specified arrays. Each array must have the same number of rows and columns as the other.
MOD The modulus (remainder) after the specified number is divided by the specified divisor. For example, =MOD(7,2) returns 1.
MROUND The specified number rounded to the specified multiple. For example, =MROUND(7,3) returns 6.
MULTINOMIAL The multinomial of the specified set of numbers.
ODD The next odd integer above the specified positive number, or the next odd integer below the specified negative number.
PI The value of Pi to 15 digits (3.14159265358979).
POWER The specified number raised to the specified power. For example, =POWER(10,5) returns 100000.
PRODUCT The product of the specified numbers. For example, =PRODUCT(2,3,4) returns 24.
QUOTIENT The quotient, the integer portion of a division. For example, =QUOTIENT(25,3) returns 8.
RADIANS The number of radians for the specified number of degrees.
RAND A random number between 0 and 1, inclusive. The number changes each time you recalculate the worksheet. (Technically, the number is pseudo-random rather than truly random; this distinction is important for some purposes.)
RANDBETWEEN A random number between the specified bottom value and top value. For example, =RANDBETWEEN(1,10) returns a random number between 1 and 10, inclusive. The number changes each time you recalculate the worksheet. (Technically, the number is pseudo-random rather than truly random,)
ROMAN The Roman numeral equivalent of the specified Arabic numeral. For example, =ROMAN(2011) returns MMXI.
ROUND The specified number rounded to the specified number of digits. For example, =ROUND(3.33333,2) returns 3.33.
ROUNDDOWN The specified number rounded down to the specified number of digits. For example, =ROUNDDOWN(18.57321,2) returns 18.57.
ROUNDUP The specified number rounded up to the specified number of digits. For example, =ROUNDUP(18.57321,2) returns 18.58.
SERIESSUM The sum of a power series.
SIGN Returns 1 if the specified number is positive, 0 if it is zero, or -1 if it is negative.
SIN The sine of the specified angle.
SINH The hyperbolic sine of the specified number.
SQRT The square root of the specified number.
SQRTPI The square root of the specified number multiplied by Pi.
SUBTOTAL The subtotal of the specified section of a database.
SUM The sum of the specified numbers. For example, =SUM(1,2,3) returns 6.
SUMIF The sum of the cells specified by a criterion.
SUMIFS The sum of the cells specified by multiple criteria.
SUMPRODUCT The sum of the products in the specified ranges or arrays.
SUMSQ The sum of the squares of the specified numbers. For example, =SUMSQ(3,4) returns 25.
SUMX2MY2 The sum of the differences between the squares of the two specified ranges.
SUMX2PY2 The sum total of the sums of the squares of numbers in the two specified ranges.
SUMXMY2 The sum of the squares of the differences in the two specified ranges.
TAN The tangent of the specified angle.
TANH The hyperbolic tangent of the specified number.
TRUNC The specified number truncated to the specified number of digits. For example, =TRUNC(24.567,1) returns 24.5.

Statistical Functions

Excel includes many statistical functions for statistical calculations such as these:

  • Deviations. These functions include AVEDEV, STDEVA, STDEV, and STDEVP.
  • Distributions. These functions include BETADIST, CHIDIST, BINOMDIST, EXPONDIST, KURT, POISSON, and WEIBULL
  • Transformations. These functions include FISHER and FISHERINV.

Many of the statistical functions are too specialized to cover here. Table 6–9 covers the statistical functions that are used for more general business and study purposes. If you work with statistics, look through the full list in the Statistical section of the Formula Builder list to find the other functions you want.

Table 6–9. Excel's Most Widely Useful Statistical Functions

Statistical Function What It Returns
AVERAGE The average of the specified values, cells, ranges, or arrays. For example, =AVERAGE(1,2,3) returns 2, and =AVERAGE(B1:B6) returns the average of the range B1:B6.
MEDIAN The median (the number in the middle of the given set) of the numbers or the values in the specified cells. For example, =MEDIAN(1,2,2,3,4,4,6) returns 3.
MODE The mode, the value that occurs most frequently in the specified values or range of cells. For example, =MODE(1,1,2,2,2,3,3,4,18) returns 2.
COUNT The number of cells in the specified range that either contain numbers or include numbers in their list of arguments. For example, =COUNT(C1:C8) returns the number of cells in the range C1:C8 that contain numbers or include numbers.
COUNTBLANK The number of empty cells in the specified range. For example, =COUNTBLANK(C1:C8) returns the number of empty cells in the range C1:C8.
COUNTIF The number of cells in the specified range that meet the criteria you set. For example, =COUNTIF(C1:C10,">2") counts the cells in the range C1:C10 that contain numbers larger than 2.
MAX The largest value in the specified range. For example, =MAX(C1:C6) returns the largest value in the range C1:C6.
MIN The lowest value in the specified range. For example, =MIN(C1:C6) returns the lowest value in the range C1:C6.

Text Functions

When you need to manipulate text in your worksheets, use the text functions explained in Table 6–10. For example, you may need to return only a particular part of a text string, find one string in another string, or change the case of text.

Table 6–10. Excel's Text Functions

Text Function What It Returns
BAHTTEXT The number converted to Thai text and with the Baht suffix.
CHAR The character represented by the specified character code. For example, =CHAR(66) returns a B. Normally, you'd just type the letters you need, but this function can be useful when working with workbooks created using different characters sets.
CLEAN The specified text string with all nonprintable characters removed. For example, =CLEAN(A1) strips all nonprintable characters from the text string in cell A1. This function is sometimes useful when importing files created in other spreadsheet formats.
CODE The character code for the first character in the specified string. For example, =CODE("$") returns 36, the character code for the $ sign.
CONCATENATE A text string consisting of the specified text strings joined together. For example, =CONCATENATE("San Francisco ","Depot") returns the text string “San Francisco Depot.” Note that you must include any spaces needed—otherwise, Excel smashes the text together. Instead of using this function, you can simply use the concatenation operator (the & sign).
DOLLAR The specified number converted to text in the Currency format. For example, =DOLLAR(9) returns $9.00.
EXACT TRUE if the two text strings match exactly, containing the same characters in the same case; otherwise FALSE. For example, =EXACT("Oakland","OAKLAND") returns FALSE because the two strings use different case.
FIND The starting position of one specified text string within another text string. This function is case-sensitive. For example, =FIND("juve","Bovine Rejuvenation Ointment") returns 10, because the j is the tenth character (including the space).
FIXED The specified number rounded to the specified number of decimals, with or without commas. For example, =FIXED("10.8726",2) returns 10.87.
LEFT The specified number of characters from the beginning of the text string. For example, =LEFT("Product Ratings",7) returns “Product.”
LEN The number of characters in the specified text string, including spaces. For example, =LEN("Product Ratings") returns 15.
LOWER The text string converted to lowercase. For example, =LOWER("OAKLAND WAREHOUSE") returns “oakland warehouse.”
MID The specified number of characters after the starting point given in the specified text string. For example, =MID("Human Heart-Rate Monitor",7,10) returns “Heart-Rate.”
PROPER The text string converted to “proper case” (first letter capitalized, the rest lowercase). For example, =PROPER("OAKLAND WAREHOUSE") returns “Oakland Warehouse.”
REPLACE The specified text string with the given replacement string inserted in the location specified. For example, =REPLACE("Human Heart-Rate Monitor",7,10,"Pulse") returns “Human Pulse Monitor.”
REPT The specified text string repeated the specified number of times. For example, =REPT("IMPROVED! ",4) returns “IMPROVED! IMPROVED! IMPROVED! IMPROVED!”
RIGHT The specified number of characters from the end of specified text string. For example, =RIGHT("Product Ratings",7) returns “Ratings.”
SEARCH The character position at which the specified character is located in the specified string. For example, =SEARCH("P","Human Pulse Monitor") returns 7, the position of the letter P in the string “Human Pulse Monitor.”
SUBSTITUTE The specified text string with the given new text string substituted for the specified old text string. For example, =SUBSTITUTE("Make the Most of Excel 2008","Excel 2008","Excel 2011") replaces the text “Excel 2008” with the text “Excel 2011” in the text string “Make the Most of Excel 2008.”
T The text string for a text value, or empty double quotation marks (a blank string) for a nontext value. For example, =T(A19) returns the text string or a blank string for the contents of cell A19. You don't normally need to use the T function, because Excel automatically converts values to text as needed.
TEXT The text string containing the specified value converted to the format chosen. For example, ="Results for " &TEXT("4 August 2011","mmmm yyyy") returns “Results for August 2011” because of the format specified in the function.
TRIM The specified text string with spaces removed from the beginning and ends, and extra spaces between words removed to leave one space between words. For example, =TRIM(" 4512 Christy Blvd. ") strips out the superfluous spaces and returns “4512 Christy Blvd.”
UPPER The text string converted to uppercase. For example, =UPPER("don't walk") returns “DON'T WALK.”
VALUE The value contained in the specified text string. For example, =VALUE("5") returns 5. Normally, you seldom need to use this function, because Excel converts values automatically as needed.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.14.144.216