Formulas

• ·         A formula always starts with an equal sign (=).
• ·         A formula is an expression that calculates and returns a value.
• o   Function – a named operation that replaces the arithmetic expression in a formula
• o   Reference – a cell or a range of cells on a worksheet with value(s)
• o   Constant – a number or value entered directly into a formula
• o   Operator – a special character that specifies the type of calculation.
• An arithmetic operator can perform basic mathematical operations, combine numbers, or produce numerical results.
• o   The arithmetic operators are +, -, *, /, %, and ^.

  The ^ operator raises a number to a power
• The comparison operators are used to compare values; the result of a comparison is either TRUE or FALSE.
• The comparison operators are =, >, <, >= (greater than or equal to), <= (less than or equal to), and <> (not equal to).
• The text concatenation operator (&) connects two (2) text values to produce one (1) single text value
• A reference operator combines ranges of cells for calculations
• : (colon) – range operator
• , (comma) – union operator
• (space) – intersection operator; produces one (1) reference to cells common to the two (2) references
• Operator precedence specifies the order of operations in a formula that contains more than one (1) operator
• o   SUM adds the values in selected cells.
• o   MIN returns the smallest number in a range of cells.
• o   MAX returns the largest number in a range of cells.
• o   COUNT counts the number of cells in a range the contain numbers.
• o   AVERAGE returns the average of numbers in a range of cells.
• o   MEDIAN returns the middle number in a sorted list of numbers. If the total number of values is even, then the average of the two (2) values in the middle pair is the median.
• TODAY() inserts a date that updates when the worksheet is recalculated or opened.
• o   NOW() inserts the date and time that update when the worksheet is recalculated or opened.
• o   IF returns one (1) value if a condition is true and another value if it's false. Syntax: IF(logical_test, [value_if_true], [value_if_false])
• o   MODE.SNGL returns the value that occurred most frequently.
• o   VLOOKUP searches a value in the leftmost column of a table, and then returns a value in the same row from a specified column.
• Syntax: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• o   SUMIF adds the values in cells specified by a given condition or criteria.
• o   SUMIFS adds the values in cells specified by a given set of conditions or criteria.
• o   COUNTIF counts the number of cells within a range that meet the given condition. Syntax: COUNTIF(range, criteria)
• COUNTIFS counts the number of cells specified by a given set of conditions or criteria
• o   AVERAGEIFS finds the average for the cells specified by a given set of conditions or criteria.
• o   MINIFS returns the minimum value among cells specified by a given set of conditions or criteria.
• o   MAXIFS returns the maximum value among cells specified by a given set of conditions or criteria.
•    Select Format Cells or press Ctrl + 1.
• ·         To insert the current date, press Ctrl + ; (semicolon).
• ·         To insert the current time, press Ctrl + Shift + ; (semicolon).
• ·         To insert a date that auto-updates each time the worksheet is reopened, type =TODAY() in the formula bar.
• ·         To insert date and time that auto-update each time the worksheet is reopened, type =NOW()

  in the formula bar.