|
Does complicated sumproduct formula crashes excel?
Hi there I have put a template together which is now 1.66mb, the workbook is
huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
Does complicated sumproduct formula crashes excel?
Calculation time for SUMPRODUCT is roughly proportional to the number of
cells involved: in your case each formula uses 65530*4*60= 15.7 million. I am impressed that Excel manages to calculate in 10 minutes. I would suggest that you use less cells by restricting the number of ros and columns being used in the formula to those cells actually containing data (or which are likely to contain data). Or you could use Dynamic Range Names. Alternatively you could probably use Pivot Tables to provide much faster and more flexible reporting. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "vivi" wrote in message ... Hi there I have put a template together which is now 1.66mb, the workbook is huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
Does complicated sumproduct formula crashes excel?
Thanks Charles
This is very useful as it gives me indication of why my excel workbook keeps crashing. To reiterate this - it actually takes 10 mins to calculate 20%..:( The reason for using the max number of rows was due to uncertainty on number of entries. I will try again using smaller ranges like up to 10000 I thought of using pivots, but wasn't sure on how to calcuate varaiances and the formats and presentations of pivot tables are not in accordance with the company's other reports. "Charles Williams" wrote: Calculation time for SUMPRODUCT is roughly proportional to the number of cells involved: in your case each formula uses 65530*4*60= 15.7 million. I am impressed that Excel manages to calculate in 10 minutes. I would suggest that you use less cells by restricting the number of ros and columns being used in the formula to those cells actually containing data (or which are likely to contain data). Or you could use Dynamic Range Names. Alternatively you could probably use Pivot Tables to provide much faster and more flexible reporting. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "vivi" wrote in message ... Hi there I have put a template together which is now 1.66mb, the workbook is huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
Does complicated sumproduct formula crashes excel?
Thanks Don
Just to make sure I understand this as I am not too familiar with this brilliant formulas using offset and counta combining with name ranges: Does that mean for all the references I use in sumproducts I have to replace it by the names ranges? I have to use the offset formula to define the first cell of the range and use counta to find the last cell of the column? and what about colB? is this for the new name range? My data is broke down into 3 sections : Budget 1st section which is Col V:CH , Actual: Col CJ : EV and Forecast is Col EX:HJ Can I still use your suggested forumla, maybe naming the column CJ:EV as Actual ? Bearing in mind the rows depending on how rows the users are filling out and column A will always be filled if data entry is required. =SUMPRODUCT(--($A$38=ColA)*--(B$38=ColB,Actual)/1000 Thanks for all your help, it's very appreciated, finally see a light at the end of tunnel. "Don Guillett" wrote: As Charles said, use the MINIMUM number of rows required. Set up a defined name for your ranges using COUNTA or MATCH or MAX to find the last row. Use within an offset formula insertnamedefinename colAin the refers to box =offset($a$1,0,0,counta($a:$a),1) Now colA will be SELF adjusting with additions or deletions. colB =offset(cola,0,1) -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Charles This is very useful as it gives me indication of why my excel workbook keeps crashing. To reiterate this - it actually takes 10 mins to calculate 20%..:( The reason for using the max number of rows was due to uncertainty on number of entries. I will try again using smaller ranges like up to 10000 I thought of using pivots, but wasn't sure on how to calcuate varaiances and the formats and presentations of pivot tables are not in accordance with the company's other reports. "Charles Williams" wrote: Calculation time for SUMPRODUCT is roughly proportional to the number of cells involved: in your case each formula uses 65530*4*60= 15.7 million. I am impressed that Excel manages to calculate in 10 minutes. I would suggest that you use less cells by restricting the number of ros and columns being used in the formula to those cells actually containing data (or which are likely to contain data). Or you could use Dynamic Range Names. Alternatively you could probably use Pivot Tables to provide much faster and more flexible reporting. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "vivi" wrote in message ... Hi there I have put a template together which is now 1.66mb, the workbook is huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
Does complicated sumproduct formula crashes excel?
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Don Just to make sure I understand this as I am not too familiar with this brilliant formulas using offset and counta combining with name ranges: Does that mean for all the references I use in sumproducts I have to replace it by the names ranges? I have to use the offset formula to define the first cell of the range and use counta to find the last cell of the column? and what about colB? is this for the new name range? My data is broke down into 3 sections : Budget 1st section which is Col V:CH , Actual: Col CJ : EV and Forecast is Col EX:HJ Can I still use your suggested forumla, maybe naming the column CJ:EV as Actual ? Bearing in mind the rows depending on how rows the users are filling out and column A will always be filled if data entry is required. =SUMPRODUCT(--($A$38=ColA)*--(B$38=ColB,Actual)/1000 Thanks for all your help, it's very appreciated, finally see a light at the end of tunnel. "Don Guillett" wrote: As Charles said, use the MINIMUM number of rows required. Set up a defined name for your ranges using COUNTA or MATCH or MAX to find the last row. Use within an offset formula insertnamedefinename colAin the refers to box =offset($a$1,0,0,counta($a:$a),1) Now colA will be SELF adjusting with additions or deletions. colB =offset(cola,0,1) -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Charles This is very useful as it gives me indication of why my excel workbook keeps crashing. To reiterate this - it actually takes 10 mins to calculate 20%..:( The reason for using the max number of rows was due to uncertainty on number of entries. I will try again using smaller ranges like up to 10000 I thought of using pivots, but wasn't sure on how to calcuate varaiances and the formats and presentations of pivot tables are not in accordance with the company's other reports. "Charles Williams" wrote: Calculation time for SUMPRODUCT is roughly proportional to the number of cells involved: in your case each formula uses 65530*4*60= 15.7 million. I am impressed that Excel manages to calculate in 10 minutes. I would suggest that you use less cells by restricting the number of ros and columns being used in the formula to those cells actually containing data (or which are likely to contain data). Or you could use Dynamic Range Names. Alternatively you could probably use Pivot Tables to provide much faster and more flexible reporting. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "vivi" wrote in message ... Hi there I have put a template together which is now 1.66mb, the workbook is huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
Does complicated sumproduct formula crashes excel?
I think I would avoid using OFFSET to define the ranges. I'd use some form
of INDEX. -- Biff Microsoft Excel MVP "Don Guillett" wrote in message ... If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Don Just to make sure I understand this as I am not too familiar with this brilliant formulas using offset and counta combining with name ranges: Does that mean for all the references I use in sumproducts I have to replace it by the names ranges? I have to use the offset formula to define the first cell of the range and use counta to find the last cell of the column? and what about colB? is this for the new name range? My data is broke down into 3 sections : Budget 1st section which is Col V:CH , Actual: Col CJ : EV and Forecast is Col EX:HJ Can I still use your suggested forumla, maybe naming the column CJ:EV as Actual ? Bearing in mind the rows depending on how rows the users are filling out and column A will always be filled if data entry is required. =SUMPRODUCT(--($A$38=ColA)*--(B$38=ColB,Actual)/1000 Thanks for all your help, it's very appreciated, finally see a light at the end of tunnel. "Don Guillett" wrote: As Charles said, use the MINIMUM number of rows required. Set up a defined name for your ranges using COUNTA or MATCH or MAX to find the last row. Use within an offset formula insertnamedefinename colAin the refers to box =offset($a$1,0,0,counta($a:$a),1) Now colA will be SELF adjusting with additions or deletions. colB =offset(cola,0,1) -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Charles This is very useful as it gives me indication of why my excel workbook keeps crashing. To reiterate this - it actually takes 10 mins to calculate 20%..:( The reason for using the max number of rows was due to uncertainty on number of entries. I will try again using smaller ranges like up to 10000 I thought of using pivots, but wasn't sure on how to calcuate varaiances and the formats and presentations of pivot tables are not in accordance with the company's other reports. "Charles Williams" wrote: Calculation time for SUMPRODUCT is roughly proportional to the number of cells involved: in your case each formula uses 65530*4*60= 15.7 million. I am impressed that Excel manages to calculate in 10 minutes. I would suggest that you use less cells by restricting the number of ros and columns being used in the formula to those cells actually containing data (or which are likely to contain data). Or you could use Dynamic Range Names. Alternatively you could probably use Pivot Tables to provide much faster and more flexible reporting. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "vivi" wrote in message ... Hi there I have put a template together which is now 1.66mb, the workbook is huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
Does complicated sumproduct formula crashes excel?
I looked at your workbook and did and editreplaceworkbook65536(AND 65535)
to 100 and replace10000 to 100. Since this is the last row of your entry sheet it seems reasonable. Since you have so many ranges, I am not now recommending defined names. Should you insert rows in the entry sheet the formulas will automatically change. This project is poorly designed and the macros are very clumsy. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your file to my address below along with this msg and a clear explanation of what you want and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Don Just to make sure I understand this as I am not too familiar with this brilliant formulas using offset and counta combining with name ranges: Does that mean for all the references I use in sumproducts I have to replace it by the names ranges? I have to use the offset formula to define the first cell of the range and use counta to find the last cell of the column? and what about colB? is this for the new name range? My data is broke down into 3 sections : Budget 1st section which is Col V:CH , Actual: Col CJ : EV and Forecast is Col EX:HJ Can I still use your suggested forumla, maybe naming the column CJ:EV as Actual ? Bearing in mind the rows depending on how rows the users are filling out and column A will always be filled if data entry is required. =SUMPRODUCT(--($A$38=ColA)*--(B$38=ColB,Actual)/1000 Thanks for all your help, it's very appreciated, finally see a light at the end of tunnel. "Don Guillett" wrote: As Charles said, use the MINIMUM number of rows required. Set up a defined name for your ranges using COUNTA or MATCH or MAX to find the last row. Use within an offset formula insertnamedefinename colAin the refers to box =offset($a$1,0,0,counta($a:$a),1) Now colA will be SELF adjusting with additions or deletions. colB =offset(cola,0,1) -- Don Guillett Microsoft MVP Excel SalesAid Software "vivi" wrote in message ... Thanks Charles This is very useful as it gives me indication of why my excel workbook keeps crashing. To reiterate this - it actually takes 10 mins to calculate 20%..:( The reason for using the max number of rows was due to uncertainty on number of entries. I will try again using smaller ranges like up to 10000 I thought of using pivots, but wasn't sure on how to calcuate varaiances and the formats and presentations of pivot tables are not in accordance with the company's other reports. "Charles Williams" wrote: Calculation time for SUMPRODUCT is roughly proportional to the number of cells involved: in your case each formula uses 65530*4*60= 15.7 million. I am impressed that Excel manages to calculate in 10 minutes. I would suggest that you use less cells by restricting the number of ros and columns being used in the formula to those cells actually containing data (or which are likely to contain data). Or you could use Dynamic Range Names. Alternatively you could probably use Pivot Tables to provide much faster and more flexible reporting. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "vivi" wrote in message ... Hi there I have put a template together which is now 1.66mb, the workbook is huge with one tab "Entry Form" and the other "Project Costs", then there are a few reports based on the calculations in Project Costs. I have monthly budget, actual and forecast for 5 years across the project costs tab, with columns for project stages, workstreams, departments, Expense type and resources name, etc. All these informations are brought into the reporting tabs and i use sumproducts to look for the year, the month, the workstream, expense type and project stage: my formula is as follow: =SUMPRODUCT(--($A$38='Project Costs'!$A7:$A65536)*--($A$2='Project Costs'!$L$7:$L$65536)*--(B$38='Project Costs'!$CJ$6:$EQ$6),'Project Costs'!$CJ$7:$EQ$65536)/1000 This formula is on every report tabs (currently 4) as the project manager wants to see spendings on different project stage and each worksteams under the project stages. It takes over 10 mins to calculate, and sometimes it crashes. What can I do to reduce the time for it to calculate? I've tried the turning off the calculate automatically option, but still when it's refresehed or saved, the problem comes back. Any suggestions? Thanks a lot!! |
| All times are GMT +1. The time now is 11:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com