Excel in Office 2007

[Professor@discussions.microsoft.com frustrated Professor]

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?

[Niek Otten]

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 Prof...@discussions.microsoft.com> wrote in message
news:811285F8-440E-4D5C...@microsoft.com...

[frustrated Professor]

"Automatic Except for Data Tables" is not checked. That is not the problem.

[ilia]

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

<frustratedProfes...@discussions.microsoft.com> 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 Profes...@discussions.microsoft.com> wrote in message

> >news:811285F8-440E-4D5C...@microsoft.com...
> > |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 -

[frustrated Professor]

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.

[Niek Otten]

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" <frustrate...@discussions.microsoft.com> wrote in message
news:5F88DB85-AC59-46BC...@microsoft.com...

[Charles Williams]

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" <nico...@xs4all.nl> wrote in message
news:%23YJLYeW...@TK2MSFTNGP02.phx.gbl...

[frustrated Professor]

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.

[ED2]

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