| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation slowdown at the bottom of worksheet
Hi Rick,
AFAIK there is nothing in Excel's calculation engine that slows down beyond 32000 rows, although the Google office desktop com addin does tend to slow Excel down the further down and further across you go. What was the formulae that you were using in the 5th column? regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "rcl2884" wrote in message ... Did microsoft not expect anyone to use the bottom rows of a spreadsheet? I was given a file with about 120,000 records. Normally I would load this into Access for analysis, but I was giving this data to someone not familiar with Access and so I loaded them into Excel 2003. There were only four columns of data and I added a fifth column that included a very simply calculation. Since I am limited by 64K rows, I pasted the rows into two adjacent sets of columns with about 60,000 each. The problem... recalculation takes forever. (20 minutes). I have run into this problem before and suspect that things bog down if you use more than 32K rows. (something to do with memory?) I pasted the data into four sets of adjacent columns and things were much better. Here are some specific times. (Nothing else running on my computer) Recalc with 2 sets of 60K rows: 1240 seconds Recalc with 4 sets of 30K rows: 0.3 seconds VBA macro that does the calc on cell at a time: 9 seconds What gives? Does anyone have an explantion? Are there any way around this log jam, if you actually want to use 60K rows? What happens on Excel 2007 with 1,000K rows? ...Rick |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation slowdown at the bottom of worksheet
Thanks for your interest.
I have had google desktop problems in the past, and so had uninstalled it. My function was something like: Max(Min(A2*B2,$A1) - B$1,0) Very simple.... I repeated this exercise on two different computers with the same results. Again, the exact same number of calculations in four sets of columns and about 30,000 rows was more than 4000 times faster than two sets of columns and 60,000 rows. On May 15, 11:15*am, "Charles Williams" wrote: Hi Rick, AFAIK there is nothing in Excel's calculation engine that slows down beyond 32000 rows, although the Google office desktop com addin does tend to slow Excel down the further down and further across you go. What was the formulae that you were using in the 5th column? regards Charles __________________________________________________ The Excel Calculation Sitehttp://www.decisionmodels.com "rcl2884" wrote in message ... Did microsoft not expect anyone to use the bottom rows of a spreadsheet? I was given a file with about 120,000 records. Normally I would load this into Access for analysis, but I was giving this data to someone not familiar with Access and so I loaded them into Excel 2003. There were only four columns of data and I added a fifth column that included a very simply calculation. Since I am limited by 64K rows, I pasted the rows into two adjacent sets of columns with about 60,000 each. The problem... recalculation takes forever. (20 minutes). I have run into this problem before and suspect that things bog down if you use more than 32K rows. *(something to do with memory?) I pasted the data into four sets of adjacent columns and things were much better. Here are some specific times. *(Nothing else running on my computer) Recalc with 2 sets of 60K rows: *1240 seconds Recalc with 4 sets of 30K rows: * 0.3 seconds VBA macro that does the calc on cell at a time: *9 seconds What gives? Does anyone have an explantion? *Are there any way around this log jam, if you actually want to use 60K rows? *What happens on Excel 2007 with 1,000K rows? ...Rick- Hide quoted text - - Show quoted text - |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation slowdown at the bottom of worksheet
I created 4 columns of 60000 rows of random numbers and added
=Max(Min(A2*B2,$A1) - B$1,0) in column E =MAX(MIN(B2*C2,$A1)-C$1,0) in column F =MAX(MIN(C2*D2,$A1)-D$1,0) in column G then copied the formulae down the 60000 rows. Full Calculation took 0.09 seconds Why is this not the same as your experience? Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "rcl2884" wrote in message ... Thanks for your interest. I have had google desktop problems in the past, and so had uninstalled it. My function was something like: Max(Min(A2*B2,$A1) - B$1,0) Very simple.... I repeated this exercise on two different computers with the same results. Again, the exact same number of calculations in four sets of columns and about 30,000 rows was more than 4000 times faster than two sets of columns and 60,000 rows. On May 15, 11:15 am, "Charles Williams" wrote: Hi Rick, AFAIK there is nothing in Excel's calculation engine that slows down beyond 32000 rows, although the Google office desktop com addin does tend to slow Excel down the further down and further across you go. What was the formulae that you were using in the 5th column? regards Charles __________________________________________________ The Excel Calculation Sitehttp://www.decisionmodels.com "rcl2884" wrote in message ... Did microsoft not expect anyone to use the bottom rows of a spreadsheet? I was given a file with about 120,000 records. Normally I would load this into Access for analysis, but I was giving this data to someone not familiar with Access and so I loaded them into Excel 2003. There were only four columns of data and I added a fifth column that included a very simply calculation. Since I am limited by 64K rows, I pasted the rows into two adjacent sets of columns with about 60,000 each. The problem... recalculation takes forever. (20 minutes). I have run into this problem before and suspect that things bog down if you use more than 32K rows. (something to do with memory?) I pasted the data into four sets of adjacent columns and things were much better. Here are some specific times. (Nothing else running on my computer) Recalc with 2 sets of 60K rows: 1240 seconds Recalc with 4 sets of 30K rows: 0.3 seconds VBA macro that does the calc on cell at a time: 9 seconds What gives? Does anyone have an explantion? Are there any way around this log jam, if you actually want to use 60K rows? What happens on Excel 2007 with 1,000K rows? ...Rick- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation slowdown at the bottom of worksheet
Why is this not the same as your experience?
Boy, I wish I new the answer to that. I tried the spreadsheet on a third computer, running Excel 2000, and found the re-calc almost instant. (0.02 seconds). What version were you running? On May 16, 3:50 am, "Charles Williams" wrote: I created 4 columns of 60000 rows of random numbers and added =Max(Min(A2*B2,$A1) - B$1,0) in column E =MAX(MIN(B2*C2,$A1)-C$1,0) in column F =MAX(MIN(C2*D2,$A1)-D$1,0) in column G then copied the formulae down the 60000 rows. Full Calculation took 0.09 seconds Why is this not the same as your experience? Charles __________________________________________________ TheExcelCalculation Sitehttp://www.decisionmodels.com "rcl2884" wrote in message ... Thanks for your interest. I have had google desktop problems in the past, and so had uninstalled it. My function was something like: Max(Min(A2*B2,$A1) - B$1,0) Very simple.... I repeated this exercise on two different computers with the same results. Again, the exact same number of calculations in four sets of columns and about 30,000 rows was more than 4000 times faster than two sets of columns and 60,000 rows. On May 15, 11:15 am, "Charles Williams" wrote: Hi Rick, AFAIK there is nothing inExcel'scalculation engine that slows down beyond 32000 rows, although the Google office desktop com addin does tend to slowExceldown the further down and further across you go. What was the formulae that you were using in the 5th column? regards Charles __________________________________________________ TheExcelCalculation Sitehttp://www.decisionmodels.com "rcl2884" wrote in message ... Did microsoft not expect anyone to use the bottom rows of a spreadsheet? I was given a file with about 120,000 records. Normally I would load this into Access for analysis, but I was giving this data to someone not familiar with Access and so I loaded them intoExcel 2003. There were only four columns of data and I added a fifth column that included a very simply calculation. Since I am limited by 64K rows, I pasted the rows into two adjacent sets of columns with about 60,000 each. The problem... recalculation takes forever. (20 minutes). I have run into this problem before and suspect that things bog down if you use more than 32K rows. (something to do with memory?) I pasted the data into four sets of adjacent columns and things were much better. Here are some specific times. (Nothing else running on my computer) Recalc with 2 sets of 60K rows: 1240 seconds Recalc with 4 sets of 30K rows: 0.3 seconds VBAmacro that does the calc on cell at a time: 9 seconds What gives? Does anyone have an explantion? Are there any way around this log jam, if you actually want to use 60K rows? What happens on Excel2007 with 1,000K rows? ...Rick- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation slowdown at the bottom of worksheet
I was using Excel 2003.
regards Charles __________________________________________________ The Excel Calculation Site http://www.decisionmodels.com "rcl2884" wrote in message ... Why is this not the same as your experience? Boy, I wish I new the answer to that. I tried the spreadsheet on a third computer, running Excel 2000, and found the re-calc almost instant. (0.02 seconds). What version were you running? On May 16, 3:50 am, "Charles Williams" wrote: I created 4 columns of 60000 rows of random numbers and added =Max(Min(A2*B2,$A1) - B$1,0) in column E =MAX(MIN(B2*C2,$A1)-C$1,0) in column F =MAX(MIN(C2*D2,$A1)-D$1,0) in column G then copied the formulae down the 60000 rows. Full Calculation took 0.09 seconds Why is this not the same as your experience? Charles __________________________________________________ TheExcelCalculation Sitehttp://www.decisionmodels.com "rcl2884" wrote in message ... Thanks for your interest. I have had google desktop problems in the past, and so had uninstalled it. My function was something like: Max(Min(A2*B2,$A1) - B$1,0) Very simple.... I repeated this exercise on two different computers with the same results. Again, the exact same number of calculations in four sets of columns and about 30,000 rows was more than 4000 times faster than two sets of columns and 60,000 rows. On May 15, 11:15 am, "Charles Williams" wrote: Hi Rick, AFAIK there is nothing inExcel'scalculation engine that slows down beyond 32000 rows, although the Google office desktop com addin does tend to slowExceldown the further down and further across you go. What was the formulae that you were using in the 5th column? regards Charles __________________________________________________ TheExcelCalculation Sitehttp://www.decisionmodels.com "rcl2884" wrote in message ... Did microsoft not expect anyone to use the bottom rows of a spreadsheet? I was given a file with about 120,000 records. Normally I would load this into Access for analysis, but I was giving this data to someone not familiar with Access and so I loaded them intoExcel 2003. There were only four columns of data and I added a fifth column that included a very simply calculation. Since I am limited by 64K rows, I pasted the rows into two adjacent sets of columns with about 60,000 each. The problem... recalculation takes forever. (20 minutes). I have run into this problem before and suspect that things bog down if you use more than 32K rows. (something to do with memory?) I pasted the data into four sets of adjacent columns and things were much better. Here are some specific times. (Nothing else running on my computer) Recalc with 2 sets of 60K rows: 1240 seconds Recalc with 4 sets of 30K rows: 0.3 seconds VBAmacro that does the calc on cell at a time: 9 seconds What gives? Does anyone have an explantion? Are there any way around this log jam, if you actually want to use 60K rows? What happens on Excel2007 with 1,000K rows? ...Rick- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculation slowdown | Excel Discussion (Misc queries) | |||
| End (bottom) of worksheet | Excel Worksheet Functions | |||
| Excel Slowdown in Cut and Paste Mode | Excel Discussion (Misc queries) | |||
| Conditional Formating Slowdown | Excel Discussion (Misc queries) | |||
| Add a row to the bottom of a worksheet. | Excel Discussion (Misc queries) | |||
Linear Mode
