Definition and Use of Formula in Excel Spreadsheets

Formula Examples in Excel
Formula Examples in Excel. Ted French

Formulas in spreadsheet programs such as Excel and Google Spreadsheets are used to perform calculations or other actions on data entered into the formula and/or stored in program files.

They can range from basic mathematical operations, such as addition and subtraction, to complex engineering and statistical calculations. 

Formulas are great for working out “what if” scenarios that compare calculations based on changing data.

Once the formula is entered, you need only change the amounts to be calculated. You don’t have to keep entering “plus this" or “minus that” like you do with a regular calculator.

Formulas Start With the = Sign

In programs such as Excel, Open Office Calc, and Google Spreadsheets, formulas begin with an equal ( = ) sign and, for the most part, they are entered into to the worksheet cell(s) where we want the results or answer to appear.

For example, if the formula =5 + 4 - 6  was entered into cell A1, the value 3 would appear in that location.

Click on A1 with the mouse pointer, however, and the formula is displayed in the formula bar above the worksheet.

Formula Breakdown

A formula can also contain any or all of the following:

Values

Values in formulas are not just restricted to numbers but can also include:

  • dates
  • text - words - often surrounded by quotation marks ( " ")

Formula Constants

A constant - as the name suggests - is a value that does not change. Nor is it calculated. Although constants can be well-known ones like Pi ( Π ) -  the ratio of a circle's circumference to its diameter - they can also be any value - such as a tax rate or a specific date - that changes infrequently.

Cell References in Formulas

Cell references - such as A1 or H34 - indicate the location of data in a worksheet or workbook. Rather than enter data directly into a formula, it is usually better to enter the data into worksheet cells and then enter the cell references to the location of the data into the formula.

The advantages of this are that:

  • if you later change your data the formula automatically updates to show the new result;
  • in certain instances, using cell references makes it possible to copy formulas from one location to another in a worksheet.

To simplify entering multiple contiguous cell references into a formula, they can be entered as a range that just indicates the start and end points. For example, the references, A1, A2, A3 can be written as the range A1:A3.

To simplify things even further, frequently used ranges can be given a name that can be entered into formulas.

Functions: Built-in Formulas

Spreadsheet programs also contain a number of built-in formulas called functions.

Functions make it easier to carry out:

    Formula Operators

    An arithmetic or mathematical operator is the symbol or sign that represents an arithmetic operation in an Excel formula.

    Operators specify the type of calculation being carried out by the formula.

    Types of operators

    The  different types of calculation operators that can be used in formulas include:

    • arithmetic - used for basic arithmetic such as addition and subtraction
    • comparison
    • text concatenation

    Arithmetic Operators

    Some of the arithmetic operators - such as the ones for addition and subtraction - are the same as those used in hand-written formulas, while the ones for multiplication, division, and for exponents are different.

    All of the arithmetic operators are:

    • Subtraction - minus sign (-)
    • Addition - plus sign ( + )
    • Division - forward slash ( / )
    • Multiplication - asterisk ( * )
    • Exponentiation - caret ( ^ )

      If more than one operator is used in a formula, there is a specific order of operations that Excel follows in deciding which operation occurs first.

      Comparison Operators

      A comparison operator, as the name suggests, carries out a comparison between two values in the formula and the result of that comparison can only ever be either TRUE or FALSE.

      There are six comparison operators:

      • Equals ( = 
      • Less than ( < 
      • Less than or equal to ( < = 
      • Greater than ( > 
      • Greater than or equal to ( > = 
      • Not equal to ( < > )

      The AND and OR functions are examples of formulas that use comparison operators.

      Concatenation Operator

      Concatenation means to join things together and the concatenation operator is the ampersand " & " and it can be used for joining multiple ranges of data in a formula.

      An example of this would be: 

      {=INDEX(D6:F11, MATCH (D3 & E3, D6:D11 & E6:E11, 0), 3)}

      where the concatenation operator is used to combine multiple data ranges in a lookup formula using Excel's INDEX and MATCH functions.

      Format
      mla apa chicago
      Your Citation
      French, Ted. "Definition and Use of Formula in Excel Spreadsheets." ThoughtCo, May. 18, 2017, thoughtco.com/description-of-formula-3123848. French, Ted. (2017, May 18). Definition and Use of Formula in Excel Spreadsheets. Retrieved from https://www.thoughtco.com/description-of-formula-3123848 French, Ted. "Definition and Use of Formula in Excel Spreadsheets." ThoughtCo. https://www.thoughtco.com/description-of-formula-3123848 (accessed November 18, 2017).