Interesting Formula Problem

[Michael Laferriere]

Hi Folks - Interesting issue in Excel ... I have a sheet that looks like
this:

Jan Feb Mar Total
Rent 1000 1000 1000 3000
Utilities 500 500 500 1500

The Total column is calculated with the sum function. If I insert a column
to the left of the totals columns, then enter manually 1000 for Rent, the
formula recalculates to include the new column. Perfect! However, if I
"fill" the 1000 from the Mar column to the new column, the formula does not
recalculate. Odd! Any ideas why? Thanks.

 

[Beege]

Hi Folks - Interesting issue in Excel ... I have a sheet that looks like
this:

Jan Feb Mar Total
Rent 1000 1000 1000 3000
Utilities 500 500 500 1500

The Total column is calculated with the sum function. If I insert a column
to the left of the totals columns, then enter manually 1000 for Rent, the
formula recalculates to include the new column. Perfect! However, if I
"fill" the 1000 from the Mar column to the new column, the formula does not
recalculate. Odd! Any ideas why? Thanks.

What's the formula look like that you have in the total column?

Beege

 

[Michael]

=sum(b2:d2)

What's the formula look like that you have in the total column?

Beege

 

[joeu2004]

I have a sheet that looks like this:
Jan Feb Mar Total
Rent 1000 1000 1000 3000
Utilities 500 500 500 1500

The Total column is calculated with the sum function. If I insert a column
to the left of the totals columns, then enter manually 1000 for Rent, the
formula recalculates to include the new column. Perfect! However, if I
"fill" the 1000 from the Mar column to the new column, the formula does not
recalculate. Odd! Any ideas why?

Just a hiccup in Excel's inductive algorithms. When you insert the
column, notice that the SUM range is not (yet) updated in the Total
column. Excel updates if you immediately manually enter a number into
the new column only because Excel ass-u-me-s that was your intent.
(It might not have been, in which case you probably would have written
a very different inquiry.) Apparently, Bill's Boys decided not to
take the same inductive leap when you do a "fill" or cut-and-paste.
Doesn't make sense? Go figure!

The way to do this reliably is to have a (hidden?) blank column just
to the left of the Total column and perhaps to the left of the Jan
column, and include the blank column(s) in the SUM range. Then, when
you insert a column, the SUM range is updated for good reason, whether
or not you enter a number in the new column.

 

[Michael]

joeu .... After some fiddling, it seems this behaviour is controlled by an
option:

.... Extend list formats and formulas Select to automatically format new
items added to the end of a list to match the format of the rest of the
list. Formulas that are repeated in every row are also copied. To be
extended, formats and formulas must appear in at least three of the five
last rows preceding the new row. ...

Since it does not Update the formulas reliably, it's better to turn it off
....Thanks.

Michael

 

[The Chief Instigator]

=sum(b2:d2)

That will only sum the three columns referenced - B, C, and D. Change that
"d2" to include the new column.

 

[Roger Govier]

Hi Michael

With a formula in column E of =SUM(B22) then
If you insert a column before E, the formula will automatically adjust
to =SUM(B2:E2), =SUM(B2:G2) etc.

If you drag cell D2 with 1000 as it's value to E2, you will overwrite
the formula with the value 1000