F
fselker
I just e-mailed my spreadsheet that demonstrates this problem. I'll be
interested in what you find!
interested in what you find!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
O
Otto Moehrbach
To who? Otto
P
Peo Sjoblom
O
Otto Moehrbach
Oh, that is a low blow!. Otto
Peo Sjoblom said:To whom? Peo <bg>
F
fselker
I didn't actually send it to either Who or Whom, but I sent it to Jim Rech
and he provided a most excellent answer included below - basically that once
sheets pass a certain undisclosed size they become foolish in their
recalculation, so what is normally a 1 second calculation becomes a 15 hour
one.
Hi Frank-
COUNTIFs can be slow, but that is not the problem with your workbook. The
problem is that Excel's smart recalc is turned off by the sheer size of your
workbook and so it has to recalc almost every formula for every COUNTIF.
Excel normally keeps track of what cells each formula depends on and does
not recalc a formula unless a precedent cell has changed value. This is what
I mean by smart recalc. But when there are too many formulas or they are too
complex Excel stops doing this and recalcs cells whether they need it or not.
That's what you're seeing. Each time Excel calcs one of your COUNTIFs it
sees it depends on the range H17:H56693 so it first recalcs that range.
(When you do an F2, Enter, Excel just calcs that cell, not precedent ranges.)
But you can easily reduce the number of formulas and make your calcs far
faster. Select the range H17:H56693 and convert it from formulas to values
(First do a Copy then and then do an Edit, Paste Special, Values right in
place). Now put an "=" in front of the F3 formula and copy it and press F9.
FYI, Excel 12 (the version now in beta test) can keep track of more
dependencies so it calcs your sheets as is.
Jim Rech
and he provided a most excellent answer included below - basically that once
sheets pass a certain undisclosed size they become foolish in their
recalculation, so what is normally a 1 second calculation becomes a 15 hour
one.
Hi Frank-
COUNTIFs can be slow, but that is not the problem with your workbook. The
problem is that Excel's smart recalc is turned off by the sheer size of your
workbook and so it has to recalc almost every formula for every COUNTIF.
Excel normally keeps track of what cells each formula depends on and does
not recalc a formula unless a precedent cell has changed value. This is what
I mean by smart recalc. But when there are too many formulas or they are too
complex Excel stops doing this and recalcs cells whether they need it or not.
That's what you're seeing. Each time Excel calcs one of your COUNTIFs it
sees it depends on the range H17:H56693 so it first recalcs that range.
(When you do an F2, Enter, Excel just calcs that cell, not precedent ranges.)
But you can easily reduce the number of formulas and make your calcs far
faster. Select the range H17:H56693 and convert it from formulas to values
(First do a Copy then and then do an Edit, Paste Special, Values right in
place). Now put an "=" in front of the F3 formula and copy it and press F9.
FYI, Excel 12 (the version now in beta test) can keep track of more
dependencies so it calcs your sheets as is.
Jim Rech
F
fselker
Hold the phone - I don't think this is the answer. More to follow.