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 -
|
|
|