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.
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. |
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
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
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
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.
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. |
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
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. |
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
Excel includes many statistical functions for statistical calculations such as these:
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. |
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
18.119.121.101