excel what is a mixed address

Cells that have both relative and absolute references.

Features: Relative address references are used in formulas, and the reference addresses (values) change with location during the formula copying process; For example, if "=A1" is copied to C2 in C1, the formula will automatically become "=A2", and if it is copied to D1, the formula will become "=B1", which reflects the relative reference in the formula copying - horizontal copy variable column number, vertical copy variable line number.

Features: Absolute address references are used in formulas, and the reference addresses (values) remain unchanged during formula copying. For example, in C1, "=$a$1" is copied to any position as "=$a$1".

Features: Use mixed references in formulas, $a 1, $a 2 only when copying the formula vertically, the line number, such as "=$a 1" in C1 is copied to C2, and the formula is changed to.

a2", and copying to d1 is still "=$a 1", which means that a mixed reference of the form $a1 and $a 2 "the vertical line number is unchanged". B$1 and B$2 are just the opposite, and in the formula copying, "the horizontal column number does not change vertically".

Familiarity with the characteristics of various references copied in formulas will effectively improve the accuracy of formulas.

The most commonly used one, for example, when calculating the total score of students, just calculate the total score of the first row, and then move the mouse to the fill handle and drag it down to complete the copy of the formula, then the reference of the cell is a relative reference. The cell in which the result is stored changes, and so does the cell it references. It is in the form of column markings plus line numbers.

Such as d5, a3, e8, etc.

2.Absolute address.

In a cell address reference, there are both absolute address references and relative cell address references.

For example, if zhi is in the formula, the address of the dao will not change with the formula copying, for example, if c1=$a$1+b1, it will become c2=$a$1+b2 when copying downward;

Relative address format such as a1, this format is relative, it can be changed with the formula copy, for example, in c1=a1+b1, when copying down, it becomes c2=a2+b2;

A mixed address format such as $a1 or a$1 is where the former column is absolute and the rows are relative, and the latter is the column relative to the rows absolute, e.g. c1=a$1+b1, c2=a$1+b2 if you copy downward, and d1=b$1+c1 if you copy it to the right.

Think of it this way, in two steps:

Step 1: The formula of C3 is placed in D3, so that since there is no $ in front of B in B3, that is, there is no absolute reference, the formula in D3 will become =$a$1*C3, that is, B3 is offset by one cell to the right.

Step 2: Pull down the formula of d3, then only the line will change, so c3 becomes c4, that is, it becomes $a$1*c4

To put it simply, if you drag horizontally, only the column labels, that is, the letters, will change, and if you drag them down, only the number of rows will increase.

So A$1*$b 3 is understandable, because B is locked with $ in front of it, and B will not move when it moves sideways, but A is not locked in front of A, so A will become B. That is, when you put it on d3, it will become b$1:$b 3, and when you pull it down to d4, it will become b$1:

b4, that is, the column does not move, and the line number changes.

To put it simply, when there is no lock, the horizontal column will move, but it will not move; Vertically, that is, the formula is pulled up and down, the rows change, and the columns do not move.

Cell C3 holds the formula "$a$1*B3", which is equivalent to an absolute reference to cell A1 multiplied by one cell to the left of C3; After copied to D4, it is still represented as an absolute reference to the A1 cell multiplied by one cell to the left of D4 (C4).

The formula "A$1*$b 3" is stored in cell C3, which means that C3 is equal to the cells of the relative column A (two columns to the left of column C), the absolute first row, and the absolute column B, and the third row relative (the same column as C3). After copying to D4, it still means that D4 is equal to multiplying the cells of the absolute first row of column B (the two columns to the left of column D) and the cells of the absolute column B relative to the fourth row (the same column as D4), that is, B$1*$b 4