Excel Formula Slow

user_4729532 0
Viewed 66

is there anything i can do to further improve performance of this formula ?

=IF($A3<>"",IF(Jan!$E6<>"",LET(d_patt,IF(Jan!$E6<>"",VLOOKUP(Jan!$E6,SETTINGS!$A$12:$B$27,2,FALSE)&IF(Jan!$B6<>"",Jan!$B6,0)&IF(Jan!$C6<>"",Jan!$C6,0)&IF(Jan!$D6<>"",Jan!$D6,0),""),"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&IF(LEFT(d_patt,3)="OVT",d_patt,"OVT000")&IF(LEFT(d_patt,3)="SSI",d_patt,"SSI000")&IF(LEFT(d_patt,3)="SSO",d_patt,"SSO000")&IF(LEFT(d_patt,3)="SDS",d_patt,"SDS000")&IF(LEFT(d_patt,3)="HOL",d_patt,"HOL000")&IF(LEFT(d_patt,3)="LID",d_patt,"LID000")&IF(LEFT(d_patt,3)="UNP",d_patt,"UNP000")&IF(LEFT(d_patt,3)="FLD",d_patt,"FLD000")&IF(LEFT(d_patt,3)="MAT",d_patt,"MAT000")&IF(LEFT(d_patt,3)="LIS",d_patt,"LIS000")&IF(LEFT(d_patt,3)="CBR",d_patt,"CBR000")&IF(LEFT(d_patt,3)="ABS",d_patt,"ABS000")),"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&"OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000"),"")

i have this on a sheet for each day of the year x 400 people so 146k+ times. this is therefor taken up 80% of the sheet load time.

The sheet basically gets shift patters from Teams, check if there is any holidays, overtime etc from the relevant month tab and relevant cell for the day, and then will generate a code like below.

ROT080OVT000SSI000SSO000SDS234HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000

i have so far managed to make this faster by using the LET function, but not show if its possible to make any further improvements.

if you need an example file i can send this or upload somewhere, not sure if that is possible via stackoverflow or a preferred site to upload to.

Thanks

excel
1 Answers

The following is a shortened version of your formula to reduce the complexity of computations.

Instead of building the 'OVT000SSI000...' string one piece after another while always checking if the first 3 characters of 'd_patt' match the current piece, we can set the whole string with '000's as default and only replace the section that matches 'd_patt' (see highlighted elements in screenshot below)

enter image description here

Which results in the full formula:

=IF(LEN($A3)>0,"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&LET(default,"OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000",d_patt,VLOOKUP(Jan!$E6,SETTINGS!$A$12:$B$27,2,FALSE)&IF(LEN(Jan!$B6)>0,Jan!$B6,0)&IF(LEN(Jan!$C6)>0,Jan!$C6,0)&IF(LEN(Jan!$D6)>0,Jan!$D6,0),IF(LEN(d_patt)>0,REPLACE(default,SEARCH(LEFT(d_patt,3),default),6,d_patt),default)),“”)

If $B6, $C6 and $D6 can only be either empty or a numerical number, in other words, if they are never a letter or special character, the 'd_patt' function can be further shortened as follows:

enter image description here

Which results in the full formula:

=IF(LEN($A3)>0,"ROT"&IF(LEN(Teams!$BHR4)>0,MID(Teams!$BHR4,MOD(NETWORKDAYS.INTL(Teams!$C4,I$2,"0000000")-1,LEN(Teams!$BHR4)/3)*3+1,3),"000")&LET(default,"OVT000SSI000SSO000SDS000HOL000LID000UNP000FLD000MAT000LIS000CBR000ABS000",d_patt,VLOOKUP(Jan!$E6,SETTINGS!$A$12:$B$27,2,FALSE)&(Jan!$B6+0)&(Jan!$C6+0)&(Jan!$D6+0),IF(LEN(d_patt)>0,REPLACE(default,SEARCH(LEFT(d_patt,3),default),6,d_patt),default)),“”)
user_171587030
Related