Re – GGVT Excel in Automatic and Manual modes of Calculation

T

TKT-Tang

Re – GGVT Excel in Automatic and Manual modes of Calculation

An Excel worksheet is set in the manual mode of calculation.
Re-calculation of the worksheet is activated by pressing the key F9.
As it turns out, in order to complete the calculations, it's so
required to press the key F9 once or twice more.

Subsequently, the worksheet is set in the automatic mode of
calculation. And there, it remains that complete re-calculation of the
worksheet is to be aided (by pressing the key F9) similar to the
situation in manual mode of calculation.

The time lapse during re-calculation of the worksheet is 30 seconds
approximately.

The following array formula is filled down into a range of the
worksheet cells :-

{=IF($BD54=CHAR(149),CHAR(149),"Line no. "&TEXT(
INDEX($R$54:$R$202,MAX(IF($BD54=$BE$54:$BE$202,
ROW($BE$54:$BE$202)-ROW($BE$54)+1,0))),"000")&"-"&
INDEX($S$54:$S$202,MAX(IF($BD54=$BE$54:$BE$202,
ROW($BE$54:$BE$202)-ROW($BE$54)+1,0)))&"-"&
INDEX($T$54:$T$202,MAX(IF($BD54=$BE$54:$BE$202,
ROW($BE$54:$BE$202)-ROW($BE$54)+1,0)))&"-"&
INDEX($U$54:$U$202,MAX(IF($BD54=$BE$54:$BE$202,
ROW($BE$54:$BE$202)-ROW($BE$54)+1,0))))}

It is readily discernible the formulae within the given range are
partially evaluated (after initiating the re-calculation) ; until the
key F9 is (additionally) pressed, calculations are hampered thereof.
Hindrance (if any) upon other formulae (located on the worksheet) is
not visually detectable.

The worksheet consists of 9000 cells more or less ; namely, the given
quantity is merely a fraction of the available cells in a single
column of the (ubiquitous) Excel.

Please share your comment on the above delineation.

Regards.
 
C

Charles Williams

The most likely causes are:

- the workbook has a circular reference and iterations are set to 1. Try
turning iteration on to see if a single F9 solves the problem.

- the workbook has a user-defined function somewhere that is not properly
coded (see http://www.DecisionModels.com/calcsecretsj.htm). Try using
Ctrl-Alt-F9 to see if that solves the problem.

- the workbook is being calculated with Excel 97 and Excel does not have the
SR1 and SR2 updates applied (see Help about to check the update level). Also
worth checking the update level for Excel 2000 or later.

- the workbook has a corrupted dependency tree. Try recalculating with Excel
2002 using Ctrl-Alt-Shift-F9


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Top