| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Question calculation time and Index formula
Hi there,
Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
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 -- Brevity is the soul of wit. "Bob Phillips" wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
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 Bob Phillips wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
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! S Davis wrote: 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 Bob Phillips wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
Physical limits? You mean the maximum number of rows/columns?
In pre XL 2007 the limits a 65000 rows and 255 columns In XL 2007: 16000 columns and 1 million+ rows -- Brevity is the soul of wit. "S Davis" wrote: 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! S Davis wrote: 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 Bob Phillips wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
Why don't you totally remove the formula in rows 5:15, 17:29, etc. and only
have them where required? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message oups.com... 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 Bob Phillips wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
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 Bob Phillips wrote: Why don't you totally remove the formula in rows 5:15, 17:29, etc. and only have them where required? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message oups.com... 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 Bob Phillips wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Question calculation time and Index formula
How about writing a VBA macro that will set the values, might well be
faster? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message oups.com... 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 Bob Phillips wrote: Why don't you totally remove the formula in rows 5:15, 17:29, etc. and only have them where required? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message oups.com... 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 Bob Phillips wrote: 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) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "S Davis" wrote in message ups.com... Hi there, Right now I'm using this formula: =INDEX(Last_Inspection_Reported,MATCH(1,(Last_Insp ection_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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extract matching vales | Excel Discussion (Misc queries) | |||
| Add a criteria to an Index and Match formula | Excel Discussion (Misc queries) | |||
| Display the max, then the next down, then the next down, etc. | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Do I need a sumif or sum of a vlookup formula? | Excel Worksheet Functions | |||
Linear Mode
