 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / November 2006Question re: calculation time and Index formula
Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last_Inspection_ID="CV"),0)) This is repeated down a list quite a long ways. I only really need this calculation every 12th time to avoid duplicate data, but I can handle leaving it as is if it will slow down the calculation time. My question is this: If I change my formula to: =If($D4=1,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last_Inspection_ID="CV"),0)),"") Will this speed up or slow down the calculation time? Column D contains a repeating series of numbers from 1-12, with each new series containing a new dataset; thus I only need the results once, BUT this is operating from a Query/named range, so the formula needs to be in there for future expansion/retraction of the data, so its not possible to just enter the formula only on the rows containing a "1" in column D. Thanks for your opinions, -Sean It seems to speed it up, presumably (as I guessed you thought it might) because 11 out of 12 rows it will take the default action. This seems to be much faster, put TRUE in D4, D16, etc, and use IF(D4,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last _Inspection_ID="CV"),0)),0)  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Hi there, > > Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last_Insp ection_ID="CV"),0)) > This is repeated down a list quite a long ways. I only really need this > calculation every 12th time to avoid duplicate data, but I can handle > leaving it as is if it will slow down the calculation time. > > My question is this: If I change my formula to: =If($D4=1,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*( Last_Inspection_ID="CV"),0)),"") > Will this speed up or slow down the calculation time? Column D contains > a repeating series of numbers from 1-12, with each new series [quoted text clipped - 6 lines] > Thanks for your opinions, > -Sean If you want to time the calculation length with precision this paper gives a couple of macros: http://msdn2.microsoft.com/en-us/library/aa730921.aspx Dave SignatureBrevity is the soul of wit. > It seems to speed it up, presumably (as I guessed you thought it might) > because 11 out of 12 rows it will take the default action. [quoted text clipped - 30 lines] > > Thanks for your opinions, > > -Sean Yeah, I can see that does speed things up a bit. However, the data this is running from is from a DS7 database, which outputs everything as text. In fact, in my own formula I have to use =if($D4="1"... or else it won't work. I appear to have hit some sort of barrier with the number of formulas in this sheet. Calculation time is absolutely absurd, and I'm at a point where I can not drag down or even copy new formulas into cells. I'm not sure how I'm going to go about this. Might be splitting this into two seperate workbooks with references to each other :( -Sean > It seems to speed it up, presumably (as I guessed you thought it might) > because 11 out of 12 rows it will take the default action. [quoted text clipped - 37 lines] > > Thanks for your opinions, > > -Sean Does anybody know offhand the physical limits for cells in excel? I am at a point where I can no longer drag down any new cells, or even copy them to another location. Deleting previous formulas will let me drag down more formulas equal to the amount that were deleted. Shoot! > Yeah, I can see that does speed things up a bit. However, the data this > is running from is from a DS7 database, which outputs everything as [quoted text clipped - 49 lines] > > > Thanks for your opinions, > > > -Sean Physical limits? You mean the maximum number of rows/columns? In pre XL 2007 the limits are: 65000 rows and 255 columns In XL 2007: 16000 columns and 1 million+ rows SignatureBrevity is the soul of wit. > Does anybody know offhand the physical limits for cells in excel? > [quoted text clipped - 55 lines] > > > > Thanks for your opinions, > > > > -Sean Why don't you totally remove the formula in rows 5:15, 17:29, etc. and only have them where required? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Yeah, I can see that does speed things up a bit. However, the data this > is running from is from a DS7 database, which outputs everything as [quoted text clipped - 12 lines] > > > > This seems to be much faster, put TRUE in D4, D16, etc, and use IF(D4,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last > > _Inspection_ID="CV"),0)),0) > > [quoted text clipped - 8 lines] > > > > > > Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last_Insp > > ection_ID="CV"),0)) > > > [quoted text clipped - 3 lines] > > > > > > My question is this: If I change my formula to: =If($D4=1,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*( > > Last_Inspection_ID="CV"),0)),"") > > > [quoted text clipped - 8 lines] > > > Thanks for your opinions, > > > -Sean I would - this would solve all my problems:) But the formula resides in a column next to a query from a database that automatically expands and retracts as it is refreshed. This creates the problem of requiring 'catch-alls' to account for every situation. I can blank out certain cells or tell the formula to not calculate (like the if="1"), but there does have to be something there. Ive removed all my indexes and starting from scratch with some entirely too simple if statements to create vllookup ranges, hopefully thatll work > Why don't you totally remove the formula in rows 5:15, 17:29, etc. and only > have them where required? [quoted text clipped - 61 lines] > > > > Thanks for your opinions, > > > > -Sean How about writing a VBA macro that will set the values, might well be faster? SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > I would - this would solve all my problems:) But the formula resides in > a column next to a query from a database that automatically expands and [quoted text clipped - 33 lines] > > > > > > > > This seems to be much faster, put TRUE in D4, D16, etc, and use IF(D4,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last > > > > _Inspection_ID="CV"),0)),0) > > > > [quoted text clipped - 8 lines] > > > > > > > > > > Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*(Last_Insp > > > > ection_ID="CV"),0)) > > > > > [quoted text clipped - 4 lines] > > > > > > > > > > My question is this: If I change my formula to: =If($D4=1,INDEX(Last_Inspection_Reported,MATCH(1,(Last_Inspection_Bus=$A4)*( > > > > Last_Inspection_ID="CV"),0)),"") > > > > > [quoted text clipped - 9 lines] > > > > > Thanks for your opinions, > > > > > -Sean |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.