When working with formulas in Microsoft Excel, users have to operate with references to other cells located in the document. But not every user knows that these links come in two types: absolute and relative. Let's find out how they differ from each other and how to create a link of the desired type.
Example of a relative link
Let's show how this works with an example. Let's take a table that contains the quantity and price of various products. We need to calculate the cost.
This is done by simply multiplying the quantity (column B) by the price (column C). For example, for the first product name the formula will look like this “=B2*C2”. We enter it into the corresponding cell of the table.
Now, in order not to manually enter formulas for the cells located below, we simply copy this formula to the entire column. We stand on the lower right edge of the cell with the formula, click the left mouse button, and while holding the button, drag the mouse down. This way, the formula will be copied to other table cells.
But, as we see, the formula in the bottom cell no longer looks like “=B2*C2”, but “=B3*C3”. Accordingly, the formulas below have also changed. This is the property of changes during copying that relative links have.
Assignments for § 3.2. Organizing calculations in spreadsheets -
110. Cell B2 contains a formula with relative references. 1) Write down the result of copying the formula from cell B2 to the cells of the selected range.
2) Imagine the table from point 1 in the value display mode, if the value 10 is entered in cell C2, and zeros are entered in the cells of the range D1:D3.
111. Cell B2 contains a formula with relative and absolute references.
1) Write down the result of copying the formula from cell B2 to the cells of the selected range.
2) Imagine the table from point 1 in the mode of displaying values, if the following numbers are entered in the cells of the range A4:B5.
112. Cell A2 contains a formula with mixed references.
1) Write down the result of copying the formula from cell A2 to the cells of the selected range.
2) Imagine the table from point 1 in the mode of displaying values, if the following numbers are entered in the cells of the range A1:D1.
113. A fragment of a spreadsheet contains numbers and formulas.
Write down the results of copying formulas from cells Cl, Dl, E1 and F1 into cells of the ranges C2:СЗ, D2:D3, Е2:ЭЗ, F2:F3, respectively. What values will be in the specified ranges after copying?
Imagine the table view in value display mode. 114. Task 114. Establish a correspondence between the names of functions and the actions performed as a result of their execution.
SUM - Sums the arguments MAX - Returns the largest value AVERAGE - Returns the arithmetic mean of its arguments COUNT - In a specified range, counts the number of cells containing numbers COUNTIF - In a specified range, counts the number of non-empty cells that satisfy the condition MIN - Returns the smallest value
115. Spreadsheet fragment contains numbers.
What value will be in cell C4 if you enter the following formula into it?
116. Write down: 1) in cell D3 such a formula so that, by copying it to cells D4:D6, correct calculations can be carried out there;
2) in cell D7 the formula for finding the total amount of expenses.
Value display mode:
117. Write in cells B10:C12 the formulas for calculating the average, maximum and minimum temperatures during the day and evening in the first seven days of April.
Value display mode:
118. In a spreadsheet, the value of the formula =SUM(D2:D3) is 6, and the value of the formula =AVERAGE(D2:D4) is 3. What is the value of cell D4?
119. A fragment of a spreadsheet contains numbers and formulas. Determine the values in cells C2 and C3. What will these values become if the value of cell A1 is deleted?
120. A fragment of a spreadsheet in formula display mode is given. What values will be in the cells of the ranges C2:NW, D2:D3 if formulas from cells Cl, D1 are copied into them, respectively?
121. A fragment of a spreadsheet in formula display mode is given. Cells A1, B1 contain some values.
After the contents of cell B2 were copied to cell V3, the table fragment in the value display mode began to look like this:
122. Write a conditional function corresponding to the block diagram.
123. A fragment of a spreadsheet in formula display mode is given.
1) Enter into the cells of the range B1:B9 the values that will appear in the spreadsheet after copying the formula from cell B1 to the range B2:B7.
2) Let A1:A7 contain the coordinates of points lying on the number line. What is calculated in cells B8 and B9 in this case?
B8 - counts the number of points that belong to the segment [5;10] B9 - on the contrary, counts the points that do not belong to the segment [5;10]
124. The results of the regional programming Olympiad were entered into the spreadsheet:
Error in relative link
But not in all cases we need relative links. For example, in the same table we need to calculate the share of the cost of each item of goods from the total amount. This is done by dividing the cost by the total amount. For example, to calculate the specific gravity of potatoes, we divide its cost (D2) by the total amount (D7). We get the following formula: “=D2/D7”.
If we try to copy the formula to other lines in the same way as the previous time, we will get a completely unsatisfactory result. As you can see, already in the second row of the table the formula looks like “=D3/D8”, that is, not only the link to the cell with the row sum has moved, but also the link to the cell responsible for the total total.
D8 is a completely empty cell, which is why the formula throws an error. Accordingly, the formula in the line below will refer to cell D9, etc. We need that when copying, a link to cell D7, where the total is located, is constantly saved, and absolute links have this property.
Possible errors when working with relative links
Of course, thanks to relative links, many calculations in Excel are significantly simplified. However, they do not always help solve the problem.
Let's say we need to calculate the share of each item in total sales.
- We go to the first cell of the column for calculations, where we write the formula: =D2/D13.
- Press Enter to get the result. After we copy the formula to the remaining cells of the column, instead of the results we will see the following error: #DIV/0!.
The fact is that due to the fact that all cell references in the formula that we copied are relative, the coordinates in subsequent cells have shifted. Those. for cell E3 the formula is: =D3/D14. But, as we see, cell D14 is empty, which is why the program generates an error informing that it is impossible to divide by 0.
Therefore, we must write the formula in such a way that the coordinates of the cell with the total (D13) remain unchanged when copied. Absolute links will help us with this.
Creating an absolute link
Thus, for our example, the divisor should be a relative reference, and change in each row of the table, and the dividend should be an absolute reference, which constantly refers to one cell.
Users will not have any problems creating relative links, since all links in Microsoft Excel are relative by default. But if you need to make an absolute link, you will have to use one trick.
After the formula is entered, simply place a dollar sign in the cell, or in the formula bar, in front of the column and row coordinates of the cell to which you want to make an absolute reference. You can also press the F7 function key immediately after entering the address, and dollar signs in front of the row and column coordinates will appear automatically. The formula in the topmost cell will look like this: “=D2/$D$7”.
Copy the formula down the column. As you can see, this time everything worked out. The cells contain correct values. For example, in the second row of the table the formula looks like “=D3/$D$7”, that is, the divisor has changed, but the dividend remains unchanged.
Relative cell reference in Excel
By default (standard), all links in Excel are relative. They look like this: =A2 or =B2 (number+letter only):
If we decide to copy this formula from line 2 to line 3, the addresses in the formula parameters change automatically:
A relative reference is convenient if you want to duplicate the same type of calculation across several columns.
To use relative cells, you need to perform a simple sequence of actions:
- Select the cell we need.
- Press Ctrl+C.
- Select the cell into which you want to insert the relative formula.
- Press Ctrl+V.
Helpful advice! If you have the same type of calculation, you can use a simple “life hack”. Select the cell, then move the mouse cursor to the square located in the lower right corner. A black cross will appear. After that, we simply “pull” the formula down. The system will automatically copy all values. This tool is called an autofill marker.
Or even simpler: select the range of cells in which you need to enter formulas so that the active cell is on the formula and press the hotkey combination CTRL + D.
Often users only need to change a row or column reference and leave part of the formula unchanged. This is easy to do, because in Excel there is such a thing as a “mixed link”.
Mixed links
In addition to the typical absolute and relative links, there are so-called mixed links. In them, one of the components changes, and the second is fixed. For example, for a mixed link $D7, the row changes but the column is fixed. For link D$7, on the contrary, the column changes, but the row has an absolute value.
As you can see, when working with formulas in Microsoft Excel, you have to work with both relative and absolute references to perform various tasks. In some cases mixed references are also used. Therefore, even an average level user must clearly understand the difference between them and be able to use these tools.
We are glad that we were able to help you solve the problem. Add the Lumpics.ru website to your bookmarks and we will be useful to you. Thank the author and share the article on social networks.
Describe what didn't work for you. Our specialists will try to answer as quickly as possible.
Lesson summary Organization of calculations. Relative, absolute and mixed references grade 9
Lesson summary Organization of calculations. Relative, absolute and mixed references (9th grade, lesson 51, textbook by L.L. Bosov).
Planned educational results: - subject - the presence of ideas about the organization of calculations in spreadsheets, about relative, absolute and mixed references; - meta-subject - general educational and general cultural skills in working with information; skills in determining the conditions and possibilities for using a software tool to solve typical problems; — personal – an idea of the areas of application of electronic tables in various fields of human activity.
The educational tasks to be solved: 1) understanding the essence of relative, absolute and relational references; 2) consideration of methods for organizing calculations using links.
Basic concepts studied in the lesson: - spreadsheets; — calculation; - formula; - link; - relative link; - absolute link; - mixed link.
ICT tools used in the lesson: - teacher’s personal computer (PC), multimedia projector, screen; — Students’ PCs.
Electronic educational resources - presentation “Organization of calculations in spreadsheets (part 1).” — resources of federal educational portals: 1) interactive test “Logical formulas in Spreadsheets.”
Features of presenting the content of the lesson topic
1. Organizational moment (1 minute) Greeting students, communicating the topic and goals of the lesson.
2. Repetition (5 minutes) 1) checking the studied material according to questions (1-15) to §5.1; 2) visual check of homework completion in RT No. 206, 207, 208; 3) consideration of tasks that caused difficulties in completing homework.
3. Study of new material (20 minutes) New material is presented accompanied by the presentation “Organization of calculations in spreadsheets (part 1).”
1 slide - title of the presentation;
Slide 2 - keywords; - spreadsheets - calculation - formula - reference - relative reference - absolute reference - mixed reference
Slide 3 - types of links (diagram); The main purpose of spreadsheets is to organize all kinds of calculations. We already know that: - calculation is a process of calculation using formulas; — the formula begins with an equal sign and can include operator signs, numbers, references, and built-in functions. The link points to the cell or range of cells that contains the data you want to use in the formula. Links allow you to: - use data located in different parts of the spreadsheet in one formula; — use the value of one cell in several formulas. There are two main types of links: 1) relative - depending on the position of the formula; 2) absolute - independent of the position of the formula. 3) mixed
Slide 4 - relative links; A relative link present in a formula fixes the location of the cell with data relative to the cell in which the formula is written. When you change the position of the cell containing the formula, the reference also changes. When copying a formula along a column and along a row, the relative link is automatically adjusted as follows: - a shift by one column leads to a change in one letter in the column name in the link; — a shift by one line results in a change in the line number in the reference by one.
Slide 5 - example 1 (with solution); Example 1. Let's calculate the estimated population of the city in the next 5 years, if in the current year it is 40,000 people and increases annually by 5%.
Slide 6 - absolute links; An absolute reference in a formula always refers to a cell located in a specific (fixed) location. In an absolute reference, each letter and number is preceded by a $ sign, for example $A$1. When you change the position of a cell containing a formula, the absolute reference does not change. When you copy a formula along rows and along columns, the absolute reference is not adjusted.
Slide 7 - example 2 (with solution); Example 2. A certain citizen opens a bank account in the amount of 10,000 rubles. He was informed that every month the deposit amount would increase by 1.2%. In order to find out the possible amount and increment of the deposit amount after 1, 2,..., 6 months, the citizen carried out the following calculations.
Slide 8 - mixed links; A mixed reference contains either an absolutely addressable column and a relatively addressable row ($A1), or a relatively addressable column and an absolutely addressable row (A$1). When you copy or fill a formula along rows and along columns, the relative part of the link is automatically adjusted, but the absolute part is not adjusted.
Slide 9 - example 3 (with solution); Example 3. You need to create a table for adding the numbers of the first ten, that is, fill out the table.
Slide 10 - relative, absolute and mixed links; Examples of relative, absolute and mixed links.
Slide 11 is the most important. A relative reference fixes the location of the data cell relative to the cell in which the formula is written. When you change the position of the cell containing the formula, the reference also changes. An absolute reference always refers to a cell located at a specific location. When you change the position of a cell containing a formula, the absolute reference does not change. A mixed reference contains either an absolutely addressable column and a relatively addressable row, or a relatively addressable column and an absolutely addressable row. When you change the position of a cell containing a formula, the relative part of the address changes, but the absolute part of the address does not change.
Questions and tasks 12 slide – questions and tasks; Questions 1-11 to paragraph 5.2. If time permits, complete tasks 213 in the workbook.
4. Practical part (15 minutes) In the practical part of the lesson, tasks 2-4 from the tasks for practical work (pp. 32-33) of the textbook are completed.
5. Summing up the lesson. Homework message. Grading (4 minutes) Slide 13 - supporting summary; Slide 14 - D/z.
Homework. §5.2 (1), questions No. 1-11 to the paragraph; RT: No. 213; Additional task: practice task 6 or 7 (pages 33-34)
All material for the lesson is in the archive.
Download (195 KB, rar): Lesson summary Organization of calculations. Relative, absolute and mixed references grade 9
The archive includes: - notes, - answers and solutions to tasks in the textbook and workbook, - presentation “Organization of calculations in spreadsheets (part 1).”
