Excel Formulas and Functions

Formulas in Excel

Formula Basics

• A formula in Excel is an expression that calculates a value based on values in other cells.
• Formulas can be used to perform arithmetic, comparison, and logical operations.
• Formulas can be entered into a cell using the = sign, followed by the formula.

Formula Types

• Arithmetic formulas: Perform mathematical operations such as addition, subtraction, multiplication, and division.
• Comparison formulas: Compare values in cells, such as =A1=B1 to check if the values in cells A1 and B1 are equal.
• Logical formulas: Use logical operators such as AND, OR, and NOT to evaluate conditions.
• Text formulas: Manipulate text strings, such as concatenating strings or extracting substrings.

Formula Operators

• Arithmetic operators:
  • + (addition)
  • - (subtraction)
  • * (multiplication)
  • / (division)
  • ^ (exponentiation)
• Comparison operators:
  • = (equal to)
  • <> (not equal to)
  • > (greater than)
  • < (less than)
  • >= (greater than or equal to)
  • <= (less than or equal to)
• Logical operators:
  • AND (logical and)
  • OR (logical or)
  • NOT (logical not)

Formula References

• Cell references: Refer to a specific cell or range of cells, such as A1 or A1:B2.
• Named ranges: Refer to a named range of cells, such as SalesData.
• Absolute references: Refer to a cell or range of cells using an absolute reference, such as $A$1.

Formula Errors

• #NAME?: The formula contains a reference to a named range that does not exist.
• #VALUE!: The formula contains a value that is not a valid number.
• #REF!: The formula contains a reference to a cell that does not exist.
• #DIV/0!: The formula attempts to divide by zero.
• #N/A: The formula contains a reference to a value that is not available.

Formulas in Excel

Formula Basics

• A formula in Excel calculates a value based on values in other cells.
• Formulas can be used to perform arithmetic, comparison, and logical operations.
• Formulas can be entered into a cell using the = sign, followed by the formula.

Formula Types

• Arithmetic formulas perform mathematical operations such as addition, subtraction, multiplication, and division.
• Comparison formulas compare values in cells, checking if they are equal, not equal, greater than, less than, etc.
• Logical formulas use logical operators such as AND, OR, and NOT to evaluate conditions.
• Text formulas manipulate text strings, allowing concatenation, extraction of substrings, and more.

Formula Operators

• Arithmetic operators include:
  • + for addition
  • - for subtraction
  • * for multiplication
  • / for division
  • ^ for exponentiation
• Comparison operators include:
  • = for equal to
  • `` for not equal to
  • > for greater than
  • = for greater than or equal to
  • < for less than