| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Very interesting problem that should be a snap to figure out! | Excel Discussion (Misc queries) | |||
| Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
| Interesting Workbook formula problem | Excel Worksheet Functions | |||
| interesting question can anyone help | Excel Discussion (Misc queries) | |||
| Interesting TIF file problem | Excel Discussion (Misc queries) | |||
Linear Mode
