Variable average values or sums are to be calculated for a specified data area in a table. The evaluation always refers to the same area—it should not change even if new data columns are inserted.
Fixed cell references, defined by a prefixed dollar sign, do not work here since their values increase with every additional cell. However, all formula references are customized when inserting new columns. This behavior is acceptable in most cases. The ‘INDIRECT’ function provides an approach for this special case. This functional argument refers to a cell that contains a reference in the text form, for example ‘B3’. However, the argument can also directly specify the desired reference in the text form. Excel does not directly consider this textually defined reference as a reference and hence does not modify it when making changes in the table. You can apply any possible reference and use this function for other calculations. Example: Enter "=SUM (INDIRECT (“A2:C2”))" as the formula to add up the values from the first three columns. The usual reference is thus more or less covered with the ‘INDIRECT (‘‘)’ function.
Subscribe to:
Post Comments (Atom)
Posts
Post a Comment