Spreadsheet function categories

When you need to analyze whether values in a list meet a specific condition, you can use a database spreadsheet function. For example, in a list that contains sales information, you can count all the rows or records in which the sales are greater than 1,000 but less than 2,500.

Some database and list management spreadsheet functions have names that begin with the letter "D." These functions, also known as Dfunctions, have three arguments— database, field, and criteria.

• The database argument is the range that contains your list. You must include the row that contains the column labels in the range.
• The field argument is the label for the column you want to summarize.
• The criteria argument is the range that contains a condition you specify.

With date and time functions, you can analyze and work with date and time values in formulas. For example, if you need to use the current date in a formula, use the TODAY spreadsheet function, which returns the current date based on your computer's system clock.

Financial functions perform common business calculations, such as determining the payment for a loan, the future value or net present value of an investment, and the values of bonds or coupons.

Common arguments for the financial functions include:

• Future value (fv)— the value of the investment or loan after all payments have been made.
• Number of periods (nper)— the total number of payments or periods of an investment.
• Payment (pmt)— the amount paid periodically to an investment or loan.
• Present value (pv)— the value of an investment or loan at the beginning of the investment period. For example, the present value of a loan is the principal amount that is borrowed.
• Rate (rate)— the interest rate or discount rate for a loan or investment.
• Type (type)— the interval at which payments are made during the payment period, such as at the beginning of a month or the end of the month.

Use an information spreadsheet function to determine the type of data stored within a cell. The information functions include a group of spreadsheet functions known as the IS functions that return TRUE if the cell meets a condition. For example, if a cell contains a number, the ISNUMBER spreadsheet function returns TRUE. If you need to determine whether blank cells exist in a range of cells, you can use the COUNTBLANK spreadsheet function to count the number of blank cells in a range of cells, or you can use the ISBLANK spreadsheet function to determine whether one cell in the range is blank.

You can use the logical functions either to see whether a condition is true or false or to check for multiple conditions. For example, you can use the IF function to determine whether a condition is true or false: One value is returned if the condition is true, and a different value is returned if the condition is false.

When you need to find values in lists or tables or when you need to find the reference of a cell, you can use the lookup and reference spreadsheet functions. For example, to find a value in a table by matching a value in the first column of a table, use the VLOOKUP spreadsheet function. To determine the position of a value in a list, use the MATCH spreadsheet function.

With math and trigonometry functions, you can perform simple calculations, such as rounding a number or calculating the total value for a range of cells, or complex calculations, such as calculating the total value for a range of cells that meet a condition in another range of cells.

Statistical spreadsheet functions perform statistical analysis on ranges of data. For example, a statistical spreadsheet function can provide statistical information about a straight line plotted through a group of values, such as the slope of the line and the y-intercept, or about the actual points that make up the straight line.

With text functions, you can manipulate text strings in formulas. For example, you can change the case or determine the length of a text string. You can also join, or concatenate, a date to a text string. The following formula is an example of how you can use the TODAY function with the TEXT function to create a message that contains the current date and formats the date in the "dd-mmm-yyyy" number format.

="Budget report as of "&TEXT(TODAY(),"dd-mmm-yyyy")