Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calc Speed & Formula simplification
Excel2003 ... 1st order of the day? Ask assistance from those intimate with
Excel (at least it started out as the 1st order of the day). As an Excel hack (my Co is actually sending me to an Excel1 class this Monday) ... I often muddle through the Excel Help Screens & these great boards to come up with Formulas that work without much regard for how they work, the order in which they work, or their calculation efficiency ... Definitely, a recognized short-coming on my part ... so my apologies for those times when I fail to use the proper Excel jargon in my post. Above said ... the following is simply beyond my Excel skills. I created a mini template (4 WorkSheets with 1000-2000 records each) and all formulas worked fine, but when I expanded the template to the size I need (4 WorkSheets with 10000-40000 records each) ... CALCULATIONS choked. I have experimented with INDEX/MATCH, SUMPRODUCT, VLOOKUP & SUM(IF ... but have not been able to write to calculate efficiently. Consequently, CALCULATIONS continue to choke. Helper Cols may be an alternative, but since I avoid them like the plague (another short-coming) I need guidance from this board on what best to do ... & I will certainly entertain the use of Helper Cols if this is what is required & if a little instruction is provided. What I have ... 4 WorkSheets (CCs, SWCs, ACTs & BTs): Note: ACTs & BTs have no Formulas ... SWCs pulls in 2 Cols from ACTs ... CCs pulls in & calculates Cols from SWCs & BTs. CCs 10000 records (Range A1:N10000 ... Row 1 = Header) SWCs 40000 records (Range A1:AF40000... Row 1 = Header) ACTs 40000 records (Range A1:G40000 ... Row 1 = Header) BTs 10000 records (Range A1:J10000 ... Row 1 = Header) WorkSheet ... CCs ... (10000 records (Range A1:N10000) ... Row 1 = Header) Formulas in Cols A:M What I think my choking formulas might be ... Looking for options to simply?: Col B ... Range (B3:B10000) ... nice array formula from this board (Ragdyer) pulls in unique records from BTs ... so I do not have to use Advance Auto-Filter ... I am willing to return to use of Advanced Auto-Filter if necessary. {=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,BTs!$C$2:$C$10 000&""),0)),"",INDEX(IF(ISBLANK(BTs!$C$2:$C$10000) ,"",BTs!$C$2:$C$10000),MATCH(0,COUNTIF(B$2:B2,BTs! $C$2:$C$10000&""),0))) } Col C ... Range (C2:C10000): =IF(OR($B2="",ISNA(MATCH(CCs!$B2,BTs!$C$1:$C$10000 ,0))),"",INDEX(BTs!$E$1:$E$10000,MATCH(CCs!$B2,BTs !$C$1:$C$10000,0))) Col D ... Range (D2:D10000) =IF($B2="","",IF(OR(ISNA(MATCH(CCs!$B2,BTs!$C$1:$C $10000,0)),INDEX(BTs!$F$1:$F$10000,MATCH(CCs!$B2,B Ts!$C$1:$C$10000,0))=0),"",INDEX(BTs!$F$1:$F$10000 ,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0)))) Col E ... Range (E2:E10000 ... same as Col C ... different target) Col F ... Range (F2:F10000) =IF($B2="","",COUNTIF(BTs!$C$2:$C$10000,CCs!$B2)) ... (I think ok?) Col G ... Range (G2:G10000) =IF($B2="","",IF(ISERROR(SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<"A"),--(SWCs!$X$2:$X$40000<""),(SWCs!$AB$2:$AB$40000))), "--",SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<"A"),--(SWCs!$X$2:$X$40000<""),(SWCs!$AB$2:$AB$40000)))) Cols H & I ... Cell H2 down & Cell I2 down ... (same as Col G ... different target) WorkSheet ... SWCs ... (40000 records (Range A1:AF40000) ... Row 1 = Header) Formulas in Cols O:AF (excluding Cols Q, T & AA which are Blank) What I think my choking formulas might be ... Looking for options to simply?: Col U ... Range (U2:U40000) =IF(OR($B2="",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000) )=0),"",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000) )) Col V ... Range (V2:V40000) ... (same as Col U ... different target) My "Thanks" to all who support these boards & provide so much magic ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calc Speed & Formula simplification
One thing to bear in mind is that XL2003 maintains a table of up to
64,000 dependencies (i.e. cells which depend on others for their values), so that in a small(ish) workbook a change in one cell will only cause those cells which are dependent to be recalculated. Thus such a "small" workbook can appear to recalculate very quickly. However, where there are more than 64,000 dependent cells, XL will then recalculate the whole workbook, and so large workbooks can appear to recalculate much more slowly. This will happen in your case. Another thing to consider is if you have a formula like: =if(isna(MATCH(...)),"",MATCH(...)) then this is inherrently inefficient, as you are evaluating the MATCH twice - better to put the MATCH part in a helper column to calculate once and then amend your formula to: =if(isna(helper_value),"",helper_value) The helper column can be hidden from view. Your formula in column C is like this, and indeed those in D, E and F - it's only the INDEX part that changes, so you can have something like: =if(isna(helper_value),"",INDEX(...,helper_value,. ..)) A third thing to bear in mind is that array formulae (including SUMPRODUCT) can be very slow for large ranges and many conditions, and in column G you have one with a 40,000 row range and 5 conditions, which you seem to be calculating twice anyway because of the IF. SUMIF is a much faster alternative, and by re-designing your data (eg by having a helper column in SWCs sheet which joins together G2&H2&I2 etc and a similar column in the CCs sheet), you might be able to change the formula to SUMIF. Well, this should give you some pointers - hope it helps. Pete On Oct 12, 4:11 pm, Ken wrote: Excel2003 ... 1st order of the day? Ask assistance from those intimate with Excel (at least it started out as the 1st order of the day). As an Excel hack (my Co is actually sending me to an Excel1 class this Monday) ... I often muddle through the Excel Help Screens & these great boards to come up with Formulas that work without much regard for how they work, the order in which they work, or their calculation efficiency ... Definitely, a recognized short-coming on my part ... so my apologies for those times when I fail to use the proper Excel jargon in my post. Above said ... the following is simply beyond my Excel skills. I created a mini template (4 WorkSheets with 1000-2000 records each) and all formulas worked fine, but when I expanded the template to the size I need (4 WorkSheets with 10000-40000 records each) ... CALCULATIONS choked. I have experimented with INDEX/MATCH, SUMPRODUCT, VLOOKUP & SUM(IF ... but have not been able to write to calculate efficiently. Consequently, CALCULATIONS continue to choke. Helper Cols may be an alternative, but since I avoid them like the plague (another short-coming) I need guidance from this board on what best to do ... & I will certainly entertain the use of Helper Cols if this is what is required & if a little instruction is provided. What I have ... 4 WorkSheets (CCs, SWCs, ACTs & BTs): Note: ACTs & BTs have no Formulas ... SWCs pulls in 2 Cols from ACTs ... CCs pulls in & calculates Cols from SWCs & BTs. CCs 10000 records (Range A1:N10000 ... Row 1 = Header) SWCs 40000 records (Range A1:AF40000... Row 1 = Header) ACTs 40000 records (Range A1:G40000 ... Row 1 = Header) BTs 10000 records (Range A1:J10000 ... Row 1 = Header) WorkSheet ... CCs ... (10000 records (Range A1:N10000) ... Row 1 = Header) Formulas in Cols A:M What I think my "choking" formulas might be ... Looking for options to simply?: Col B ... Range (B3:B10000) ... nice "array" formula from this board (Ragdyer) pulls in "unique" records from BTs ... so I do not have to use "Advance Auto-Filter" ... I am willing to return to use of "Advanced Auto-Filter" if necessary. {=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,BTs!$C$2:$C$10 000&""),0)),"",INDEX(IF(I*SBLANK(BTs!$C$2:$C$10000 ),"",BTs!$C$2:$C$10000),MATCH(0,COUNTIF(B$2:B2,BTs !*$C$2:$C$10000&""),0))) } Col C ... Range (C2:C10000): =IF(OR($B2="",ISNA(MATCH(CCs!$B2,BTs!$C$1:$C$10000 ,0))),"",INDEX(BTs!$E$1:$*E$10000,MATCH(CCs!$B2,BT s!$C$1:$C$10000,0))) Col D ... Range (D2:D10000) =IF($B2="","",IF(OR(ISNA(MATCH(CCs!$B2,BTs!$C$1:$C $10000,0)),INDEX(BTs!$F$1*:$F$10000,MATCH(CCs!$B2, BTs!$C$1:$C$10000,0))=0),"",INDEX(BTs!$F$1:$F$1000 0*,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0)))) Col E ... Range (E2:E10000 ... same as Col C ... different target) Col F ... Range (F2:F10000) =IF($B2="","",COUNTIF(BTs!$C$2:$C$10000,CCs!$B2)) ... (I think ok?) Col G ... Range (G2:G10000) =IF($B2="","",IF(ISERROR(SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!*$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000*<"A"),--(SWCs!$X$2:$X$40000<""),(SWCs!$AB$2:$AB$40000))), "--",SUMPRODUCT(*--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:*$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<"A"),--(SWCs!$X$2:$X$40000<""),(S*WCs!$AB$2:$AB$40000))) ) Cols H & I ... Cell H2 down & Cell I2 down ... (same as Col G ... different target) WorkSheet ... SWCs ... (40000 records (Range A1:AF40000) ... Row 1 = Header) Formulas in Cols O:AF (excluding Cols Q, T & AA which are Blank) What I think my "choking" formulas might be ... Looking for options to simply?: Col U ... Range (U2:U40000) =IF(OR($B2="",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40*000=SWCs!$C2),(ACTs!$E$2:$E$40000 ))=0),"",SUMPRODUCT(--(ACTs!$A$2:$A$40000=*SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000) )) Col V ... Range (V2:V40000) ... (same as Col U ... different target) My "Thanks" to all who support these boards & provide so much "magic" ... Kha |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calc Speed & Formula simplification
Pete ... (Good morning)
Thank you for responding to this post ... Last week I had a class to take & a vaca day to burn (still 18 to burn by end of year) so I am just getting back to you know ... I am reworking this spread sheet to incorporate some of your suggestions. I have been able to take out many of the "check" portions of my formulas ... I have added a certain amount of "Helper Columns" (still reviewing/revising) & I have successfully combined Cols G&H&I all as you recommended. Above said ... this is still a work in process, but I am getting there ... Thank you for the guidance ... Kha "Pete_UK" wrote: One thing to bear in mind is that XL2003 maintains a table of up to 64,000 dependencies (i.e. cells which depend on others for their values), so that in a small(ish) workbook a change in one cell will only cause those cells which are dependent to be recalculated. Thus such a "small" workbook can appear to recalculate very quickly. However, where there are more than 64,000 dependent cells, XL will then recalculate the whole workbook, and so large workbooks can appear to recalculate much more slowly. This will happen in your case. Another thing to consider is if you have a formula like: =if(isna(MATCH(...)),"",MATCH(...)) then this is inherrently inefficient, as you are evaluating the MATCH twice - better to put the MATCH part in a helper column to calculate once and then amend your formula to: =if(isna(helper_value),"",helper_value) The helper column can be hidden from view. Your formula in column C is like this, and indeed those in D, E and F - it's only the INDEX part that changes, so you can have something like: =if(isna(helper_value),"",INDEX(...,helper_value,. ..)) A third thing to bear in mind is that array formulae (including SUMPRODUCT) can be very slow for large ranges and many conditions, and in column G you have one with a 40,000 row range and 5 conditions, which you seem to be calculating twice anyway because of the IF. SUMIF is a much faster alternative, and by re-designing your data (eg by having a helper column in SWCs sheet which joins together G2&H2&I2 etc and a similar column in the CCs sheet), you might be able to change the formula to SUMIF. Well, this should give you some pointers - hope it helps. Pete On Oct 12, 4:11 pm, Ken wrote: Excel2003 ... 1st order of the day? Ask assistance from those intimate with Excel (at least it started out as the 1st order of the day). As an Excel hack (my Co is actually sending me to an Excel1 class this Monday) ... I often muddle through the Excel Help Screens & these great boards to come up with Formulas that work without much regard for how they work, the order in which they work, or their calculation efficiency ... Definitely, a recognized short-coming on my part ... so my apologies for those times when I fail to use the proper Excel jargon in my post. Above said ... the following is simply beyond my Excel skills. I created a mini template (4 WorkSheets with 1000-2000 records each) and all formulas worked fine, but when I expanded the template to the size I need (4 WorkSheets with 10000-40000 records each) ... CALCULATIONS choked. I have experimented with INDEX/MATCH, SUMPRODUCT, VLOOKUP & SUM(IF ... but have not been able to write to calculate efficiently. Consequently, CALCULATIONS continue to choke. Helper Cols may be an alternative, but since I avoid them like the plague (another short-coming) I need guidance from this board on what best to do ... & I will certainly entertain the use of Helper Cols if this is what is required & if a little instruction is provided. What I have ... 4 WorkSheets (CCs, SWCs, ACTs & BTs): Note: ACTs & BTs have no Formulas ... SWCs pulls in 2 Cols from ACTs ... CCs pulls in & calculates Cols from SWCs & BTs. CCs 10000 records (Range A1:N10000 ... Row 1 = Header) SWCs 40000 records (Range A1:AF40000... Row 1 = Header) ACTs 40000 records (Range A1:G40000 ... Row 1 = Header) BTs 10000 records (Range A1:J10000 ... Row 1 = Header) WorkSheet ... CCs ... (10000 records (Range A1:N10000) ... Row 1 = Header) Formulas in Cols A:M What I think my "choking" formulas might be ... Looking for options to simply?: Col B ... Range (B3:B10000) ... nice "array" formula from this board (Ragdyer) pulls in "unique" records from BTs ... so I do not have to use "Advance Auto-Filter" ... I am willing to return to use of "Advanced Auto-Filter" if necessary. {=IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,BTs!$C$2:$C$10 000&""),0)),"",INDEX(IF(I*SBLANK(BTs!$C$2:$C$1000 0),"",BTs!$C$2:$C$10000),MATCH(0,COUNTIF(B$2:B2,BT s!*$C$2:$C$10000&""),0))) } Col C ... Range (C2:C10000): =IF(OR($B2="",ISNA(MATCH(CCs!$B2,BTs!$C$1:$C$10000 ,0))),"",INDEX(BTs!$E$1:$*E$10000,MATCH(CCs!$B2,B Ts!$C$1:$C$10000,0))) Col D ... Range (D2:D10000) =IF($B2="","",IF(OR(ISNA(MATCH(CCs!$B2,BTs!$C$1:$C $10000,0)),INDEX(BTs!$F$1*:$F$10000,MATCH(CCs!$B2 ,BTs!$C$1:$C$10000,0))=0),"",INDEX(BTs!$F$1:$F$100 00*,MATCH(CCs!$B2,BTs!$C$1:$C$10000,0)))) Col E ... Range (E2:E10000 ... same as Col C ... different target) Col F ... Range (F2:F10000) =IF($B2="","",COUNTIF(BTs!$C$2:$C$10000,CCs!$B2)) ... (I think ok?) Col G ... Range (G2:G10000) =IF($B2="","",IF(ISERROR(SUMPRODUCT(--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!*$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000*<"A"),--(SWCs!$X$2:$X$40000<""),(SWCs!$AB$2:$AB$40000))), "--",SUMPRODUCT(*--(SWCs!$G$2:$G$40000=CCs!$A2),--(SWCs!$H$2:$H$40000=CCs!$C2),--(SWCs!$I$2:*$I$40000=CCs!$D2),--(SWCs!$E$2:$E$40000<"A"),--(SWCs!$X$2:$X$40000<""),(S*WCs!$AB$2:$AB$40000)) )) Cols H & I ... Cell H2 down & Cell I2 down ... (same as Col G ... different target) WorkSheet ... SWCs ... (40000 records (Range A1:AF40000) ... Row 1 = Header) Formulas in Cols O:AF (excluding Cols Q, T & AA which are Blank) What I think my "choking" formulas might be ... Looking for options to simply?: Col U ... Range (U2:U40000) =IF(OR($B2="",SUMPRODUCT(--(ACTs!$A$2:$A$40000=SWCs!$B2),--(ACTs!$B$2:$B$40*000=SWCs!$C2),(ACTs!$E$2:$E$4000 0))=0),"",SUMPRODUCT(--(ACTs!$A$2:$A$40000=*SWCs!$B2),--(ACTs!$B$2:$B$40000=SWCs!$C2),(ACTs!$E$2:$E$40000) )) Col V ... Range (V2:V40000) ... (same as Col U ... different target) My "Thanks" to all who support these boards & provide so much "magic" ... Kha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code simplification | Excel Worksheet Functions | |||
Sumproduct and speed of Calc. | Excel Worksheet Functions | |||
Simplification help | Excel Worksheet Functions | |||
Re-calc. speed question | Excel Worksheet Functions | |||
formula simplification | Excel Worksheet Functions |