Microsoft® Excel 97 Quick Reference
This section is a reference of the more commonly used Excel functions from the following function categories: Database, Date and Time, Financial, Information, Logical, Lookup and Reference, Math and Trigonometry, Statistical, and Text. Refer to the section titled "Formula & Function Management," earlier in this book, for general information on entering and using functions.
Each of the following subsections begins with a brief description of the function category, followed by information on specific functions in that category. Information supplied on these functions includes the syntax, a description of the function, what you need to supply for the function to work, what the resulting information will be, and examples (for selected functions), as well as occasional tips, notes, cautions, and troubleshooting items.
Because Microsoft does not provide paper documentation of Excel's functions, we have included the most common and useful functions in this convenient reference. For information on some of the less used functions, or additional information on the more complex aspects of these functions, choose Help, Contents and Index (in Excel). Then click the Index tab and search on "functions."
NOTE: Function arguments appear in italic throughout this section. In syntax lines, all required arguments are indicated in bold italic; optional arguments appear in italic only.
The database functions provide summary statistics on a database or list in a worksheet. They enable you to analyze whether values in the list meet a specified condition, or criteria. Excel's database functions all use the same arguments: database, field, and criteria. These arguments can represent any range you specify in the worksheet.
For the database argument in any database function, you can specify a cell range (such as A5:G20) or a range name (such as DATABASE). Be sure to include the headers row in the database range or range name.
The field argument indicates the column you want to average. You can specify the field by its field name in quotation marks (such as "City"), by a reference to the cell containing the field name (such as E5), or by a number--1 always represents the first field (or column) in the database, 2 is the second, and so on.
The criteria argument can be a cell range (such as A1:G3), or a range name (such as CRITERIA). The criteria you enter in a worksheet must not overlap the database. Also, do not place the criteria range below the database, or it may be overwritten as you add records to the database.
Syntax: =DAVERAGE (database,field,criteria) The DAVERAGE function averages the numbers in the field of the database for those records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the average of the numbers in the column of the list that match the criteria you specify.
Example: In the following formula, the database being analyzed resides in B10:G75, the field being averaged has the heading Items, and the criteria is in a range with the name CritSales. Remember to use quotation marks when you reference a specific field name.
=DAVERAGE(B10:G75,"Items",CritSales)
Syntax: =DCOUNTA (database,field,criteria) The DCOUNTA function counts the number of nonblank cells in the field of the database for those records that satisfy the criteria. If the field argument is omitted, DCOUNTA counts all nonblank records in the database that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; (optional) field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the total count of the nonblank cells in the column of the list that match the criteria you specify.
Syntax: =DCOUNT (database,field,criteria) The DCOUNT function counts the cells containing numbers in the field of the database for those records that satisfy the criteria. If the field argument is omitted, DCOUNT counts all records in the database that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; (optional) field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the total count of the cells with numbers in the column of the list that match the criteria you specify.
Syntax: =DGET (database,field,criteria) The DGET function extracts from the database the single record that matches the criteria. If no records match the criteria, #VALUE! is returned. If more than one record matches the criteria, #NUM! is returned.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the record that matches the criteria you specify (or #VALUE! if no records match, or #NUM! if more than one record matches).
Syntax: =DMAX (database,field,criteria) The DMAX function finds the largest number in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the maximum value in the column of the list that matches the criteria you specify.
Syntax: =DMIN (database,field,criteria) The DMIN function finds the smallest number in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the minimum value in the column of the list that matches the criteria you specify.
Syntax: =DPRODUCT(database,field,criteria) The DPRODUCT function multiplies all values in the field of the database for records that satisfy the criteria. This function is similar to DSUM, but the values are multiplied rather than added.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the product of all values in the column of the list that match the criteria you specify.
Syntax: =DSTDEVP (database,field,criteria) The DSTDEVP function calculates the standard deviation of the entire population, based on the numbers in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the standard deviation of an entire population, based on the values in the column of the list that match the criteria you specify.
Syntax: =DSTDEV (database,field,criteria) The DSTDEV function calculates the standard deviation of a sample population, based on the numbers in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the standard deviation of a sample population, based on the values in the column of the list that match the criteria you specify.
Syntax: =DSUM (database,field,criteria) The DSUM function totals all numbers in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the sum of all values in the column of the list that match the criteria you specify.
Syntax: =DVARP (database,field,criteria) The DVARP function calculates the variance of an entire population, based on the numbers in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the estimated variance of an entire population, based on the values in the column of the list that match the criteria you specify.
Syntax: =DVAR (database,field,criteria) The DVAR function calculates the estimated variance (how the sample deviates from the average) of a sample population, based on the numbers in the field of the database for records that satisfy the criteria.
You Supply: database as the cell range or range name of the entire list; field as the field name in quotation marks, the cell reference of the field name, or the position of the field number in the database (use 1 for the first field, 2 for the second, and so on); criteria as a cell range or a range name.
Result is: the estimated variance of a sample population, based on the values in the column of the list that match the criteria you specify.
Although Excel can display dates and times in many different formats, Excel actually stores all dates as serial numbers and all times as fractions. The serial number represents the number of days from January 1, 1900, until the date you specify; a time is a decimal fraction of 24 hours. Serial numbers enable you to more easily perform calculations on dates, such as the number of days between two dates. The serial number for July 1, 1997, 3:00 p.m., is 35612.625, where 35612 is the number of days from the beginning of the century and .625 is the decimal fraction of 24 hours representing 3:00 p.m.
NOTE: When a date or time function includes the serial_number argument, you can supply a serial date number or a reference to a cell containing a date or time.
Syntax: =DATE (year,month,day) The DATE function produces the serial number for a specific date. Use DATE to calculate a serial number in formulas that produce a numeric year, month, or day. For example, you can use this function to determine the amount of days an account is past due by calculating the difference between the serial numbers of the date due and the current date.
TIP: Use the formula =DATE (year,month+1,0) to calculate the last day of the specified month. For example, =DATE (97,11+1,0) returns 11/30/97.
You Supply: year as a number from 1900 to 9999, or a reference to a cell containing a year number; month as a number from 1 to 12, or a reference to a cell containing a month number; and day as a number from 1 to 31, or a reference to a cell containing a day number.
Result is: a serial number that represents the specified date. If Excel displays the result in a Date format, change the cell format to General to see the serial number.
Example: The following formula produces the serial number 35612 if cell C20 contains the day number 1:
=DATE(1997,7,C20)
Syntax: =DAY(serial_number) The DAY function converts a serial_number to the number of the day of the month between 1 and 31. Format the cell as a number.
You Supply: serial_number as a number between 0 and 2958465, or as a date format that Excel recognizes.
Result is: a number representing the day of the month, between 1 and 31. If Excel displays the result in a Date format, change the cell format to General to see the serial number.
Examples: Each of the following formulas produce the day number 16:
=DAY(35627)
=DAY("16-Jul-97")
Syntax: =HOUR(serial_number) Hours are included in the fractional part of a day in a serial number. The HOUR function returns the hour number (based on a 24-hour clock) for the fractional day in the serial_number. Format the cell as a number.
You Supply: serial_number as a number between 0 and 2958465, or as a time format that Excel recognizes.
Result is: a number representing the hour of the day, between 0 (12:00 a.m.) and 23 (11:00 p.m.). If Excel displays the result in a Time format, change the cell format to General to see the serial number.
Example: The following formula produces the hour number 18:
=HOUR(35627.75)
Syntax: =MINUTE(serial_number) The MINUTE function returns the number of minutes from a serial_number. The fractional part of a day is based on a 24-hour clock. Format the cell as a number.
You Supply: serial_number as a number between 0 and 2958465, or as a time format that Excel recognizes.
Result is: a number representing the minute of the hour, between 0 and 59. If Excel displays the result in a Time format, change the cell format to General to see the serial number.
Example: The following formula produces the minute number 7:
=MINUTE(35627.755)
Syntax: =MONTH(serial_number) The MONTH function converts the serial_number to the number of the month (from 1 to 12). Format the cell as a number.
You Supply: serial_number as a number between 0 and 2958465, or as a date format that Excel recognizes.
Result is: a number representing the month of the year, between 1 and 12. If Excel displays the result in a Date format, change the cell format to General to see the serial number.
Example: The following formula produces the month number 7:
=MONTH(35627)
Syntax: =NOW( ) The NOW function calculates the serial number of the date and time in the computer's clock. Excel updates the date and time only when the worksheet is opened or recalculated. Be sure to include the empty parentheses when entering this function. NOW does not use an argument. Use the NOW function to stamp a worksheet with the date and time. Type =NOW( ) in a cell. If the result doesn't appear as a date and time, format the cell with the desired date or time format. (See also the "Today (Current Date)" function.)
TIP: To freeze a date or time you entered with the NOW function, copy the cell with Edit, Copy; then use Edit, Paste Special (with the Values option) to paste over the cell you copied.
You Supply: only the function name followed by empty parentheses. The NOW function uses no arguments.
Result is: the current date and time, in the format m/d/yy h:mm (such as 11/6/97 21:41).
Syntax: =SECOND(serial_number) The SECOND function returns the number of
seconds (between 0 and 59) in the fractional part of the serial_number.
You Supply: serial_number as a number between 0 and 2958465, or as
a time format that Excel recognizes.
Result is: a number representing the second of the hour, between 0 and 59. If Excel displays the result in a Time format, change the cell format to General to see the serial number.
Syntax: =TIME(hour,minute,second) The TIME function calculates the serial number when given the hour, minute, and second of time on a 24-hour clock. The time format 0:00:00 represents 12:00:00 a.m., and the format 23:59:59 represents 11:59:59 p.m.
You Supply: hour as a number from 0 to 23, or a reference to a cell containing an hour number; minute as a number from 0 to 59, or a reference to a cell containing a minute number; and second as a number from 0 to 59, or a reference to a cell containing a second number.
Result is: a decimal number from 0 to 0.999988426 that represents the specified time. If Excel displays the result in a Time format, change the cell format to General to see the decimal number.
Example: The following formula produces the decimal number .903646 in the General format:
=TIME(21,41,15)
Syntax: =TODAY( ) The TODAY function calculates the serial number of the computer's current date. This acts the same as the NOW function, but does not return the decimal (time) portion of the serial number. Excel updates the serial number when the worksheet is opened or recalculated. Use the TODAY function to stamp a worksheet with the current date. Type =TODAY( ) in a cell. If the result doesn't appear as a date, format the cell with the desired date format. (See also the "Now (Current Date and Time)" function.)
TIP: To freeze the date you entered with the TODAY function, copy the cell with Edit, Copy; then use Edit, Paste Special (with the Values option) to paste over the cell you copied.
You Supply: only the function name followed by empty parentheses. The TODAY function uses no arguments.
Result is: the current date, in the format m/d/yy (such as 11/6/97).
Syntax: =WEEKDAY(serial_number,return_type) The WEEKDAY function converts the serial_number to the day of the week. The result is a number from 1 (Sunday) to 7 (Saturday). The optional return_type argument determines what day of the week to start with. With a return_type of 1 (or no return type), number 1 is Sunday and 7 is Saturday. With a return_type of 2, number 1 is Monday and 7 is Sunday. With a return_type of 3, number 0 is Monday and 6 is Sunday.
You Supply: serial_number as a number between 0 and 2958465, or as a date format that Excel recognizes; (optional) return_type as a number between 1 and 3.
Result is: a number representing the number of the day of the week, from 1 to 7. If Excel displays the result in a Date format, change the cell format to General to see the serial number.
Example: The following formula produces 5 (Friday):
=WEEKDAY("4-Jul-97",2)
Syntax: =YEAR(serial_number) The YEAR function converts the serial_number into the year.
You Supply: serial_number as a number between 0 and 2958465, or as a date format that Excel recognizes.
Result is: a number representing the year, between 1900 and 9999. If Excel displays the result in a Date format, change the cell format to General to see the serial number.
Example: The following formula produces the year 1997:
=YEAR(35627)
Instead of entering and calculating complex financial equations manually, you can use Excel's financial functions to greatly ease this task. Excel provides several financial functions that solve annuity problems. An annuity is a series of regular cash flows over a period of time. For example, cash flows may be housing payments that occur according to a regular time period.
Excel also includes financial functions to analyze uneven cash flows and to calculate depreciation using the double-declining balance depreciation method.
TROUBLESHOOTING: The error #NUM! appeared in the cell after I entered a financial function. What does this mean? You may have incorrectly entered the positive or negative signs for pmt or pv. Remember that money you are paying out should appear as a negative number.
Syntax: =FV(rate,nper,pmt,pv,type) The FV function determines
the future value of a series of cash flows of equal pmt amounts made
at even periods for nper periods at the constant interest rate.
A lump sum, pv, can be invested at the beginning of the term. If no
values are entered for pv and type, they are considered
to be zero. The periodic interest (rate) must have the same unit as nper--the
number of periods (such as months) in the life of the cash flow. For example, the
annual interest rate should be divided by 12 if payments or receipts are monthly.
You Supply: rate as the interest rate per period; nper as the total
number of periods; pmt as the payment amount made each period; (optional)
pv as the present value; (optional) type as 0 if payment is at the
end of the period, or 1 if payment is at the beginning of the period.
Result is: the future value of the investment, expressed in a dollar amount.
Example: The following formula calculates the worth of an investment at the end
of the term, if you invest $1,000 as a lump sum and add $200 at the start of each
month for 5 years (60 months) at an interest rate of 8 percent compounded monthly:
=FV(.08/12,60,-200,-1000,1)
The result is $16,283.19. Notice that amounts you pay out are expressed using negative numbers.
Syntax: =IPMT(rate,per,nper,pv,fv,type) The IPMT function calculates the interest portion of a payment on an annuity. You can use this function to calculate the interest paid on a mortgage at some period, per, within the term of the mortgage, nper. The value of per must be in the range 1 to nper. If no values are entered for fv and type, they are considered to be zero.
You Supply: rate as the interest rate per period; per as the period number; nper as the total number of periods; pv as the present value; (optional) fv as the future value; (optional) type as 0 if payment is at the end of the period, or 1 if payment is at the beginning of the period.
Result is: the interest payment for the specified period, expressed in a dollar amount.
Example: The following formula calculates how much was paid toward interest in the tenth month for a flat-rate mortgage of $130,000 made at 9 percent interest for 20 years:
=IPMT(.09/12,10,240,130000)
The result is ($961.46). The result is negative because it is the amount you paid out.
Syntax: =RATE(nper,pmt,pv,fv,type,guess) The RATE function calculates
the interest rate for the annuity that you define with the arguments. If no values
are entered for fv and type, they are considered to be zero. If you
do not enter an estimated interest rate for guess, Excel uses 10 percent (.1).
RATE can return more than one solution, depending on the value used for guess.
If guess is too far from the correct value, Excel may not be able to make
an estimate and may return #NUM!.
You Supply: per as the total number of periods; pmt as the payment
amount; pv as the present value; (optional) fv as the future value;
(optional) type as 0 if payment is at the end of the period, or 1 if payment
is at the beginning of the period; (optional) guess as your guess for what
the rate will be.
Result is: the interest rate per period, expressed as a percentage.
Example: The following formula results in an interest rate of 1.02 percent per month:
=RATE(36,-400,12000)
Syntax: =NPV(rate,value1,value2, ...) The NPV function calculates the net present value of a series of cash flows found in the range or array of value1, value2, and so on, given a discount rate equal to rate. The net present value of a series of cash flows is the value that a future stream of cash represents in terms of cash today, given the fact that future cash can be invested to earn the rate percentage. The cash flows are considered to be at the end of each period. Cash flows do not have to be equal amounts. The rate is the rate per period.
You Supply: rate as the discount rate per period; value1 as the first cash flow; (optional) value2 as the second cash flow; and so on up to 29 maximum cash flow amounts.
Result is: the net present value, expressed in a dollar amount.
Syntax: =NPER(rate,pmt,pv,fv,type) The NPER function calculates the number of periods required to create the annuity specified by the given arguments. If no values are entered for fv and type, they are considered to be zero.
You Supply: rate as the interest rate per period; pmt as the payment amount made each period; pv as the present value; (optional) fv as the future value; (optional) type as 0 if payment is at the end of the period, or 1 if payment is at the beginning of the period.
Result is: the number of periods for an investment.
Example: The following formula calculates the number of periods required for an annuity totaling $10,000, with payments of $500 per period at 10 percent interest.
=NPER(0.10/12,-500,10000)
The result is approximately 22 payments.
Syntax: =PMT(rate,nper,pv,fv,type) The PMT function calculates the periodic payment for different types and future values (fv) of investments given the investment's rate, term (nper), and present value (pv). If no values are entered for fv and type, they are considered to be zero.
You Supply: rate as the interest rate per period; nper as the total number of periods; pv as the present value; (optional) fv as the future value; (optional) type as 0 if payment is at the end of the period, or 1 if payment is at the beginning of the period.
Result is: the periodic payment for a loan, expressed in a dollar amount.
Example: The following formula calculates the periodic payment for a loan totaling $120,000 over 30 years at 10 percent interest:
=PMT(.1/12,360,120000)
The result is ($1,053.09). The result is negative because it represents the amount you pay out.
Syntax: =PV(rate,nper,pmt,fv,type) The PV function calculates the present value of a series of future cash flows of equal pmt amounts made at even periods for nper periods at the constant interest rate. PV is the amount in current dollars that equals an even cash flow in the future. If the amounts of the cash flow are uneven, use the NPV function. If no values are entered for fv and type, they are considered to be zero.
You Supply: rate as the interest rate per period; nper as the total number of periods; pmt as the payment amount; (optional) fv as the future value; (optional) type as 0 if payment is at the end of the period, or 1 if payment is at the beginning of the period.
Result is: the present value of an investment (the total amount a series of future payments is worth now), expressed in a dollar amount.
Example: The following formula calculates the total amount of a car loan you can afford if you know that you can pay $300 per month for the next four years at 11 percent interest:
=PV(0.11/12,48,-300)
The result is $11,607.43.
Syntax: =PPMT(rate,per,nper,pv,fv,type) The PPMT function calculates the principal portion of a payment made on an amortized investment. This portion is the part of the PMT function that reduces a loan balance. If no values are entered for fv and type, they are considered to be zero.
You Supply: rate as the interest rate per period; per as the period number; nper as the total number of periods; pv as the present value; (optional) fv as the future value; (optional) type as 0 if payment is at the end of the period, or 1 if payment is at the beginning of the period.
Result is: the principal portion of a payment for the specified period, expressed in a dollar amount.
Example: The following formula calculates how much was paid toward principal in the tenth month for a flat-rate mortgage of $130,000 made at 9 percent interest for 20 years:
=PPMT(.09/12,10,240,130000)
The result is ($208.18). The result is negative because it is the amount you paid out.
Syntax: =SLN(cost,salvage,life) The SLN function returns the annual amount of straight-line depreciation when given the initial cost of an item, the salvage value at the end of the item's economic life, and the economic life of the item.
You Supply: cost as the initial cost of the asset; salvage as the salvage value at the end of the life of the asset; life as the useful life of the asset.
Result is: the straight-line depreciation of an asset for one period, expressed as a dollar amount.
Example: The following formula results in depreciation of $900 per year:
=SLN(12000,7500,5)
Syntax: =SYD(cost,salvage,life,per) The SYD function calculates the depreciation for the period, per, using the sum-of-years' digits depreciation method. You must indicate the initial cost, the salvage value at the end of the economic life, and the life of the item.
You Supply: cost as the initial cost of the asset; salvage as the salvage value at the end of the life of the asset; life as the useful life of the asset; per as the period.
Result is: the sum-of-years' digits depreciation of an asset for a specified period, expressed as a dollar amount.
Examples: The following formulas result in depreciation of $1,500 for the first year, and $1,200 for the second year for the same asset:
=SYD(12000,7500,5,1)
=SYD(12000,7500,5,2)
Syntax: =VDB(cost,salvage,life,start_period, end_period,factor,no_switch) The variable-declining balance depreciation function returns the depreciation on an asset for the period you indicate. The cost, salvage, and life arguments have the same definitions as described in earlier functions. Start_period is the period at which you want to start calculating depreciation and end_period is the ending period for the calculation. Both must be in the same units as the life. Factor is the rate at which the balance declines. If factor is omitted, it is assumed to be 2 (for double-declining balance).
No_switch is a logical argument indicating whether VDB should switch to
straight-line depreciation when it is greater than the declining-balance depreciation.
Using TRUE for no_switch prevents the switch to straight-line method. FALSE,
or omitting the no_switch argument, switches to the straight-line method.
All arguments must be positive.
You Supply: cost as the initial cost of the asset; salvage as the
salvage value at the end of the life of the asset; life as the useful life
of the asset; start_period as the period to start calculating depreciation;
end_period as the ending period for the calculation; (optional) factor
as the rate at which the balance declines; (optional) no_switch as TRUE to
prevent switching to SLD, or FALSE to switch to SLD.
Result is: the variable-declining balance depreciation of an asset for a specified period, expressed in a dollar amount.
The information functions are provided primarily for compatibility with worksheets from other vendors. These functions enable you to test for conditions such as which worksheets are open, whether a cell is formatted with Times Roman italic, whether the worksheet is running on a Mac or PC, and how much memory is available.
Syntax: =INFO(type_text) The INFO function returns information on the current operating environment. Use the type_text argument to indicate what type of information you want. The following table lists specific arguments you can use for type_text.
| Type_text | Returned Value |
| "directory" | Current directory |
| "memavail" | Memory available |
| "memused" | Memory used for data, in bytes |
| "numfile" | Number of active worksheets |
| "origin" | Absolute A1-style of references as text |
| "osversion" | Operating system version |
| "recalc" | Recalculation mode: Automatic or Manual |
| "release" | Microsoft Excel version |
| "system" | Operating system name: Windows = pcdos; Macintosh = Mac |
| "totmem" | Memory available, in bytes; includes memory in use |
Result is: the returned value, based on the type_text argument you specify (refer to table above).
Example: The following formula returns Automatic if your worksheet is set to automatic calculation:
=INFO("recalc")
Syntax: =ISfunction(value) Excel provides 11 functions that determine whether a cell meets certain conditions, such as whether it is blank or contains an error value. Depending on the status of the cell, the ISfunction produces either a TRUE or FALSE value. ISfunctions are most useful when used with the IF function to test whether a cell or range is blank or contains numbers, text, or errors. You also can use ISfunctions to test for the appropriate type of entry. The ISfunctions and their results are listed in the following table.
| Function | Result |
| ISBLANK(value) | TRUE if value is a blank reference; FALSE if value is nonblank |
| ISERR(value) | TRUE if value is any error other than #N/A; FALSE for any other value |
| ISERROR(value) | TRUE if value is any error value; FALSE if value is not an error value |
| ISEVEN(value) | TRUE if the integer portion of the value is aneven number; FALSE if the value is odd |
| ISLOGICAL(value) | TRUE if value is a logical value; FALSE if value is not a logical value |
| ISNA(value) | TRUE if value is the #N/A error value; FALSE if value is not #N/A |
| ISNONTEXT(value) | TRUE if value is not text; FALSE if value is text |
| ISNUMBER(value) | TRUE if value is a number; FALSE if value is not a number |
| ISODD(value) | TRUE if the integer portion of the value is an odd number; FALSE if the value is even |
| ISREF(value) | TRUE if value is a reference; FALSE if value is not a reference |
| ISTEXT(value) | TRUE if value is text; FALSE if value is not text |
Result is: TRUE or FALSE, depending on the result of the test.
Example: The following formula includes an IF function to test whether or not cell C10 contains a number. If cell C10 contains a number, then the cell containing the formula displays Valid entry; otherwise the cell displays Please enter a number. (See also the "Logical IF" function.)
=IF(ISNUMBER(C10),"Valid entry","Please enter a number")
Syntax: =NA( ) The NA function always produces the error value #N/A, which means "No value available." NA does not use an argument, but the parentheses are required. You can type #N/A directly into a cell to obtain the same result. Use this function to mark empty cells in the worksheet and avoid unintentionally including blank cells in your calculations. If you enter #N/A into blank data-entry cells, for example, the formulas that depend on those cells result in #N/A.
You Supply: only the function name followed by empty parentheses. The NA function uses no arguments.
Result is: the value #N/A.
Syntax: =TYPE(value) The TYPE function determines the type of a cell's contents and produces a corresponding code, as shown in the table below.
| Value | Result |
| Number | 1 |
| Text | 2 |
| Logical value | 4 |
| Formula | 8 |
| Error value | 16 |
| Array | 64 |
Result is: either 1, 2, 4, 8, 16, or 64, depending on the type of cell contents (see table above).
Example: The following formula results in 1 if cell J24 contains a number; if cell J24 contains text, the formula result is 2:
=TYPE(J24)
The logical functions enable you to add decision-making and logical tests to your worksheets. The IF statement is useful for testing conditions and making decisions based on a cell's contents. The AND and OR functions can test multiple criteria or test conditions for use in IF functions.
Syntax: =AND(logical1,logical2, ...) The AND function joins test conditions, and returns TRUE if all logical arguments are TRUE; or FALSE if any logical argument is FALSE. Arguments for the AND function must be single logical values (or arrays that contain logical values) and they cannot contain more than 30 logical values. The #VALUE! error appears if there are no logical values in the arguments.
You Supply: logical1 as the first condition you want to test; (optional) logical2 as the second condition you want to test; and so on up to 30 conditions maximum.
Result is: TRUE if all logical arguments are TRUE; or FALSE if any one logical argument is FALSE.
Example: The following formula result is TRUE only when D15 is not zero and G23 is less than 30:
=AND(D15,G23<30)
Syntax: =FALSE( ) The FALSE function always produces a logical FALSE result and uses no arguments. You can type the FALSE function with or without the parentheses.
You Supply: only the function name, or the function name followed by empty parentheses. The FALSE function uses no arguments.
Result is: always FALSE.
Syntax: =IF(logical_test,value_if_true,value_if_false) The IF function produces value_if_true when the logical_test evaluates as TRUE; or produces value_if_false when the logical_test evaluates as FALSE. If value_if_true is omitted, Excel returns the value TRUE when logical_test evaluates as TRUE. If value_if_false is omitted, Excel returns the value FALSE when logical_test evaluates as FALSE. IF is one of the most valuable functions in Excel; this function can test cells and make decisions based on the cell contents. Use the AND, OR, and NOT as the logical_test function with the IF function to make complex decisions.
You Supply: logical_test as any value or expression that can evaluate to TRUE or FALSE; (optional) value_if_true as the value that is returned if logical_test is TRUE; and (optional) value_if_false as the value that is returned if logical_test is FALSE. Use quotation marks for value_if_true or value_if_false if the argument is to return the specified text.
Result is: the value_if_true argument if the test evaluates to TRUE, or the value_if_false argument if the test evaluates to FALSE.
Example: The following formula returns Valid if the contents of cell D17 is greater than 10 and less than 30; otherwise the formula returns Invalid:
=IF(AND(D17>10,D17<30),"Valid","Invalid")
Syntax: =NOT(logical) The NOT function reverses the result of the logical argument from TRUE to FALSE or from FALSE to TRUE. Use this function to return the opposite condition of the logical_test in an IF statement.
You Supply: logical as any value or expression that can evaluate to TRUE or FALSE.
Result is: FALSE for a TRUE result, or TRUE for a FALSE result.
Example: The following formula tests whether cell D17 contains the result 10 or 30 and produces the message Not 10 or 30 when the cell does not contain either of those results; otherwise, the formula result is Contains 10 or 30:
=IF(NOT(OR(D17=10,D17=30)),"Not 10 or 30","Contains 10 or 30")
Syntax: =OR(logical1,logical2,...) The OR function joins test conditions, and returns TRUE if one or more logical arguments is TRUE; or FALSE only when all logical arguments are FALSE. OR is limited to 30 or fewer arguments. Arguments cannot be blank cells, error values, or text.
TIP: Use ISfunctions within OR functions to test for blank cells, error values, or text.
You Supply: logical1 as the first condition you want to test; (optional) logical2 as the second condition you want to test; and so on up to 30 conditions maximum.
Result is: TRUE if any logical argument is TRUE; or FALSE if all logical arguments are FALSE.
Example: The following formula tests whether cell D17 contains the result 10 or 30 and produces the message Contains 10 or 30 when it does; otherwise, the formula produces the message Not 10 or 30:
=IF(OR(D17=10,D17=30),"Contains 10 or 30","Not 10 or 30")
Syntax: =TRUE( ) The TRUE function always produces a logical TRUE result, and uses no arguments. You can type the TRUE argument with or without the parentheses.
You Supply: only the function name, or the function name followed by empty parentheses. The TRUE function uses no arguments.
Result is: always TRUE.
The lookup functions enable your worksheets to retrieve a value from within a table. You can use the INDEX function, for example, to extract specific values from within an array. The reference functions are necessary when you need to determine cell contents, ranges, or selected areas. Some of them, such as OFFSET, also are used in macro sheets.
Syntax: =ADDRESS(row_num,column_num,abs_num, a1,sheet_text)
The ADDRESS function produces a cell reference in text form for the cell indicated
by the row_num and col_num. Use one of four values in
abs_num to specify the type of reference: absolute reference (the default)
is 1; absolute row, relative column is 2; relative row, absolute column is 3; and
relative reference is 4. If the a1 argument is TRUE, or omitted, Excel returns
A1 style cell references. FALSE returns the R1C1 style cell reference. The sheet_text
argument is the name of the worksheet or macro sheet used by the reference.
You Supply: row_num as the row number for the cell reference; column_num
as the column number for the cell reference; (optional) abs_num as the reference
type; (optional) a1 as a logical value that specifies the reference style;
and (optional) sheet_text as the name of the worksheet to be used as the external
reference.
Result is: a cell reference in text form for the cell indicated by the row number and column number you specify.
Example: The following formula returns $B17:
=ADDRESS(17,2,3,TRUE)
Syntax: =AREAS(reference) The AREAS function returns the number of areas in reference. Use the AREAS function to find how many selections are within an area.
You Supply: reference as a reference to one or more cells or ranges.
Result is: the number of areas in reference. Each area can be either a single cell or a range of cells.
Example: The following formula returns 2 when the range named PRINTAREA is defined as two separate ranges:
=AREAS(PRINTAREA)
Syntax: =CHOOSE(index_num,value1,value2, ...) The CHOOSE function selects from the list of values a value that corresponds to the index_num. For example, when the index_num is 2, the function chooses value2. CHOOSE displays #VALUE when the index_num is less than one or greater than the number of items in the list.
You Supply: index_num as a number between 1 and 29, specifying which value argument is selected; value1 as a cell reference, name, formula, function, or text argument; (optional) value2 as a cell reference, name, formula, function, or text argument; and so on up to 29 values maximum.
Result is: the value or action to perform from a list of values, based on the index_num.
Example: The following formula returns 27 when cell A10 contains 4:
=CHOOSE(A10,21,7,19,27)
Syntax: =COLUMN(reference) The COLUMN function produces the column number of the reference cell. If reference is an array or a range, then the column numbers of each column in the range return as a horizontal array. If the reference argument is not specified, COLUMN produces the column number of the cell that contains the function. Reference cannot contain multiple areas.
TIP: Use the INDEX function instead of the COLUMN function if you want to read values from an array.
You Supply: reference as a cell reference or range.
Result is: the column number of the cell reference, or an array if reference is a range.
Syntax: =HLOOKUP(lookup_value,table_array, row_index_num,range_lookup) The HLOOKUP function looks across the top row of the range defined by table_array until the lookup_value is met; then looks down that column to the row specified by row_index_num. Range_lookup is a logical value (TRUE or FALSE). If the value is TRUE or omitted, HLOOKUP will return an approximate match. If FALSE, HLOOKUP will return an exact match or #N/A if no match is found. Values in the first row of table_array must be in ascending order, both alphabetically (A - Z) and numerically (0 - 9). The lookup_value and the values in the first row of the table_array can be text, numbers, or logical values.
row_index_num begins with 1. To return a value from the first row, use
1, and from the second row, use 2, and so on. If row_index_num is less than
1, HLOOKUP produces the #VALUE! error. If row_index_num is greater than the
number of rows in the table, #REF! is displayed.
You Supply: lookup_value as a value, reference, or text string from the
first row of the table; table_array as a reference to the table range; row_index_num
as the row number in the table for the value you are looking up; and (optional) range_lookup
as a logical value of TRUE or FALSE.
Result is: the value in the same column as lookup_value in a table, based on the row you specify; or #N/A if no match is found.
Syntax: =HYPERLINK(link_location, friendly_name) The HYPERLINK function inserts a hyperlink into your work- sheet that jumps to a specified linked location, such as a file on a hard drive or network, or an Internet address. The link_location argument is the full path and filename or the URL for an Internet address. The friendly_name argument is the text or value actually displayed in the cell. If you do not include a friendly_name argument the value of the link_location argument displays.
You Supply: link_location as the path and filename to a file, or a URL address; and (optional) friendly_name as the text you want to display in the cell (in place of the path and filename). Use quotation marks with the friendly_name argument.
Result is: the hyperlink, inserted in the current cell of the worksheet. Click the hyperlink to jump to the specified file or Internet address. You must have Internet access to use a hyperlink that jumps to an Internet address.
Syntax: =INDEX(array,row_num,column_num) In the array form of the INDEX function, row_num and column_num return the value of a cell in the array. The definitions of row_num and column_num are the same as described in the reference version of the INDEX function.
You Supply: array as a range of cells or array constant; row_num as the row from which to return a value; and (optional) column_num as the column from which to return a value.
Result is: a value or reference to a value in a table or range.
Example: The following formula returns 10:
=INDEX({2,3,4;10,11,12},2,1)
Syntax: =INDEX(reference,row_num,column_num, area_num) The reference form of the INDEX function produces a cell reference from within the reference specified, and at the intersection of the row_num and column_num. If reference is a single row or column, then either the column_num or row_num argument can be omitted (but not both). Other functions convert the value returned by INDEX to a cell reference or value as needed. The referenced area is reference. If this area contains multiple ranges, enclose reference in parentheses with each range separated by commas. If reference contains more than one area, area_num can choose between areas. The arguments row_num and column_num choose a cell in the area specified. The first row or column is 1. Omitting the row_num or column_num or using 0 returns a reference for the entire row or column. A second form of the INDEX function is used with arrays.
NOTE: If either row_num or column_num occurs outside the specified reference area, the INDEX function results in the message #REF!
You Supply: reference as a reference to one or more cell ranges; row_num as the row from which to return a value; (optional) column_num as the column from which to return a value; and (optional) area_num as a range in reference from which to return a value.
Result is: a value or reference to a value in a table or range.
Example: The following formula returns a reference or value in cell C5, which is the second row and third column in the first area:
=INDEX((A4:D7,F10:J15),2,3,1)
Syntax: =LOOKUP(lookup_value,array) The array form of the LOOKUP
function is similar to HLOOKUP and VLOOKUP. LOOKUP searches for a match to lookup_value
in the first row or the first column of the array, depending
on the shape of the array. If the array is square, or wider
than tall, LOOKUP searches across the first row for the lookup_value. If
the array is taller than it is wide, the search proceeds down the first column. If
LOOKUP cannot find the lookup_value, it finds the largest value less than
the lookup_value. If lookup_value is smaller than the smallest value
in the row or column being examined, the message #N/A is returned. The value
returned is taken from the last row or column in the array that matches the lookup_value.
The row or column being examined for the lookup_value must be sorted in ascending
order.
You Supply: lookup_value as a number, text, name, or logical value that
you are searching for; and array as a range of cells that contain
numbers, text, names, or logical values that you compare with lookup_value.
Result is: a value from a one-row or one-column range, or from an array.
Syntax: =MATCH(lookup_value,lookup_array, match_type) The MATCH function returns the position of the match for lookup_value in the lookup_array. The type of match is determined by match_type. The lookup_value can be a number, text, logical value, or cell reference. MATCH returns the row or column position in the array of the found item, not its value or cell reference. When combined with the INDEX function, the MATCH function enables you to find exact matches to a lookup_value or return an error. This prevents the possible use of an incorrect value returned by VLOOKUP, HLOOKUP, or LOOKUP. The types of matches are listed in the following table, with a description of what each match type finds.
| Match_type | Finds |
| 1, or omitted | Largest value less than or equal to lookup_value. The lookup_array must be in sorted order. The default is 1 if match_type is omitted. |
| 0 | First value that is an exact match. |
| -1 | Smallest value greater than or equal to lookup_value. The lookup_array must be in sorted order. |
Result is: the position of the match for lookup_value in the lookup_array.
Syntax: =OFFSET(reference,rows,cols,height,width) Returns the cell reference "offset" from a reference by a number of rows and a number of columns. The reference used is the reference argument. The reference may be a single cell or a range. The height and width of an offset range can be controlled by the height and width values. If height and width are omitted, OFFSET uses the height and width of the reference. Use OFFSET to retrieve data from a table of information. Use OFFSET with the SELECTION and FORMULA functions in Excel macros to select ranges or to enter values on work-sheets. Using OFFSET to specify the cell to act on is much faster than concatenating text references.
NOTE: If the offset extends beyond the edge of the worksheet, or if the row or height is less than one, the OFFSET function returns #REF!
You Supply: reference as a reference to a cell or range on which you want to base the offset; rows as the number of rows that you want the upper-left cell of the result to refer to; cols as the number of columns that you want the upper-left cell of the result to refer to; (optional) height as the height in number of rows that you want the result to be; and (optional) width as the width in number of columns that you want the result to be.
Result is: a reference to a range that is a specified number of rows and columns from a cell or range.
Example: The following formula returns the value stored in cell E9:
=OFFSET(B7,2,3)
Syntax: =ROW(reference) The ROW function results in the row number of the reference cell. If reference is a range, ROW produces a vertical array of the row numbers. If you don't specify the reference argument, ROW produces the row number of the cell in which the function is entered. Use the INDEX function to extract a row number as a specific element within ROW.
You Supply: reference as the cell or range for which you want the row number.
Result is: the row number of the reference.
Examples: The following formula returns 12:
=ROW(A12)
Syntax: =TRANSPOSE(array) The TRANSPOSE function transposes the current array so that the first row in the current array becomes the first column of the new array, the second row of the current array becomes the second column of the new array, and so on. Because the TRANSPOSE function produces an array as a result, you must enter the TRANSPOSE function as an array formula.
You Supply: array as a range of cells or array of values.
Result is: a vertical range of cells as a horizontal range, or a horizontal range of cells as a vertical range.
Syntax: =VLOOKUP(lookup_value,table_array, col_index_num,range_lookup) The VLOOKUP function looks down the left column of table_array until the lookup_value is met, and then looks across that row to the column specified by col_index_num. Values in the first column can be text, numbers, or logical values in ascending order. Upper- and lowercase text are considered the same. Range_lookup is a logical value (TRUE or FALSE). If the value is TRUE or omitted, VLOOKUP will return an approximate match; if FALSE, VLOOKUP will return an exact match or #N/A if no match is found. If VLOOKUP cannot find lookup_value, the function searches for the next largest value in the first column.
You Supply: lookup_value as a value, reference, or text string from the first column of the table; table_array as a reference to the table range; col_index_num as the column number in the table for the value you are looking up; and (optional) range_lookup as a logical value of TRUE or FALSE.
Result is: the value in the same row as lookup_value in a table, based on the column you specify; or #N/A if no match is found.
Many of the functions you use in worksheet calculations fall in the math and trigonometry functions category. In addition to commonly used functions such as SUM and ROUND, this category also includes more complex scientific and engineering functions.
NOTE: Trigonometric functions use angles measured in radians. Use the following equations to convert between radians and degrees:
Radians = Degrees*p/180
Degrees = Radians*180/p
Syntax: =ABS(number) The ABS function returns the absolute (positive) value of the number.
You Supply: Number as the number for which you want the absolute value, or a reference to a cell containing number.
Result is: the absolute value of the number (the number without its sign).
Example: The following formula returns 18 when cell A10 contains -18:
=ABS(A10)
Syntax: =ACOS(number) The ACOS function produces the arccosine of the number in radians. ACOS is the inverse of the COS function. The number must be in the range -1 to 1. The resulting angle is in the range 0 to [pi] radians (0 to 180 degrees).
You Supply: Number as the cosine of the angle you want, between -1 and 1.
Result is: the arccosine of a number, in radians.
Example: The following formula returns 1.047198 (radians):
=ACOS(0.5)
Syntax: =ASIN(number) The ASIN function produces the arcsine of the number in radians. When given a number, the result of a sine function, ASIN, produces the original angle measured in radians. The number must be in the range -1 to 1. The resulting angle will be in the range -[pi]/2 to [pi]/2 radians (-90 to 90 degrees).
You Supply: Number as the sine of the angle you want, between -1 and 1.
Result is: the arcsine of a number, in radians.
Example: The following formula returns .523599 (radians):
=ASIN(0.5)
Syntax: =ATAN(number) The ATAN function produces the arctangent of the number as a radian angle. ATAN is the inverse of the TAN function. The resulting angle will be in the range -[pi]/2 to [pi]/2 radians (-90 to 90 degrees).
You Supply: Number as the tangent of the angle you want.
Result is: the arctangent of a number, in radians.
Syntax: =ATAN2(x_number,y_number) The ATAN2 function produces the arctangent for coordinate values of x_number and y_number. The resulting angle is in the range -[pi] to [pi] radians (-180 to 180 degrees) excluding -[pi] (-180 degrees). If x_number and y_number are both 0, the function produces the message #DIV/0!.
You Supply: x_number as the x-coordinate of the point; and y_number as the y-coordinate of the point.
Result is: the arctangent of x_number and y_number, in radians.
Syntax: =COMBIN(number,number_chosen) The COMBIN function produces the combination of items without regard to order. For example, if there are 15 socks in a drawer and you pull out two socks, there are =COMBIN(15,2) different combinations you could choose from (the answer is 105).
You Supply: Number as the total number of items; and number_chosen as the number of items you choose.
Result is: the number of possible combinations for a given number of items.
Syntax: =COS(number) The COS function produces the cosine of the radian angle number.
You Supply: Number as the angle in radians.
Result is: the cosine of the angle.
Syntax: =DEGREES(angle) The DEGREES function converts radians to degrees.
You Supply: angle as the angle in radians that you want to convert.
Result is: the number of degrees in the specified angle.
Syntax: =EVEN(number) The EVEN function rounds a number up to an even number. Negative numbers are rounded away from zero.
You Supply: Number as the value you want to round to an even number.
Result is: the number rounded up to the nearest even integer.
Example: The following formula returns 12:
=EVEN(10.6)
Syntax: =EXP(number) The EXP function returns e, the base of the natural logarithm, raised to the power of number. EXP is the inverse of the LN function. The value of e is 2.71828182845904.
You Supply: umber as the exponent applied to the base e.
Result is: e raised to the power of the specified number.
Example: The following formula returns 2.718282:
=EXP(1)
Syntax: =FACT(number) The FACT function returns the factorial of the number, which must be a positive number. A noninteger number is truncated.
You Supply: Number as the number you want the factorial of.
Result is: the factorial of the number.
Examples: The following formula returns 120 (1*2*3*4*5):
=FACT(5)
The following formula returns 6 (1*2*3):
=FACT(3.7)
Syntax: =INT(number) The INT function rounds the number down, to the nearest integer. Negative numbers are rounded away from zero.
NOTE: Use INT to round a number down to the nearest integer. Use TRUNC to truncate a number by removing the decimal portion, to a specified number of digits. Use ROUND to round a number to a specific number of places to the left or right of the decimal.
You Supply: Number as the number you want to round down to an integer.
Result is: the number rounded down to the nearest integer.
Examples: The following formula returns 9:
=INT(9.6)
The following formula returns -10:
=INT(-9.6)
Syntax: =LOG(number,base) The LOG function returns the logarithm of the number in the base specified. The value of the number must be positive. LOG uses base 10 if the base argument is omitted.
You Supply: Number as the positive number for which you want the logarithm; and (optional) base as the base of the logarithm.
Result is: the logarithm of a number, to the base you specify.
Example: The following formula returns 2.26186:
=LOG(12,3)
Syntax: =PI( ) The PI function returns the value of [pi]. An estimate of [pi], 3.14159265358979, is used. The parentheses must be included even though the function does not take an argument.
You Supply: only the function name followed by empty parentheses. The PI function uses no arguments.
Result is: the value of [pi]. The number of decimals displayed on-screen depends on the column width, although all decimals in [pi] are used in calculations.
Syntax: =MOD(number,divisor) The MOD function produces the remainder (modulus), of the number divided by the divisor. The #DIV/0! error appears if the divisor is zero.
You Supply: Number as the number for which you want to find a remainder after the division operation; and divisor as the number by which you want to divide number.
Result is: the remainder, after a number is divided by the divisor.
Example: The following formula returns 2:
=MOD(27,5)
Syntax: =LN(number) The LN function returns the natural log of the number in base e. LN is the inverse of the EXP function. The value of the number must be positive.
You Supply: Number as the positive number for which you want the natural logarithm.
Result is: the natural logarithm of the number.
Example: The following formula returns 1.252763:
=LN(3.5)
Syntax: =ODD(number) The ODD function produces a number rounded up to the closest odd number.
You Supply: Number as the value you want to round up to an odd number.
Result is: the number rounded up to the nearest odd integer.
Example: The following formula returns 75:
=ODD(73.5)
Syntax: =PRODUCT(number1,number2,...) The PRODUCT function multiplies number1 by number2 by the rest of the arguments. You can specify up to 30 arguments. Arguments that are blank cells, logical values, error values, or text are ignored. Text that can be converted into a numeric value is converted.
You Supply: Number1 as the first number that you want to multiply; (optional) number2 as the second number you want to multiply; and so on up to 30 numbers maximum.
Result is: the product of all the numbers supplied as arguments.
Example: The following formula returns 24 when cells A1:A4 contain the numbers 1, 2, 3, and 4:
=PRODUCT(A1:A4)
Syntax: =RADIANS(angle) The RADIANS function converts degrees to radians.
You Supply: angle as an angle in degrees that you want to convert.
Result is: the radian of the specified angle.
Syntax: =RAND( ) The RAND function produces a random decimal number from 0 to 1. The function does not take an argument between the parentheses. To produce a new random number, press F9 to recalculate the formula. To freeze a random number, copy it with Edit, Copy and paste it in the same cell (using Edit, Paste Special, with the Paste Values and Operations None options). If you receive an error message when you try to use this function, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, choose Tools, Add-Ins to enable this feature.
TIP: To produce a random number between 0 and a number greater than 1, enter a formula that multiplies that number by the result of the RAND function. To find a random number between 0 and 100 if the RAND function is in cell B5, for example, use the formula =100*B5.
NOTE: You can use the RANDBETWEEN function to find a random number between any two numbers. To find a random number between 20 and 50, for example, use the formula =RANDBETWEEN(20,50).
You Supply: only the function name followed by empty parentheses. The RAND function uses no arguments.
Result is: a random number between 0 and 1.
Syntax: =ROUND(number,num_digits) The ROUND function rounds the number to the number of digits specified in num_digits. If num_digits is positive, the number rounds to the specified decimal places to the right of the decimal point. If num_digits is zero, the number rounds to the nearest integer. If num_digits is negative, the number rounds to the left of the decimal point.
NOTE: Use ROUND to round a number to a specific number of places to the left or right of the decimal. Use INT to round a number down to the nearest integer. Use TRUNC to truncate a number by removing the decimal portion, to a specified number of digits.
You Supply: Number as the number you want to round; and num_digits as the number of digits to which you want to round.
Result is: a number rounded to the specified number of digits.
Examples: The following formula returns 102.93:
=ROUND(102.927,2)
The following formula returns 103:
=ROUND(102.927,0)
The following formula returns 100:
=ROUND(102.927,-2)
Syntax: =SIN(number) The SIN function produces the sine of the radian angle number.
You Supply: Number as the angle in radians for which you want the sine.
Result is: the sine of an angle.
Syntax: =SQRT(number) The SQRT function returns the square root of the number. The value of the number must be positive.
You Supply: Number as the positive number for which you want a square root.
Result is: the square root of number.
Example: The following formula returns 7.937254:
=SQRT(63)
Syntax: =SUM(number1,number2,...) The SUM function calculates the sum of the arguments. Arguments can be individual values or ranges and are limited to 30 arguments. Arguments that cannot be converted from text to numbers or error values are ignored.
TIP: Use the AutoSum button in the Standard toolbar to automatically sum the numbers in an adjacent range. Excel supplies a suggested range of numbers to sum; if the range is incorrect, drag in the worksheet to indicate the correct range and then press Enter.
You Supply: Number1 as the first number, cell, or range you want to sum; (optional) number2 as the second number, cell, or range you want to sum; and so on up to 30 arguments maximum.
Result is: the sum of the supplied arguments.
Syntax: =TAN(number) The TAN function produces the tangent of the radian angle number.
You Supply: umber as the angle in radians for which you want the tangent.
Result is: the tangent of an angle.
Syntax: =TRUNC(number,num_digits) The TRUNC function changes the number to an integer by cutting off, or truncating, the decimal fraction portion. If num_digits is omitted, it is assumed to be zero.
NOTE: Use TRUNC to truncate a number by removing the decimal portion, to a specified number of digits. Use INT to round a number down to the nearest integer. Use ROUND to round a number to a specific number of places to the left or right of the decimal.
You Supply: Number as the number you want to truncate; and (optional) num_digits as a number specifying the precision of the truncation.
Result is: the number truncated to the number of decimals specified.
Example: The following formula returns 5.63:
=TRUNC(5.6357,2)
Statistical functions can help you with simple problems, such as finding an average or counting items. Statistical functions also can perform simple statistical analysis, such as finding the standard deviation and variance of a set of numbers.
Syntax: =AVERAGE(number1,number2,...) The AVERAGE function returns the average (mean) of the arguments. Arguments may be single values, cells, or ranges. The ranges can contain numbers, cell references, or arrays that contain numbers. Text, logical values, errors, and blank cells are ignored. AVERAGE can take from 1 to 30 arguments.
You Supply: Number1 as the first number or range you want to average; (optional) number2 as the second number or range you want to include in the average; and so on up to 30 arguments maximum.
Result is: the average, or arithmetic mean, of the supplied arguments.
Syntax: =COUNTIF(range,criteria) The COUNTIF function counts the number of cells within range that match criteria. Only nonblank cells are included in the count. The criteria argument can be a number, text, or an expression. If criteria is not just a number, you must enclose it within quotation marks.
You Supply: range as the range of cells you want to count; and criteria as the condition that defines which cells you want to count.
Result is: the number of cells in the range that meet the specified criteria.
Example: The following formula returns 1 if the range B1:B4 contains the numbers 57, 102, 84, and 98:
=COUNTIF(B1:B4,">100")
Syntax: =COUNT(value1,value2,...) The COUNT function produces a count of the numbers in the arguments. The value arguments can be numbers, cell references, or arrays that contain numbers. Text, logical values, errors, and blank cells are not counted. You can include from 1 to 30 arguments in COUNT.
NOTE: Although the arguments in the COUNT function can reference ranges that include both numbers and text, only the numbers are actually counted.
You Supply: value1 as the first number or range you want to count; value2 as the second number or range you want to include in the count; and so on up to 30 arguments maximum.
Result is: the total count of the numbers in the supplied arguments.
Syntax: =MAX(number1,number2,...) The MAX function produces the largest value among the arguments. MAX can take up to 30 arguments. Arguments that are error values or text that cannot be interpreted as a number are ignored. Within a referenced array or range, any empty cells, logical values, text, or error values are ignored.
You Supply: Number1 as the first number or range for which you want the maximum number; (optional) number2 as the second number or range for which you want the maximum number; and so on up to 30 arguments maximum.
Result is: the largest value in the supplied arguments.
Syntax: =MEDIAN(number1,number2,...) The MEDIAN function returns the median value of the arguments. The median value is the middle value in a set of numbers. MEDIAN can take up to 30 arguments. If MEDIAN includes an even number of arguments, then MEDIAN calculates the average of the two middle values.
You Supply: Number1 as the first number or range for which you want the median value; (optional) number2 as the second number or range for which you want the median value; and so on up to 30 arguments maximum.
Result is: the median (middle) value of the supplied arguments.
Examples: The following formula returns 4:
=MEDIAN(1,4,2,6,9)
The following formula returns 5 (the average of the two middle values, 4 and 6):
=MEDIAN(1,4,2,6,9,10)
Syntax: =MIN(number1,number2,...) The MIN function produces the smallest value among the arguments. MIN can take up to 30 arguments. Arguments that are not numbers are ignored. If the arguments contain no numbers, MIN produces 0.
You Supply: Number1 as the first number or range for which you want the minimum value; (optional) number2 as the second number or range for which you want the minimum value; and so on up to 30 arguments maximum.
Result is: the smallest value in the supplied arguments.
Syntax: =STDEVP(number1,number2,...) The STDEVP function calculates the standard deviation of a population, where the entire population is listed in the arguments. STDEV can take up to 30 arguments. If the arguments do not include the entire population, use STDEV instead. (See also the "Standard Deviation (Sample Population)" function.)
You Supply: Number1 as the first number or reference to include in the population; (optional) number2 as the second number or reference to include in the population; and so on up to 30 arguments maximum.
Result is: the standard deviation based on all values in the population.
Syntax: =STDEV(number1,number2,...) The STDEV function calculates an estimate of the standard deviation of a population, based on a sample of the population. STDEV can take up to 30 arguments. If the arguments include the entire population, use STDEVP instead. (See also the "Standard Deviation (Entire Population)" function.)
You Supply: Number1 as the first number or reference to include in the sample; (optional) number2 as the second number or reference to include in the sample; and so on up to 30 arguments maximum.
Result is: the standard deviation based on a sample of values in the population.
Syntax: =VARP(number1,number2,...) The VARP function calculates the variance of a population, where the entire population is listed in the arguments. Use VAR instead if the arguments contain only a sample of the population. (See also the "Variance (Sample Population)" function.)
You Supply: Number1 as the first number or reference to include in the population; (optional) number2 as the second number or reference to include in the population; and so on up to 30 arguments maximum.
Result is: the variance based on all values in the population.
Syntax: =VAR(number1,number2,...) The VAR function calculates an estimate of the variance in a population, based on a sample of the population. Use VARP if the arguments contain the entire population. (See also the "Variance (Entire Population)" function.)
You Supply: Number1 as the first number or reference to include in the sample; (optional) number2 as the second number or reference to include in the sample; and so on up to 30 arguments maximum.
Result is: the variance based on a sample of values in the population.
The text functions enable you to manipulate text strings. You can extract portions of text from long strings of text, or you can change numbers and dates to text so that they can exceed a cell's width without producing a cell filled with #####. Numbers or dates converted to text can be joined to text in titles, sentences, and labels. Text functions are also useful for manipulating text that you want to convert to ASCII files.
Syntax: =CHAR(number) The CHAR function produces the character corresponding to the ASCII code number between 1 and 255.
You Supply: Number a number between 1 and 255 specifying the character you want.
Result is: the character specified by the code number, based on the character set that your computer uses.
Syntax: =CODE(text) The CODE function produces the ASCII code of the first letter in the specified text.
You Supply: text as the text for which you want the code of the first character.
Result is: a numeric code for the first character in a text string, based on the character set that your computer uses.
Syntax: =CLEAN(text) The CLEAN function removes from the specified text argument any characters that are lower than ASCII 32 or above ASCII 127. These characters are not printed. This function is useful for removing control codes, bells, and non-ASCII characters from imported text.
You Supply: text as the text string that you want to clean.
Result is: the text string excluding all nonprintable characters.
Syntax: =CONCATENATE(text1,text2,...) The CONCATENATE function joins text1 to text2. CONCATENATE can take up to 30 arguments. The arguments can include text strings, numbers, or single-cell references.
You Supply: text1 as the first text string you want to join into a single text string; (optional) text2 as the second text string you want to join into a single text string; and so on up to 30 arguments maximum.
Result is: a single text string that joins all the text strings supplied as arguments.
Example: You can use the CONCATENATE function to join text strings in an address database. For example, the following formula returns Anne M. Miller if cell A3 contains Anne, cell B3 contains M. and cell C3 contains Miller:
=CONCATENATE(A3," ",B3," ",C3)
Note that the second and fourth arguments in the above example are used to insert a single space between the first name and middle initial, and between the middle initial and last name.
Syntax: =TEXT(value, format_text) The TEXT function converts the numeric value to text and displays it with the format specified by format_text. The result appears to be a formatted number, but actually is text. Use one of the predefined or custom numeric formats to specify the format for the value. The format cannot contain an asterisk (*), nor can it be in the General format.
You Supply: value as a number or cell reference; and format_text as a number format in text form, from the Category box on the Number tab of the Format Cells dialog box (other than General format).
Result is: a value converted to text in the specified number format.
Example: The following formula returns $1,234.56:
=TEXT(1234.56,"$#,##0.00")
Syntax: =EXACT(text1,text2) The EXACT function compares the text1 and text2 arguments. If they are exactly the same, EXACT returns the logical TRUE; if they are not the same, EXACT returns FALSE. Upper- and lowercase text are considered to be different in the arguments.
You Supply: text1 as the first text string; and text2 as the second text string you want to compare.
Result is: TRUE if the arguments match exactly, or FALSE if they don't match.
Syntax: =FIND(find_text,within_text,start_num) Beginning at start_num, the FIND function searches the text specified by within_text to locate find_text. If find_text is found, the FIND function produces the character location where find_text starts. If start_num is out of limits or a match is not found, the #VALUE! error value is displayed. If start_num is not specified, it is assumed to be 1, which is the starting character. Unlike the SEARCH function, FIND is case-sensitive and doesn't allow wildcard characters.
You Supply: find_text as the text you want to find; within_text as the text containing the text you want to find; and (optional) start_num as the character position in which you want to start the search.
Result is: the number of the starting position of the found string.
Example: The following formula returns 4 if cell C5 con- tains X:
=FIND(C5,"123XYZ")
Syntax: =FIXED(number,decimals,no_commas) The FIXED function rounds the number to the specified decimals and displays it as text in fixed decimal format with commas. If you omit decimals, the number is rounded to two decimal places. If you specify a negative number of decimals, the function rounds the number to the left of the decimal point. When no_commas is TRUE, commas are removed from the result. If you omit no_commas, commas are displayed in the result.
You Supply: Number as the number you want to round and convert to text; (optional) decimals as the number of digits to the right of the decimal point; and (optional) no_commas as TRUE if you don't want to display commas in the result, or FALSE if you do want to display commas.
Result is: a number rounded to the specified number of decimal points, either with or without commas (as indicated by the no_commas argument).
Examples: The following formula returns 1,234.57:
=FIXED(1234.567)
The following formula returns 1200:
=FIXED(1234.567,-2,TRUE)
Syntax: =LEFT(text,num_chars) The LEFT function produces the leftmost number of characters from text. The value of num_chars must be greater than zero. If num_chars is omitted, it is assumed to be 1.
You Supply: text as the text string containing the character(s) you want to extract; and (optional) num_chars as the number of characters you want to extract.
Result is: the specified number of leftmost character(s) in the text string.
Example: The following formula returns 3rd if cell E20 contains 3rd Edition:
=LEFT(E20,3)
Syntax: =LOWER(text) The LOWER function changes all text in the argument to lowercase.
You Supply: text as the text string you want to convert to lowercase.
Result is: the text string with all letters in lowercase.
Example: The following formula returns part number bx154c when cell B7 contains Part Number BX154C:
=LOWER(B7)
Syntax: =MID(text,start_num,num_chars) The MID function produces characters from the specified text, beginning at the character in the start_num position and extending the specified num_chars.
You Supply: text as the text string containing the character(s) you want to extract; start_num as the position of the first character you want to extract (the first character in text is number 1); and num_chars as the number of characters you want to extract.
Result is: the specified number of characters from a text string, starting at the position you specify.
Example: The following formula returns BX512A:
=MID("Part number BX512A is not available",13,6)
Syntax: =PROPER(text) The PROPER function changes text in the argument to lowercase with initial capitals for each word.
You Supply: text as the text string you want to convert to proper text.
Result is: the text string with the first letter in each word capitalized.
Syntax: =REPT(text,number_times) The REPT function repeats the text for number_times. The value of number_times must be positive and nonzero. The maximum number of resulting characters is 255.
You Supply: text as the text you want to repeat; and number_times as the number of times you want to repeat text.
Result is: the text string repeated the number of times specified.
Example: The following formula returns #___#___#___ as the text string:
=REPT("#___",3)
Syntax: =REPLACE(old_text,start_num,num_chars, new_text) The REPLACE function replaces the characters in old_text with new_text, starting with the character at start_num and continuing for the specified num_chars. The first character in old_text is character 1. (See also the "Substitute Text" function.)
You Supply: old_text as the text string in which you want to replace characters; start_num as the first character position in old_text that you want to replace with new_text; num_chars as the number of characters in old_text that you want to replace; and new_text as the text that will replace characters in old_text.
Result is: the text string with old_text replaced by new_text.
Example: The following formula returns Year: 2000 as the text string:
=REPLACE("Year: 1999",7,4,"2000")
Syntax: =RIGHT(text,num_chars) The RIGHT function produces the rightmost number of characters from text. The value of num_chars must be greater than zero. If num_chars is omitted, it is assumed to be 1.
You Supply: text as the text string containing the character(s) you want to extract; and (optional) num_chars as the number of characters you want to extract.
Result is: the specified number of rightmost character(s) in the text string.
Example: The following formula returns IN as the text string:
=RIGHT("Indianapolis, IN",2)
Syntax: =SEARCH(find_text,within_text,start_num) The SEARCH
function begins at start_num in the specified within_text
argument, searches through it for find_text, and produces the character
number where find_text begins. The first character position
in within_text is 1. If start_num is omitted, it is assumed
to be 1. SEARCH ignores case differences. If find_text is not found
or if start_num is out of limits, #VALUE! is returned. The
wild card ? can be used in find_text to specify any single character at that
location within the text you want to find. The wild card * can be used in find_text
to specify any group of characters at that location within the text you want to find.
SEARCH is not case-sensitive (unlike the FIND function).
You Supply: find_text as the text you want to find; within_text
as the text containing the text you want to find; and (optional) start_num
as the character position in which you want to start the search.
Result is: the number of the starting position of the found string.
Example: The following formula returns 13:
=SEARCH("BX*","Part number BX512A is not available")
Syntax: =SUBSTITUTE(text,old_text,new_text, instance_num) The SUBSTITUTE function substitutes new_text for old_text within the specified text. If old_text occurs more than once, instance_num specifies which occurrence to replace. If instance_num is not specified, every occurrence of old_text is replaced. SUBSTITUTE is case-sensitive. If you want to replace specific text in a text string, use SUBSTITUTE. If you want to replace any text that occurs in a specific location in a text string, use the REPLACE function. (See also the "REPLACE" function.)
You Supply: text as the text in which you want to substitute characters; old_text as the text you want to replace; new_text as the text that will replace old_text; and (optional) instance_num as a number indicating which occurrence of old_text you want to replace.
Result is: the text string with old_text replaced by new_text at the specified occurrence (or all occurrences if instance_num is omitted).
Syntax: =T(value) The T function returns text when value is text, or double quotes (empty text) when value is not text.
You Supply: value as the value you want to test.
Result is: the text referred to by value, or empty text if value is not text.
Syntax: =TRIM(text) The TRIM function deletes all spaces from text so that only one space remains between words. This can be useful for cleaning text used in databases, or text imported to or exported from Excel.
You Supply: text as the text string from which you want excess spaces removed.
Result is: the text string with all spaces removed (except for one space between words).
Syntax: =UPPER(text) The UPPER function changes all text in the argument to uppercase.
You Supply: text as the text string you want to convert to uppercase.
Result is: the text string with all letters in uppercase.
Example: The following formula returns PART NUMBER BX154C when cell B7 contains Part Number BX154C:
=UPPER(B7)
©Copyright, Macmillan Computer Publishing. All rights reserved.