Interesting Formula Problem
 

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.


--
Michael

Interesting Formula Problem
 

Michael Laferriere wrote:
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

Interesting Formula Problem
 

=sum(b2:d2)


"Beege" wrote in message
. ..
Michael Laferriere wrote:
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

Interesting Formula Problem
 

On Mar 8, 10:05 am, "Michael Laferriere" wrote:
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.

Interesting Formula Problem
 

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





"joeu2004" wrote in message
oups.com...
On Mar 8, 10:05 am, "Michael Laferriere" wrote:
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.

Interesting Formula Problem
 

"Michael" writes:

"Beege" wrote in message
...
Michael Laferriere wrote:
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
=sum(b2:d2)

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

--
Patrick "The Chief Instigator" Humphrey ) Houston, Texas
chiefinstigator.us.tt/aeros.php (TCI's 2006-07 Houston Aeros) AA#2273
LAST GAME: Chicago 4, Houston 1 (March 7)
NEXT GAME: Saturday, March 10 vs. Chicago, 7:35

Interesting Formula Problem
 

Hi Michael

With a formula in column E of =SUM(B2:D2) 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


--
Regards

Roger Govier


"Michael" wrote in message
...
=sum(b2:d2)


"Beege" wrote in message
. ..
Michael Laferriere wrote:
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