Calculation

  • Thread starter Thread starter Bill Ridgeway
  • Start date Start date
B

Bill Ridgeway

I have a rather large spreadsheet (database) with a large number of
formulae. Because the process has been slowed down by the amount of
recalculation I can see the process of the recalculation. This occurs in
batches of perhaps 2 or 3 rows. The odd thing is that these batches appear
out of sequence not, as you'd expect, in chronological order.

Has anyone got a simple answer to this strange behaviour?

Thanks.

Bill Ridgeway
 
Thanks Mike. As you say it's not so simple. Neither does it answer the
question why (as each re-calculation is dependent on the result of the
immediately previous cell - over 3000 rows) can it re-calculate out of
sequence? I suppose this is just one of those things that we have too
accept!

The general concept seems to be to optimize re-calculation. This supports
the notion that sorting the database slows down re-calculation and that
re-calculation is speeded after copying all the formulae down each column
(which I do from time-to-time).

Regards.

Bill Ridgeway
 
Mon, 2 Jul 2007 11:25:16 +0100 from Bill Ridgeway <info@
1001solutions.co.uk>:
I have a rather large spreadsheet (database) with a large number of
formulae. Because the process has been slowed down by the amount of
recalculation I can see the process of the recalculation. This occurs in
batches of perhaps 2 or 3 rows. The odd thing is that these batches appear
out of sequence not, as you'd expect, in chronological order.
Click to expand...

I assume you mean "not in row order" or "not in column order". Unless
you've got a time machine, you are seeing them in chronological
order.

Excel knows about dependencies, so it recalculates such that if B99
is used by A2 they are calculated in that order.

http://msdn2.microsoft.com/en-us/library/aa730921.aspx
explains how Excel does its recalculations (and a lot more).
 
Stan Brown said:
Mon, 2 Jul 2007 11:25:16 +0100 from Bill Ridgeway <info@
1001solutions.co.uk>:

I assume you mean "not in row order" or "not in column order". Unless
you've got a time machine, you are seeing them in chronological
order.

Excel knows about dependencies, so it recalculates such that if B99
is used by A2 they are calculated in that order.

http://msdn2.microsoft.com/en-us/library/aa730921.aspx
explains how Excel does its recalculations (and a lot more).
Click to expand...

That's interesting! My spreadsheet consists of several columns each cell of
which (except the first) calculates progressively from row 1 through 3000.
it is logical to assume (using the 'dependence' theory) that each cell can
only be calculated using the returned value of the previous cell when the
returned value of the previous cell is known ... I would, therefore, expect
to see a ripple of changed values progressing down the column. It doesn't.
I can see groups of three or four cells (including the last cell) change out
of sequence. That is not logical.

Regards.

Bill Ridgeway
 
Hi Bill,

I guess what you are saying is that your idea of a logical
chronological order differs from Excels.

From my personal perspective I say "Vive le Difference!"
If Excel were to follow me down some of the shortcuts
that I attempt we would all be in a lot of trouble. <g>

Regards
Martin
 
Back
Top