Excel 2007 - Calculation Problems

[Alan Smith]

Hi all,

I am using Excel 2007, and have a large file (11MB) that is giving me
problems. The file is used to forecast revenue opportunities for a 4 year
period, and then calculate the recognized revenue based on the timing of
deals, revenue type etc. and is approxiamately 735 rows by 1700 columns.

Within the worksheet are many formulas, using various combinations of SUM,
VLOOKUP, SUMPRODUCT, OFFSET, SUBTOTAL, SUMIF, INDEX, COUNTA, MIN and probably
a couple more, but there are no errors in the worksheet.

Despite constantly checking the settings (Automatic, no iterations), and/or
hitting F9 (which doesn't work, btw), I have a "Calculate" message in the
status bar. I believe the previous 65536 limits were removed in 2007, so that
should not be an issue. Is there something else causing a problem? Any ideas?

Thanks,

Alan

 

[Niek Otten]

Hi Alan,

I'm not sure the 65536 limit is removed if you use an old pre-2007 file.
Try saving it as a "true" 2007 file and re-open. What happens? Please respond here.

| Hi all,
|
| I am using Excel 2007, and have a large file (11MB) that is giving me
| problems. The file is used to forecast revenue opportunities for a 4 year
| period, and then calculate the recognized revenue based on the timing of
| deals, revenue type etc. and is approxiamately 735 rows by 1700 columns.
|
| Within the worksheet are many formulas, using various combinations of SUM,
| VLOOKUP, SUMPRODUCT, OFFSET, SUBTOTAL, SUMIF, INDEX, COUNTA, MIN and probably
| a couple more, but there are no errors in the worksheet.
|
| Despite constantly checking the settings (Automatic, no iterations), and/or
| hitting F9 (which doesn't work, btw), I have a "Calculate" message in the
| status bar. I believe the previous 65536 limits were removed in 2007, so that
| should not be an issue. Is there something else causing a problem? Any ideas?
|
| Thanks,
|
| Alan

 

[Jim Rech]

With 1700 columns I think you're in the new format.<g>

If calculations are sufficiently complicated or numerous Excel stops trying
to do an intelligent calculation (just the cells that have changed) and
instead just calcs the entire workbook. That's because the time saved in
the calc is less than the time it takes to keep track of dependencies. If
you've reached that point the Calc message is permanent I believe.

--
Jim
| Hi all,
|
| I am using Excel 2007, and have a large file (11MB) that is giving me
| problems. The file is used to forecast revenue opportunities for a 4 year
| period, and then calculate the recognized revenue based on the timing of
| deals, revenue type etc. and is approxiamately 735 rows by 1700 columns.
|
| Within the worksheet are many formulas, using various combinations of SUM,
| VLOOKUP, SUMPRODUCT, OFFSET, SUBTOTAL, SUMIF, INDEX, COUNTA, MIN and
probably
| a couple more, but there are no errors in the worksheet.
|
| Despite constantly checking the settings (Automatic, no iterations),
and/or
| hitting F9 (which doesn't work, btw), I have a "Calculate" message in the
| status bar. I believe the previous 65536 limits were removed in 2007, so
that
| should not be an issue. Is there something else causing a problem? Any
ideas?
|
| Thanks,
|
| Alan

 

[Dave F]

In previous versions of Excel "Calculate" in the status bar indicated
a circular reference somewhere.

 

[Jim Rech]

That's one thing but not the only thing.

This is the issue I had in mine:

http://support.microsoft.com/kb/243495

--
Jim
| In previous versions of Excel "Calculate" in the status bar indicated
| a circular reference somewhere.
|
| > With 1700 columns I think you're in the new format.<g>
| >
| > If calculations are sufficiently complicated or numerous Excel stops
trying
| > to do an intelligent calculation (just the cells that have changed) and
| > instead just calcs the entire workbook. That's because the time saved
in
| > the calc is less than the time it takes to keep track of dependencies.
If
| > you've reached that point the Calc message is permanent I believe.
| >
| > --
| >
| > | > | Hi all,
| > |
| > | I am using Excel 2007, and have a large file (11MB) that is giving me
| > | problems. The file is used to forecast revenue opportunities for a 4
year
| > | period, and then calculate the recognized revenue based on the timing
of
| > | deals, revenue type etc. and is approxiamately 735 rows by 1700
columns.
| > |
| > | Within the worksheet are many formulas, using various combinations of
SUM,
| > | VLOOKUP, SUMPRODUCT, OFFSET, SUBTOTAL, SUMIF, INDEX, COUNTA, MIN and
| > probably
| > | a couple more, but there are no errors in the worksheet.
| > |
| > | Despite constantly checking the settings (Automatic, no iterations),
| > and/or
| > | hitting F9 (which doesn't work, btw), I have a "Calculate" message in
the
| > | status bar. I believe the previous 65536 limits were removed in 2007,
so
| > that
| > | should not be an issue. Is there something else causing a problem? Any
| > ideas?
| > |
| > | Thanks,
| > |
| > | Alan
|

 

[Alan Smith]

Thanks for the comments so far. To respond to the questions:

1. Circular references - I don't get any notification or see any indication
that this is the problem.
2. Calc time - I thought removing the limits in 2007 effectively neutralized
the impact of dependencies? Regardless, even trying to recalculate the sheet
is a problem, as F9 seems to have provide no response.

I had no such problems with the file a few days ago, so it is possible it
has become corrupt (the fact that it has crashed Excel 6 times since
yesterday would lend some weight to this theory), but I just wanted to see if
there was something unusual I was unaware of before I started a rebuild.

Thanks for the assistance guys, but I think I have some work ahead of me :-(

Alan

 

[Dave F]

Yes, but the poster is using XL 2007. Your support link applies to
other versions.

XL 2007 has a much higher dependency limit.

As to the original poster: how do you know that you do not have any
circular references? There is no pop up alert indicating such. When
I intentionally create a circular reference (using XL 03) the only
indication I get is the Calculate message in the status bar.

I don't have access to XL 07 right now, but I suspect that is your
issue as well. Maybe test this theory by opening up a new XL 07
workbook, intentionally create a circular reference, and see what
happens?

Dave

 

[Jim Rech]

XL 2007 has a much higher dependency limit.

Really? Where did you read that? I trust you're not confusing that with
some other increase in Excel limits. This is not a limit that was
arbitrarily imposed like so many others. There is a real tradeoff between
time spend keeping track of dependencies and time calcing. Just increasing
memory limits isn't the issue.

Still you might be right. But where did you read it?

You must have killed the Circ toolbar by closing it manually. Delete
delete/rename you XLB file with Excel closed and it will be back with your
first calc after creating a circ.

 

[Jim Rech]

the fact that it has crashed Excel 6 times since yesterday would lend some
Oh, yeah!<g>.

Have you gotten SP1 yet? Can't hurt (I hope).

 

[Dave F]

Perhaps I'm misinterpreting the data at this link,
http://msdn2.microsoft.com/en-us/library/aa730921.aspx , but it seems
that it makes reference to increased dependency limits.

Dave

 

[Jim Rech]

You're right. Charles got his information from Dave Gainer's blog which I
searched yesterday to find something on this and failed. But there it is.
http://blogs.msdn.com/excel/archive/2005/09/26/474258.aspx

So there is a higher dependency limit but I suppose Alan could still have
hit it. And since he's certain there are no circs (Excel 2007 will say
"Circular Reference" in the status bar) I don't know why else Calculate is
always on. Me not knowing something doesn't prove much as we've seen
though<g>. He has some volatile functions, I wonder if that factors in? I
guess I'd have to see the workbook.

 

[Alan Smith]

I've only been using 2007 for a few weeks - my IT dept. insisted on the
upgrade during planning season

I'll check out SP1 (if I don't have it) and go from there.

Cheers.

 

[Alan Smith]

Sorry guys, been too busy to check in for a few days.

Regarding the circulars, everytime I have encountered one in XL I get the
help box and see the notification in the status bar. I am assuming no news is
good news here.

My guess now is that the inherent volatility of using so many functions in
1.25 million cells is causing some issues, though I would have thought that
designing a worksheet with 16 billion cells would mean one could use them all
if needed?

Alan