EXCEL with formula cell auto filling, excel Cell

EXCEL with formula cell auto filling, excel Cell

Have you ever encountered this problem:

When EXCEL is used, especially when formulas are used for calculation, some variables do not need to be changed when cells are filled down, but some variables need to be changed?

If you encounter such a problem, there is a trick: first, distinguish between changing variables and unchanged variables, that is, when filling down, the variable that changes the data zone does not need to move down with the cell. That is to say, the variable points to a fixed data zone. Otherwise, the variable is changed.

1. Example: if the data column B and column C exist and cell D2 is the average of Column B, the product of each row in column D and column C must be calculated and put in column E, how do I write the auto-downward filling formula for column E?

Analysis: Here, B and C are variable changes. Different rows take different values, while D2 cells are unchanged variables. First, D2 = ararage (B1: Bn) is calculated ), then calculate the value of column E.

Formula for column E: E2 = C2 * $ D $2

Here, the row and column are both added with the $ symbol, indicating absolute reference. During the filling process, the column C is not auto-incrementing, and the column C is auto-incrementing. $ D $2 (D2 cell) not changed.

2. to calculate that D is equal to the multiplication of B and C in each row, it should be written:

D2 = B2 * C2

And drag down the fill.