Example of a Formula Range
In the example below we see the formula range (C1:C5). The definition is determined by the minimum value (C1) and the maximum value (C5).
The formula =SUM(A1:B1) persists within the full range. The cell references in this formula are relative to the position of the formula within the range: in cell C1 we see the calculation of the sum of A1 and B1, but in cell C2 we see the calculation of the sum of A2 and B2 and in cell C3 we see the calculation of the sum of A3 and B3, and so on.
When you add new values to the columns (or rows) at the end of a formula range, the formula will expand automatically.
In the image below, we insert the number 42 in column A, cell A6 and the number 4 in column B, cell B6. As soon as we hit enter, the SUM of these two values is automatically calculated in cell C6; the formula range expanded!
Problems with Formula Ranges
Several issues can occur in formula ranges, such as references to empty cells, which is either meaningless or a mistake. References to merged cells can also be risky, because a merged cell has different reference possibilities (cell names), but only one is correct. Another issue occurs when a formula range is interrupted, for example, by a numeric value. Because the cells in the range will only display the result of the formula, it is hard to spot an interruption.
PerfectXL detects different types of problems with ranges.