Excel 301 Function Definitions

Now

A date stamp indicating the date and time that a workbook is created can be very important in financial projections and reports. Excel provides the NOW for calculating the current systems date and time, with December 31, 1899, as the starting date. To insert the NOW function in a cell, click the Date & Time button in the Function Library group on the Formulas tab on the Ribbon, and select NOW on the menu that opens. A Function Arguments dialog box displays the message that the function takes no arguments. When you click OK in the dialog box, the current date and time are displayed in the selected cell.

Average

The Average function sums the numbers in the specified range and then divides the sum by the number of non-zero cells in the range.

COUNTIF

The COUNTIF function allows you to count the number of cells in range that satisfy a particular criteria. For example, you can use COUNTIF to count the number of employees with a particular pay code or the number of customers that purchase a particular product. When entering a formula with the COUNTIF function directly into a cell, you can use the COUNTIF(range, criteria) syntax.

DCOUNT

The DCOUNT function counts the number of entries in a range that meet a specified criteria.

MATCH

The MATCH function locates the relative position of a specified item in a range of cells. For example, you can use MATCH to locate the position of a customer entry in a table by inputting a known value from the row, such as the customer's phone number. A return value of 3 means it is the third entry in the range.

Max

The Max function finds the maximum, or largest, value in a range. (Editing group on home tab - down arrow on auto sum)

Min

The Min function finds the minimum, or smallest, value in a range.

SUMIF

The SUMIF function is a conditional sum that adds values only from those cells in a range that fit a specified criteria. SUMIF syntax =SUMIF(range, criteria, sum_range). Range is the range of cells to be filtered, criteria is the filtering condition, and the optional sum_range argument specifies the range of cells to total if different from the range argument.

VLOOKUP

The VLOOKUP function locates specific values in vertical tables in Excel, much like you would look up a phone number in a telephone book by locating a person's name, then reading across the row to find the phone number

IF

You may find the IF function useful when you want Excel to perform one action if a specified condition is true and a different action is the condition is false. The condition consists of a comparison operator such as "greater than" and two expressions. Expressions can include a cell reference, a number, text, a formula, or even another function. To insert the IF function in a cell, click the Logical button in the Function Library group on the Formulas tab on the Ribbon. Then, select IF in the Logical menu to open the Function Arguments dialog box, and enter the arguments for the function.