Kioskea
Recherche

Spreadsheet - Formulas

March 2015

Introduction to Formulas

The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of a cell.

A formula is used to represent an expression, which, one entered into the cell, allows the spreadsheet to perform automatic calculations based on values in other cells and to display the result.

Formulas may also contain references to other cells, expressions, or functions.

Entering a Formula

Entering a formula is done by selecting a cell and by using the formula bar.

Microsoft Excel formula bar

To enter a formula, start by entering the equal sign (=) and then enter the cell references of the cells to be used and the operators and/or functions.

It is also possible to click on the button Formula on the formula bar. The example below demonstrates the use of a formula in cell C15 to calculate the sum of cells D11 and D13.

Entering a formula in Microsoft Excel

When the Enter is pushed, the spreadsheet calculates the value of cell C15 and displays the result:

Interpreting a formula in Microsoft Excel

Practical Example

In the example below, the use of formulas to automatically calculate the price with tax included (Tax included) of a list of articles for which the "Before Tax" (BT) price is given, and then to calculate the total price of these articles is shown.

Tax Included price calculation for an article given the pre-tax value and the VAT

To obtain the Tax Included price for article 34FR453, add the value of the tax to the pre-tax value, meaning the pre-tax value multiplied by the VAT (19.6% in this case):

Amount_TI = Amount_BT + Amount_BT*VAT

In the above example,

  • the TI amount is located in E6,
  • the BT amount is located in D6,
  • the VAT amount is located in D11,
which gives the following formula:
= D6 + D6*D11

To calculate the TI amount for article 34FR453, first select the cell corresponding to this calculation (E6) and then start entering the formula (either by starting with the equal sign or by clicking the formula button).

The spreadsheet is therefore in the formula entering mode, and:

  • either enter the formula directly with the keyboard
  • or build the formula by selecting the cells one at a time

In the second example, clicking on cell D6 will automatically add its name to the formula bar. Another click on this cell will display D6+D6 because addition is the default operation in the spreadsheet. Push the * button to choose multiplication, and then select cell D11.

To finish entering the formula, push the Enter button on the keyboard or use the Enter button on the formula bar. The spreadsheet will display the result of the calculation in cell E6:

Tax Included price calculation for an article given the pre-tax value and the VAT

Copying Formulas

Imagine that we want to copy this formula and use it for the other articles. Select the formula in (cell E6), copy it, and select the block of cells from E7 to E9 (written E7:E9) and paste the previously copied formula. Here is the result:

Copying a formula in Excel

Note that the copied formula does not produce the correct amounts:

Errors when copying a formula in Excel

In effect, when clicking on one of these cells, it can be noticed that the spreasheet references the correct price before tax, but that the reference to the cell with the VAT value is wrong (D13 instead of D11).

This is because the previously entered references were relative references. The spreadsheet therefore performs an automatic offset on cell references when copying the formula. Thus, to avoid the spreadsheet offsetting the cell with the VAT value, use an absolute reference ($D$11). Let's redo the formula:

Using an absolute reference in a formula

The spreadsheet now gives a coherent result:

Formula result with an absolute reference

Using Functions in Formulas

Functions may also be used in formulas, thus allowing advanced calculations. Most spreadsheets offer a large number of integrated functions (Excel has more than 400).

Let's redo the above example. To automatically calculate the pre-tax total and the TI total of orders, use the Sum() function. To do this, select the cell in which to display the sum and enter the formula (starting with the = sign) using the Sum() function. There are many ways of entering the Sum() function:

  • by manually entering it in the formula bar
  • by using the function button that allows the function to be chosen from a list:

    Using the function button

  • by using the sum button directly

    Using the sum button

Whatever method chosen, the two arguments of the Sum() function must be specified, which means specifying the cells for which the sum is to be calculated. There are two further possibilities for the user:
  • enter the references of the selected cells on the keyboard
  • select the cells or the blocks of cells to be used

Use of the sum function

Here is the result calculated by the spreadsheet:

Result of the sum function

For unlimited offline reading, you can download this article for free in PDF format:
Spreadsheet-formulas.pdf

See also



Tabellenkalkulation - Die Formeln
Tabellenkalkulation - Die Formeln
Tableur - Les formules
Tableur - Les formules
Foglio elettronico - Le formule
Foglio elettronico - Le formule
Folha de cálculo - As fórmulas
Folha de cálculo - As fórmulas
This document entitled « Spreadsheet - Formulas » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.