Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
I have a spreadsheet containing 25000 rows of data. For each row, I am
trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
You may want to use countif [=countif($A$2:A2,A2) in cell A2 and
=countif($a$2:A3,A3) in cell A3 and so on]. This is the formula to identify how many dupliate entries are there in a give range, use that as weight, obtain a product and then arrive at weighted average. If this post is helpful, choose yes to close the thread "jday" wrote: I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
A way...
Sort the data by column A. Use the built-in Subtotals utility to sum columns B and C at each name change. Place this formula in column D... =IF(RIGHT(A2,5) = "Total",B2/C2,"") Fill formula down. And another way... Upgrade to XL 2003 from xl 2007 -- Jim Cone Portland, Oregon USA "jday" wrote in message I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
Unfortunately the subtotal insertion won't be practical since I actually have
more than 160 columns of data (I just used columns B/C as an example to simplify my question---assumed I could apply the same formula logic across all of the columns I actually need it for). I have 160 columns of data that need to be 'summed' and another 100 columns that contain calculations using this summed data. I think I'm just gonna have to live with my calculation speed it sounds like! P.S. XL 2003 is an upgrade from xl 2007 ?? Unfortunately my company just upgraded to 2007, so probably will be living with this for awhile! "Jim Cone" wrote: A way... Sort the data by column A. Use the built-in Subtotals utility to sum columns B and C at each name change. Place this formula in column D... =IF(RIGHT(A2,5) = "Total",B2/C2,"") Fill formula down. And another way... Upgrade to XL 2003 from xl 2007 -- Jim Cone Portland, Oregon USA "jday" wrote in message I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
Check Charles William's site for sound advice about Excel performance:
www.decisionmodels.com -- Kind regards, Niek Otten Microsoft MVP - Excel "jday" wrote in message ... Unfortunately the subtotal insertion won't be practical since I actually have more than 160 columns of data (I just used columns B/C as an example to simplify my question---assumed I could apply the same formula logic across all of the columns I actually need it for). I have 160 columns of data that need to be 'summed' and another 100 columns that contain calculations using this summed data. I think I'm just gonna have to live with my calculation speed it sounds like! P.S. XL 2003 is an upgrade from xl 2007 ?? Unfortunately my company just upgraded to 2007, so probably will be living with this for awhile! "Jim Cone" wrote: A way... Sort the data by column A. Use the built-in Subtotals utility to sum columns B and C at each name change. Place this formula in column D... =IF(RIGHT(A2,5) = "Total",B2/C2,"") Fill formula down. And another way... Upgrade to XL 2003 from xl 2007 -- Jim Cone Portland, Oregon USA "jday" wrote in message I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
Explore Pivot Tables...
"jday" wrote: I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
For what it is worth...
The Subtotals feature will sum multiple columns. It will probably do 160 columns. Further, I haven't used it but one new xl2007 function is SumIfs. Maybe it would have some application to your problem. And, xl2007 does much of everything slower; In your case 90 minutes, versus maybe 90 seconds in an earlier version. That's a downgrade in my opinion. -- Jim Cone Portland, Oregon USA "jday" wrote in message Unfortunately the subtotal insertion won't be practical since I actually have more than 160 columns of data (I just used columns B/C as an example to simplify my question---assumed I could apply the same formula logic across all of the columns I actually need it for). I have 160 columns of data that need to be 'summed' and another 100 columns that contain calculations using this summed data. I think I'm just gonna have to live with my calculation speed it sounds like! P.S. XL 2003 is an upgrade from xl 2007 ?? Unfortunately my company just upgraded to 2007, so probably will be living with this for awhile! "Jim Cone" wrote: A way... Sort the data by column A. Use the built-in Subtotals utility to sum columns B and C at each name change. Place this formula in column D... =IF(RIGHT(A2,5) = "Total",B2/C2,"") Fill formula down. And another way... Upgrade to XL 2003 from xl 2007 -- Jim Cone Portland, Oregon USA "jday" wrote in message I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extremely slow recalculation time
Hi jday,
Just wanted to follow up if you are having trouble with the countif formula too? If the post resolved your problem, click 'Yes' to close the thread. "jday" wrote: Unfortunately the subtotal insertion won't be practical since I actually have more than 160 columns of data (I just used columns B/C as an example to simplify my question---assumed I could apply the same formula logic across all of the columns I actually need it for). I have 160 columns of data that need to be 'summed' and another 100 columns that contain calculations using this summed data. I think I'm just gonna have to live with my calculation speed it sounds like! P.S. XL 2003 is an upgrade from xl 2007 ?? Unfortunately my company just upgraded to 2007, so probably will be living with this for awhile! "Jim Cone" wrote: A way... Sort the data by column A. Use the built-in Subtotals utility to sum columns B and C at each name change. Place this formula in column D... =IF(RIGHT(A2,5) = "Total",B2/C2,"") Fill formula down. And another way... Upgrade to XL 2003 from xl 2007 -- Jim Cone Portland, Oregon USA "jday" wrote in message I have a spreadsheet containing 25000 rows of data. For each row, I am trying to calculate a weighted average cost (rate) based upon certain criteria. Here is an example of the calculation that appears in cell D2 (same basic formula is copied down for all rows): =iferror(sumif($A$2:$A$25000,$A2,$B$2:$B$25000)/sumif($A$2:$A$25000,$A2,$C$2:$C$25000),0) Here is an example of what the data in A-C, with the calculated result of my formula in column D: A B C D (calculation) Jones $10000 1,000 $7.714 Jones $ 5000 2,000 $7.714 Smith $25000 3,000 $7.368 Jones $12000 500 $7.714 Smith $30000 4,000 $7.368 Smith $15000 2,500 $7.368 This formula works fine & gives me the result I need, HOWEVER, the way the formula gets populated is thru a macro that copies/pastes the formula down after the "data" (col's A-C) is imported into the worksheet. This copy/paste process takes a LONG time to recalculate for each of the 100 columns that contain a similar formula to what I have in column D. The problem is, I cannot "turn off" the calculation because I need to have the result of some columns before I can calculate others. The, when the macro is finished running, it does a copy/paste values over all of the columns to "lock in" the result. Can anyone provide a suggestion in terms of a different formula or function that can help me achieve a more optimal calculation speed? Currently, it takes nearly 90 minutes to "calculate" this worksheet when the data is imported (based upon 20-25,000 rows of data x 100 columns of SUMIF calculations.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special is extremely slow | Excel Discussion (Misc queries) | |||
Opens extremely slow | Excel Discussion (Misc queries) | |||
Excel extremely slow opening | Setting up and Configuration of Excel | |||
Workbook is now Extremely Slow | Excel Worksheet Functions | |||
Excel extremely slow opening and using | Excel Discussion (Misc queries) |