Help with Function/Formula Overloads

[Rob]

Hello....

I was wondering just how much Excel 2003 can take with regards to massive
amounts of Functions/Formula? I have a workbook and two worksheets have many
functions in it and every time I make a slight change to anything it takes
about 15 minutes to recalculate everything... even if I do not make a change
to a formula/function or the data that the are pulling from.


if there is an issue with this can someone help me with thinning out my
mess.? Currently I have on one sheet -- seven columns with 1003 rows that
have a formula/function in each and every cell. On that sheet alone that's
7021 cells with code and over 30,000 "IF" Statements/checks. On the other
sheet, I have at least 5 times as much.


Thanks In Advance!

 

[T. Valko]

See this:

http://www.decisionmodels.com/

 

[Mike H]

Rob,

You should consider ways of trimming this down but in the meantime why not
set calculation to manual so at least you can control when it calculates by
tapping F9

Tools|Options - calculation tab
select manual

Mike

 

[Rob]

Thanks That's a great suggestion but isn't that setting a global setting
where if I disable it then no matter what workbook I open it will not
auto-calculate? Or is there a way to disable it for just a certain workbook?

 

[Rob]

I thank you VERY Much for the suggestion, however, I seriously got lost
trying to figure out what i'm supposed to learn from that. However I DID see
that I seriously need to understand what is and isn't volital in my formulae
and nix it.


Consiquently.... Is there a means to create a VBA Macro that will do all
the work of the formulae when it is ran and then just display the results
without any formulae in any cells?

If so How do I convert Formulae to VBA so that it will work and go row by row?

Thanks Again and sorry for the late reply.

 

[Bob Phillips]

It may not even be volatile functions, it may just be formulae that are
repeatedly doing the same thing, such as multiple lookups into a table where
some element in the table is changing, every one of your lookups will
recalculate.

It sounds as though your workbook needs a serious overhaul, 15 mins is far
to long to wait for recalc, 30 secs is enough to turn an average user off.

VBA could be of use, Charles Williams does a demo where he shows how a
simple UDF can be umpteen times faster than a COUNTIF, but it would need to
fit the problem, and we haven't seen that.