Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Slow Calculations
I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. |
#2
|
|||
|
|||
Maybe using a pivottable will be quicker.
mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson |
#3
|
|||
|
|||
Maybe. But I need the report dynamic enough to generate different subtotals based on input from the user. Can a pivot table recalculate itself on the fly (e.g. without someone specifically recalculating the table)? If so, how? Thanks, Mickey "Dave Peterson" wrote: Maybe using a pivottable will be quicker. mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson |
#4
|
|||
|
|||
Nope.
But pivottables aren't made to, either. If your data that creates the pivottable is quite large, then refreshing the pivottable could be slow, too. So you'd be just exchanging one slow tool for a different slow tool. Some options... Teach the users to click anywhere on the pivottable and hit the refresh icon (!) on that pivottable toolbar. If you want to refresh the pivottables on a given sheet (dedicated to the pivottable), you could add some code to the worksheet_activate event to update just those pivottables. Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable Application.EnableEvents = False For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT Application.EnableEvents = True End Sub There are other things you could do, too. You may want to post a little more info to make any response relevant. mikxtr wrote: Maybe. But I need the report dynamic enough to generate different subtotals based on input from the user. Can a pivot table recalculate itself on the fly (e.g. without someone specifically recalculating the table)? If so, how? Thanks, Mickey "Dave Peterson" wrote: Maybe using a pivottable will be quicker. mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
Well, here's the catch: no macros. What I'd prefer to do is store the data in an access table and pull only the necessary data through macros. But I've been restricted by my boss from macros (we're trying to minimize user-error), so there it is. My file generates 3 reports, each on a separate worksheet. The data is 68 columns wide & 8000 rows deep (and growing). I inserted a couple of columns to run off the user menu. In other words. the user selects the criteria that s/he wants to see, and these columns look at the codes in the data and return info that determines whether that row of data is something that the report should include. Then I sumif based on those columns. So far, we've decided to make the calculations a little more linear. Instead of 3 sheets calculating off the data sheet, I now have one sheet calculating off the data sheet and the other two sheets calculating off the first sheet. Does this help? Does it even make sense? Many thanks for your previous suggestions. Mickey "Dave Peterson" wrote: Nope. But pivottables aren't made to, either. If your data that creates the pivottable is quite large, then refreshing the pivottable could be slow, too. So you'd be just exchanging one slow tool for a different slow tool. Some options... Teach the users to click anywhere on the pivottable and hit the refresh icon (!) on that pivottable toolbar. If you want to refresh the pivottables on a given sheet (dedicated to the pivottable), you could add some code to the worksheet_activate event to update just those pivottables. Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable Application.EnableEvents = False For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT Application.EnableEvents = True End Sub There are other things you could do, too. You may want to post a little more info to make any response relevant. mikxtr wrote: Maybe. But I need the report dynamic enough to generate different subtotals based on input from the user. Can a pivot table recalculate itself on the fly (e.g. without someone specifically recalculating the table)? If so, how? Thanks, Mickey "Dave Peterson" wrote: Maybe using a pivottable will be quicker. mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
You may want to visit Charles Williams' web site:
http://www.DecisionModels.com/calcsecrets.htm He has a few tips that may help. But you may want to talk to the boss once more. If you can make your program work via code and Access, then you might be reducing the number of user errors (although the number of developer errors could go up <vbg). But if your code can eliminate much of the grunt work, I would think that the final results would be more, er, trustworthy. Remind him or her that a computer is supposed to make those mundane tasks that can be mechanized much more consistent (and probably lots faster!). mikxtr wrote: Well, here's the catch: no macros. What I'd prefer to do is store the data in an access table and pull only the necessary data through macros. But I've been restricted by my boss from macros (we're trying to minimize user-error), so there it is. My file generates 3 reports, each on a separate worksheet. The data is 68 columns wide & 8000 rows deep (and growing). I inserted a couple of columns to run off the user menu. In other words. the user selects the criteria that s/he wants to see, and these columns look at the codes in the data and return info that determines whether that row of data is something that the report should include. Then I sumif based on those columns. So far, we've decided to make the calculations a little more linear. Instead of 3 sheets calculating off the data sheet, I now have one sheet calculating off the data sheet and the other two sheets calculating off the first sheet. Does this help? Does it even make sense? Many thanks for your previous suggestions. Mickey "Dave Peterson" wrote: Nope. But pivottables aren't made to, either. If your data that creates the pivottable is quite large, then refreshing the pivottable could be slow, too. So you'd be just exchanging one slow tool for a different slow tool. Some options... Teach the users to click anywhere on the pivottable and hit the refresh icon (!) on that pivottable toolbar. If you want to refresh the pivottables on a given sheet (dedicated to the pivottable), you could add some code to the worksheet_activate event to update just those pivottables. Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable Application.EnableEvents = False For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT Application.EnableEvents = True End Sub There are other things you could do, too. You may want to post a little more info to make any response relevant. mikxtr wrote: Maybe. But I need the report dynamic enough to generate different subtotals based on input from the user. Can a pivot table recalculate itself on the fly (e.g. without someone specifically recalculating the table)? If so, how? Thanks, Mickey "Dave Peterson" wrote: Maybe using a pivottable will be quicker. mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
Thanks, Dave. I would talk to my boss, but I understand his concern: Files with macros sometimes get opened by others with macros disabled, thus causing issues if your file is macro dependent. I would argue him on this point (like I do daily), but with Excel 2002, I have opened files many times without enabling macros simply because Excel did not prompt me. (And yes, I know about the security level, etc, but it still chooses not to work ... randomly.) So this time, I smile, nod and make do. Thanks again for all of your suggestions. Now off to reading about calculations .... Mickey "Dave Peterson" wrote: You may want to visit Charles Williams' web site: http://www.DecisionModels.com/calcsecrets.htm He has a few tips that may help. But you may want to talk to the boss once more. If you can make your program work via code and Access, then you might be reducing the number of user errors (although the number of developer errors could go up <vbg). But if your code can eliminate much of the grunt work, I would think that the final results would be more, er, trustworthy. Remind him or her that a computer is supposed to make those mundane tasks that can be mechanized much more consistent (and probably lots faster!). mikxtr wrote: Well, here's the catch: no macros. What I'd prefer to do is store the data in an access table and pull only the necessary data through macros. But I've been restricted by my boss from macros (we're trying to minimize user-error), so there it is. My file generates 3 reports, each on a separate worksheet. The data is 68 columns wide & 8000 rows deep (and growing). I inserted a couple of columns to run off the user menu. In other words. the user selects the criteria that s/he wants to see, and these columns look at the codes in the data and return info that determines whether that row of data is something that the report should include. Then I sumif based on those columns. So far, we've decided to make the calculations a little more linear. Instead of 3 sheets calculating off the data sheet, I now have one sheet calculating off the data sheet and the other two sheets calculating off the first sheet. Does this help? Does it even make sense? Many thanks for your previous suggestions. Mickey "Dave Peterson" wrote: Nope. But pivottables aren't made to, either. If your data that creates the pivottable is quite large, then refreshing the pivottable could be slow, too. So you'd be just exchanging one slow tool for a different slow tool. Some options... Teach the users to click anywhere on the pivottable and hit the refresh icon (!) on that pivottable toolbar. If you want to refresh the pivottables on a given sheet (dedicated to the pivottable), you could add some code to the worksheet_activate event to update just those pivottables. Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable Application.EnableEvents = False For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT Application.EnableEvents = True End Sub There are other things you could do, too. You may want to post a little more info to make any response relevant. mikxtr wrote: Maybe. But I need the report dynamic enough to generate different subtotals based on input from the user. Can a pivot table recalculate itself on the fly (e.g. without someone specifically recalculating the table)? If so, how? Thanks, Mickey "Dave Peterson" wrote: Maybe using a pivottable will be quicker. mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
I've never seen excel do that (open workbooks with macros disabled if I choose
to enable them)... But I get your drift with the PHB! mikxtr wrote: Thanks, Dave. I would talk to my boss, but I understand his concern: Files with macros sometimes get opened by others with macros disabled, thus causing issues if your file is macro dependent. I would argue him on this point (like I do daily), but with Excel 2002, I have opened files many times without enabling macros simply because Excel did not prompt me. (And yes, I know about the security level, etc, but it still chooses not to work ... randomly.) So this time, I smile, nod and make do. Thanks again for all of your suggestions. Now off to reading about calculations .... Mickey "Dave Peterson" wrote: You may want to visit Charles Williams' web site: http://www.DecisionModels.com/calcsecrets.htm He has a few tips that may help. But you may want to talk to the boss once more. If you can make your program work via code and Access, then you might be reducing the number of user errors (although the number of developer errors could go up <vbg). But if your code can eliminate much of the grunt work, I would think that the final results would be more, er, trustworthy. Remind him or her that a computer is supposed to make those mundane tasks that can be mechanized much more consistent (and probably lots faster!). mikxtr wrote: Well, here's the catch: no macros. What I'd prefer to do is store the data in an access table and pull only the necessary data through macros. But I've been restricted by my boss from macros (we're trying to minimize user-error), so there it is. My file generates 3 reports, each on a separate worksheet. The data is 68 columns wide & 8000 rows deep (and growing). I inserted a couple of columns to run off the user menu. In other words. the user selects the criteria that s/he wants to see, and these columns look at the codes in the data and return info that determines whether that row of data is something that the report should include. Then I sumif based on those columns. So far, we've decided to make the calculations a little more linear. Instead of 3 sheets calculating off the data sheet, I now have one sheet calculating off the data sheet and the other two sheets calculating off the first sheet. Does this help? Does it even make sense? Many thanks for your previous suggestions. Mickey "Dave Peterson" wrote: Nope. But pivottables aren't made to, either. If your data that creates the pivottable is quite large, then refreshing the pivottable could be slow, too. So you'd be just exchanging one slow tool for a different slow tool. Some options... Teach the users to click anywhere on the pivottable and hit the refresh icon (!) on that pivottable toolbar. If you want to refresh the pivottables on a given sheet (dedicated to the pivottable), you could add some code to the worksheet_activate event to update just those pivottables. Option Explicit Private Sub Worksheet_Activate() Dim myPT As PivotTable Application.EnableEvents = False For Each myPT In Me.PivotTables myPT.RefreshTable Next myPT Application.EnableEvents = True End Sub There are other things you could do, too. You may want to post a little more info to make any response relevant. mikxtr wrote: Maybe. But I need the report dynamic enough to generate different subtotals based on input from the user. Can a pivot table recalculate itself on the fly (e.g. without someone specifically recalculating the table)? If so, how? Thanks, Mickey "Dave Peterson" wrote: Maybe using a pivottable will be quicker. mikxtr wrote: I have a file that is relying upon "sumif" statements to sum a plethora of data. Ok, it may not have too many rows, but rows X columns = slow file. At least, that's what I'm thinking. Does anyone have any suggestions on how to make excel more computationally efficient? Many thanks, Mickey P.S. I'd move it to Access, but then the powers-that-be will never read the report. -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
looping through a set of calculations | Excel Worksheet Functions | |||
Speed up and slow down, the auto-scroll. | Excel Discussion (Misc queries) | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) | |||
Updating calculations automatically when i enter in new data | Excel Discussion (Misc queries) |