Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Function/Formula Overloads
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Function/Formula Overloads
See this:
http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "Rob" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Function/Formula Overloads
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" wrote: 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Function/Formula Overloads
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? "Mike H" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Function/Formula Overloads
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. "T. Valko" wrote: See this: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "Rob" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Function/Formula Overloads
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. -- __________________________________ HTH Bob "Rob" wrote in message ... 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. "T. Valko" wrote: See this: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "Rob" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with function / Formula | Excel Worksheet Functions | |||
Help with OR function in IF function formula | Excel Worksheet Functions | |||
how do i add a function to a formula? | Excel Discussion (Misc queries) | |||
A formula/function to return a formula/function | Excel Worksheet Functions | |||
Formula/function | Excel Worksheet Functions |