Formula Error

  • Thread starter Thread starter tepermanj
  • Start date Start date
T

tepermanj

If I insert a formula, e.g. =SUM(C1:C5). Then I delete row 3. Excel will
redesignate the cells, C4 will become C3 and C5 will become C4. The formula
will also be updated accordingly to =SUM(C1:C4).
However, if I have formulas in a column, e.g. A2 has the formula =SUM(A1+1),
A3 has the formula =SUM(A2+1), etc..., the above does not happen. In other
words, the cells are redesignated, A4 will become A3, A5 will become A4,
etc... Unfortunately, the formulas are not reconfigured as they are in the
first example. Instead, the following error is received:
=SUM(#REF!+1)
Why does excel update formulas in certain instances and not in others?
Thanks.
 
And even if these formulae auto adjusted, you would have a problem. If A2
says A1+1, you delete row one, then the original A2, now A1, would read A1+1,
which is a circular reference On the other hande if rA2 has this formula,
and you insert a row 2, would you also want it to auto adjust? I think not,
and that is the dilemma of the developer. Who to please, and when! The sum
formula handles a range, which can skrink and grow, without involving the
formula cell. while the formula referring to an individual cell cannot
shrink or grow.
 
So, basically the answer is that they just haven't coded for it.
The code could easily test for first row/column and not auto-adjust.
Any of these conditions could be accounted for.
Thanks.
 
Back
Top