Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
I create a 2-way table that is populated with random numbers. I then attempt
to calculate some basic descriptive statistics (Min, Max, Average, Stdev, etc.) of the numbers in the table. When I hit F9, the table recalculates but the descriptive statistics do not. In fact, the descriptive statistics only recalculate when the cells containing these functions are "Highlighted." This is not how things used to be. What is going on? How do I fix this problem? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
Maybe:
On the Formulas tab, Calculation group, Calculation options: Automatic Except for Data tables is checked -- Kind regards, Niek Otten Microsoft MVP - Excel "frustrated Professor" <frustrated wrote in message ... |I create a 2-way table that is populated with random numbers. I then attempt | to calculate some basic descriptive statistics (Min, Max, Average, Stdev, | etc.) of the numbers in the table. When I hit F9, the table recalculates but | the descriptive statistics do not. In fact, the descriptive statistics only | recalculate when the cells containing these functions are "Highlighted." | This is not how things used to be. What is going on? How do I fix this | problem? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
"Automatic Except for Data Tables" is not checked. That is not the problem.
"Niek Otten" wrote: Maybe: On the Formulas tab, Calculation group, Calculation options: Automatic Except for Data tables is checked -- Kind regards, Niek Otten Microsoft MVP - Excel "frustrated Professor" <frustrated wrote in message ... |I create a 2-way table that is populated with random numbers. I then attempt | to calculate some basic descriptive statistics (Min, Max, Average, Stdev, | etc.) of the numbers in the table. When I hit F9, the table recalculates but | the descriptive statistics do not. In fact, the descriptive statistics only | recalculate when the cells containing these functions are "Highlighted." | This is not how things used to be. What is going on? How do I fix this | problem? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
I can't recreate the problem. In any calculation mode, I have a table
with some random generated numbers and some descriptive statistics you mentioned. Each time I press F9, everything refreshes as expected. On Nov 12, 9:35 am, frustrated Professor wrote: "Automatic Except for Data Tables" is not checked. That is not the problem. "Niek Otten" wrote: Maybe: On the Formulas tab, Calculation group, Calculation options: Automatic Except for Data tables is checked -- Kind regards, Niek Otten Microsoft MVP - Excel "frustrated Professor" <frustrated wrote in message ... |I create a 2-way table that is populated with random numbers. I then attempt | to calculate some basic descriptive statistics (Min, Max, Average, Stdev, | etc.) of the numbers in the table. When I hit F9, the table recalculates but | the descriptive statistics do not. In fact, the descriptive statistics only | recalculate when the cells containing these functions are "Highlighted." | This is not how things used to be. What is going on? How do I fix this | problem?- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
Hi ilia:
Thanks so much for looking into the problem. Let me be more specific and see if that helps. I am running Office 2007 with Microsoft Windows XP. Open a clean Excel spreadsheet. In cell B3 type "=rand(). In cells B4-B6 type 1, 2, and 3 respectively. In cells C3, D3, E3 type 1, 2, and 3 respectively. Now, highlight the area B3 through E6 (4X4 array). From the Data tab select "what if" and choose Table. Select a couple of blank cells for the row and column inputs--anything will do. I selected $A$8 and $A$9. Now, choosing "OK" will populate the table with random numbers in cells C4-E6. Now, off to the right, in cell G4, type =MIN(C4:E6). Below that in cell G5, type = MAX(C4:E6) and in cell G6 type =AVERAGE(C4:E6). Now, press F9. When I press F9, the random numbers in the table (cells C4-E6) change, but the formulas in cells G4-G6 do not recalculate (values do not change). Now, hold down shift and highlight cells G4-E6 and notice that as you highlight each cell, it updates. You can repeat this several times. Each time you want the minimum, maximum, and average to update, you have to highlight them--simply pressing F9 will not change them. I thought I must have a bad install of Excel, so I repeated this on one of my students computers (who also had Office 2007--this time with VISTA)--same result exactly. I have been using and teaching Excel for a long time, so I have done this sort of thing (with the table) many times, but I have never seen this situation where you can only get formulas to update by highlighting their cells?? If you still can't get this to repeat, then maybe my student and I both have either bad installs or a setting that we should change. But, if it is a setting, I can't determine what it might be. "ilia" wrote: I can't recreate the problem. In any calculation mode, I have a table with some random generated numbers and some descriptive statistics you mentioned. Each time I press F9, everything refreshes as expected. On Nov 12, 9:35 am, frustrated Professor wrote: "Automatic Except for Data Tables" is not checked. That is not the problem. "Niek Otten" wrote: Maybe: On the Formulas tab, Calculation group, Calculation options: Automatic Except for Data tables is checked -- Kind regards, Niek Otten Microsoft MVP - Excel "frustrated Professor" <frustrated wrote in message ... |I create a 2-way table that is populated with random numbers. I then attempt | to calculate some basic descriptive statistics (Min, Max, Average, Stdev, | etc.) of the numbers in the table. When I hit F9, the table recalculates but | the descriptive statistics do not. In fact, the descriptive statistics only | recalculate when the cells containing these functions are "Highlighted." | This is not how things used to be. What is going on? How do I fix this | problem?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
Very interesting!
I followed your recipe and I can confirm the behavior. I put =RAND() in cells A8 and A9 and changed G4 to =MIN(C4:E6)+0*A8 The cell now changes with each recalc (of course), but it is one cycle behind: you get the minimum of the previous set of data in the table. Editing the formula highlights the right cells, so the precedent cells are recognized. The Show precedents button also works correctly. Of course there is the tricky fact that the table doesn't have precedent cells. That really shouldn't matter (and it doesn't in Excel2003), but it is a bit special. I may be wrong, I'll look into it again tomorrow (sometimes a night's sleep helps), but for the moment I think this is a bug. I'll definitely be back tomorrow on this. -- Kind regards, Niek Otten Microsoft MVP - Excel "frustrated Professor" wrote in message ... | Hi ilia: | | Thanks so much for looking into the problem. Let me be more specific and | see if that helps. I am running Office 2007 with Microsoft Windows XP. Open | a clean Excel spreadsheet. In cell B3 type "=rand(). In cells B4-B6 type 1, | 2, and 3 respectively. In cells C3, D3, E3 type 1, 2, and 3 respectively. | Now, highlight the area B3 through E6 (4X4 array). From the Data tab select | "what if" and choose Table. Select a couple of blank cells for the row and | column inputs--anything will do. I selected $A$8 and $A$9. Now, choosing | "OK" will populate the table with random numbers in cells C4-E6. Now, off to | the right, in cell G4, type =MIN(C4:E6). Below that in cell G5, type = | MAX(C4:E6) and in cell G6 type =AVERAGE(C4:E6). Now, press F9. When I press | F9, the random numbers in the table (cells C4-E6) change, but the formulas in | cells G4-G6 do not recalculate (values do not change). Now, hold down shift | and highlight cells G4-E6 and notice that as you highlight each cell, it | updates. You can repeat this several times. Each time you want the minimum, | maximum, and average to update, you have to highlight them--simply pressing | F9 will not change them. I thought I must have a bad install of Excel, so I | repeated this on one of my students computers (who also had Office 2007--this | time with VISTA)--same result exactly. I have been using and teaching Excel | for a long time, so I have done this sort of thing (with the table) many | times, but I have never seen this situation where you can only get formulas | to update by highlighting their cells?? If you still can't get this to | repeat, then maybe my student and I both have either bad installs or a | setting that we should change. But, if it is a setting, I can't determine | what it might be. | | | | "ilia" wrote: | | I can't recreate the problem. In any calculation mode, I have a table | with some random generated numbers and some descriptive statistics you | mentioned. Each time I press F9, everything refreshes as expected. | | On Nov 12, 9:35 am, frustrated Professor | wrote: | "Automatic Except for Data Tables" is not checked. That is not the problem. | | | | "Niek Otten" wrote: | Maybe: | | On the Formulas tab, Calculation group, Calculation options: Automatic Except for Data tables is checked | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "frustrated Professor" <frustrated wrote in message | ... | |I create a 2-way table that is populated with random numbers. I then attempt | | to calculate some basic descriptive statistics (Min, Max, Average, Stdev, | | etc.) of the numbers in the table. When I hit F9, the table recalculates but | | the descriptive statistics do not. In fact, the descriptive statistics only | | recalculate when the cells containing these functions are "Highlighted." | | This is not how things used to be. What is going on? How do I fix this | | problem?- Hide quoted text - | | - Show quoted text - | | | |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
I agree with Niek: I think its definitely a bug.
Interestingly the underlying numbers ARE being recalculated, (you can check from the immdiate pane) its just the display that is not being updated until that portion of the screen gets repainted. (moveing another window to cover the offending cells and then moveing the covering window away again will reveal the correct numbers!). regards Charles _________________________________________ UK Cambridge XL Users Conference 29-30 Nov http://www.exceluserconference.com/UKEUC.html "Niek Otten" wrote in message ... Very interesting! I followed your recipe and I can confirm the behavior. I put =RAND() in cells A8 and A9 and changed G4 to =MIN(C4:E6)+0*A8 The cell now changes with each recalc (of course), but it is one cycle behind: you get the minimum of the previous set of data in the table. Editing the formula highlights the right cells, so the precedent cells are recognized. The Show precedents button also works correctly. Of course there is the tricky fact that the table doesn't have precedent cells. That really shouldn't matter (and it doesn't in Excel2003), but it is a bit special. I may be wrong, I'll look into it again tomorrow (sometimes a night's sleep helps), but for the moment I think this is a bug. I'll definitely be back tomorrow on this. -- Kind regards, Niek Otten Microsoft MVP - Excel "frustrated Professor" wrote in message ... | Hi ilia: | | Thanks so much for looking into the problem. Let me be more specific and | see if that helps. I am running Office 2007 with Microsoft Windows XP. Open | a clean Excel spreadsheet. In cell B3 type "=rand(). In cells B4-B6 type 1, | 2, and 3 respectively. In cells C3, D3, E3 type 1, 2, and 3 respectively. | Now, highlight the area B3 through E6 (4X4 array). From the Data tab select | "what if" and choose Table. Select a couple of blank cells for the row and | column inputs--anything will do. I selected $A$8 and $A$9. Now, choosing | "OK" will populate the table with random numbers in cells C4-E6. Now, off to | the right, in cell G4, type =MIN(C4:E6). Below that in cell G5, type = | MAX(C4:E6) and in cell G6 type =AVERAGE(C4:E6). Now, press F9. When I press | F9, the random numbers in the table (cells C4-E6) change, but the formulas in | cells G4-G6 do not recalculate (values do not change). Now, hold down shift | and highlight cells G4-E6 and notice that as you highlight each cell, it | updates. You can repeat this several times. Each time you want the minimum, | maximum, and average to update, you have to highlight them--simply pressing | F9 will not change them. I thought I must have a bad install of Excel, so I | repeated this on one of my students computers (who also had Office 2007--this | time with VISTA)--same result exactly. I have been using and teaching Excel | for a long time, so I have done this sort of thing (with the table) many | times, but I have never seen this situation where you can only get formulas | to update by highlighting their cells?? If you still can't get this to | repeat, then maybe my student and I both have either bad installs or a | setting that we should change. But, if it is a setting, I can't determine | what it might be. | | | | "ilia" wrote: | | I can't recreate the problem. In any calculation mode, I have a table | with some random generated numbers and some descriptive statistics you | mentioned. Each time I press F9, everything refreshes as expected. | | On Nov 12, 9:35 am, frustrated Professor | wrote: | "Automatic Except for Data Tables" is not checked. That is not the problem. | | | | "Niek Otten" wrote: | Maybe: | | On the Formulas tab, Calculation group, Calculation options: Automatic Except for Data tables is checked | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "frustrated Professor" <frustrated wrote in message | ... | |I create a 2-way table that is populated with random numbers. I then attempt | | to calculate some basic descriptive statistics (Min, Max, Average, Stdev, | | etc.) of the numbers in the table. When I hit F9, the table recalculates but | | the descriptive statistics do not. In fact, the descriptive statistics only | | recalculate when the cells containing these functions are "Highlighted." | | This is not how things used to be. What is going on? How do I fix this | | problem?- Hide quoted text - | | - Show quoted text - | | | |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
So...
Based on what has been said, I have concluded that this is a bug in Excel in Office 2007. I will cease searching for a setting to change and wait for Microsoft to correct the problem. Thanks to everyone. "frustrated Professor" wrote: I create a 2-way table that is populated with random numbers. I then attempt to calculate some basic descriptive statistics (Min, Max, Average, Stdev, etc.) of the numbers in the table. When I hit F9, the table recalculates but the descriptive statistics do not. In fact, the descriptive statistics only recalculate when the cells containing these functions are "Highlighted." This is not how things used to be. What is going on? How do I fix this problem? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel in Office 2007
I, too, am a frustrated professor as I both use this feature in my own
research and I instruct/require my students to do so as well. everything worked in 2003 and earlier versions. Ed "frustrated Professor" wrote: So... Based on what has been said, I have concluded that this is a bug in Excel in Office 2007. I will cease searching for a setting to change and wait for Microsoft to correct the problem. Thanks to everyone. "frustrated Professor" wrote: I create a 2-way table that is populated with random numbers. I then attempt to calculate some basic descriptive statistics (Min, Max, Average, Stdev, etc.) of the numbers in the table. When I hit F9, the table recalculates but the descriptive statistics do not. In fact, the descriptive statistics only recalculate when the cells containing these functions are "Highlighted." This is not how things used to be. What is going on? How do I fix this problem? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Opening an Office 2007 Excel file in Office 2003 | Excel Discussion (Misc queries) | |||
How do I make Office Enterprise 2007 look like Office XP professio | Setting up and Configuration of Excel | |||
Unable to open Office 2007 files using Office 2002 XP | Excel Discussion (Misc queries) | |||
Migrating Office XP EXCEL files to Office 2007 | Setting up and Configuration of Excel | |||
Performance not upto Office 2003 standards in Office 2007 | Excel Discussion (Misc queries) |