Cell reference

from Wikipedia, the free encyclopedia

A cell reference describes the assignment of formula variables to specific values ​​in the corresponding input fields ( cells ) in spreadsheets .

In spreadsheets such as Microsoft Excel or Calc from OpenOffice.org , formulas are entered in cells to perform calculations. These formulas are calculated automatically and the result is displayed in the cell. Each time the data is changed, it is updated automatically.

The cell references are necessary for this: The operand does not use the actual values ​​that have already been entered as inputs in other cells, but a reference to the respective cell or cell area.

Examples:

  • The formula =B2-B3calculates the difference between the values ​​in cells B2 and B3.
  • The formula =SUMME(B2:D2)adds up all the numbers from B2 to D2.

These are relative references. This means that the references z. B. is adjusted accordingly when copying the formula. If the formula is =B2-B3z. B. in B4, that is, B4=B2-B3and this formula is copied into cell C4, then the analog calculation results there, namely the difference between the contents of the two cells above it, namely C4=C2-C3. In the same way, E2=SUMME(B2:D2)copied into field E3 results in the formula E3=SUMME(B3:D3).

This effect of relative references is often desired. Occasionally, however, the same reference should be used in the copied formula as in the original. Then you need an absolute reference.

B5=B4*$B$1When copied to C5, the formula yields e.g. B. C5=C4*$B$1. The relative reference (here B4 ) is adjusted, the absolute reference (here $ B $ 1 ) always remains the same, regardless of where the formula is copied.

If the content of a reference is always to be used regardless of deleting or moving the cells above or next to it, then you can use B. in Excel the function =Indirekt("A1").

Web links