On 8/1/2011 3:08 PM, Clif McIrvin wrote:
wrote in message
...
Clif,

Thanks for the in-depth help.

I added the code, but I'm not sure there's a difference - although
my design might have something to do with that.

Using Excel 2007, I have an .xlsb workbook that contains two
worksheets.

On Sheet1, there are about 1,000,000 cells that each contain sixteen
nested formulas.

1) In each cell, there's an initial IF test that determines whether
the next of three other nested IF formulas should run.

2) Each nested IF formula contains four other function calculations.
If the first IF formula evaluates TRUE, then calculation for that cell
stops. Otherwise, the second IF formula runs and if it evaluates
FALSE, the last IF formula runs. So in total, there's a maximum of
about 16,000,000 calculations.

On Sheet2, there's two radio buttons, ON and OFF, that set a TRUE /
FALSE value. That value is what the initial IF formulas on Sheet1 use
to determine if the formulas should further calculate.

So on Sheet2, choosing the ON radio button triggers the formulas on
Sheet1 to calculate. But since Sheet2 has the focus, screen repainting
doesn't occur.

The biggest bottleneck appears to be a third party COM add-in that
provides four custom functions in each of the three nested IF
formulas.

Apparently, the COM application that returns the evaluated data
takes too long to do so.

This appears to cause all of the nested IF formulas to evaluate
which is why it takes so long up to 3.5 hours in total.

If there's a way to speed that up, I'd like to know.

Until then, I'll be hoping The Great Pumpkin at Halloween will bring
me a new Intel octo-core hyper-threaded computer.


- Ronald K.



Your worksheet is way over my head. Seems like I have read that nested
formulas are always evaluated regardless of the result of the IF
condition argument... though it's possible I'm confusing platforms.
Also, I believe I've read that calling a volitile UDF (COM add-in custom
function?) causes reclaculations. All in all, it sounds like the sheet
design is causing excessive "over-calculation".

It might be worth your while to start a new thread asking for help to
improve your worksheet's calculation efficiency ... it could well be
that there are other ways to get the needed results.

If you do, I'd suggest starting with the summary you posted above, then
post those 16 nested formulas -- assuming you can do that without
violating any non-disclosure agreements or such.

It would also be helpful to anyone inclined to help you if you could
describe the calculation rules "in english" (or "psuedo code") -- when
it comes to background information the rule generally is: the more the
merrier!

Good luck!

I think that's great advice - I'm curious about what the nested formulas
look like too.

Also is there any way you could write your own functions to perform the
calculations the COM add-in is doing? Would you mind telling us what the
COM add-in is doing?

Mike