Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1246551%22%20slang%3D%22es-ES%22%3ERe%3A%20Announcing%20LET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1246551%22%20slang%3D%22es-ES%22%3E%3CP%3EGood%20day%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m.%3C%2FA%3E%20%3CBR%20%2F%3EAbout%20the%20LET()%20function.%20%3CBR%20%2F%3EGood%20%3CBR%20%2F%3Emorning.%20About%20the%20LET()%20function.%20Please%20beg%20you%20to%20add%20a%20first%20ShapeName%20argument!%20LET(ShapeName%3B%20Variable%3B%20Value%3B...%3B%20Formula)%20In%20this%20way%20it%20would%20be%20possible%20to%20refer%20to%20it%20in%20other%20formulas%20by%20name%20and%20also%20gives%20an%20essential%20clue%20to%20be%20able%20to%20better%20understand%20what%20this%20formula%20is%20going%20without%20having%20to%20name%20the%20reference.%20This%20would%20make%20it%20%22almost%22%20perfect.%20%E2%80%A2LET(VolumeParalepipeted%3Bx%3B%20VarAncho%3By%3B%20VarLargo%3Bz%3B%20VarAlto%3Bx*y*z)%20%3FLET(x%3B%20VarAncho%3By%3B%20VarLargo%3Bz%3B%20VarAlto%3B%20VolumePiped%3Bx*y*z)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22FuncionLet.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F178859iBBD6DD99AEA46757%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22FuncionLet.png%22%20alt%3D%22FunctionLet.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1252193%22%20slang%3D%22es-ES%22%3ERe%3A%20Announcing%20LET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1252193%22%20slang%3D%22es-ES%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DLET(%0Alength%2C%20L%2C%0Awidth%2C%20W%2C%0Aheight%2C%20H%2C%0Avolume%2C%20length%20*%20width%20*%20height%0A)%20%3C%2FPRE%3E%3CP%3E%3CFONT%3EThe%20last%20parameter%20%22volume%22%20is%20unnecessary%20to%20repeat.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1254166%22%20slang%3D%22es-ES%22%3ERe%3A%20Announcing%20LET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1254166%22%20slang%3D%22es-ES%22%3E%3CP%3EGood%20day.%20%3CBR%20%2F%3EDear%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F595517%22%20target%3D%22_blank%22%3E%40aoki_taichi%2C%3C%2FA%3E%20we%20cannot%20blame%20any%20program%20for%20our%20own%20mistakes%2C%20nor%20pretend%20that%20they%20can%20correct%20them%20for%20us.%20%3CBR%20%2F%3EIf%20when%20we%20create%20a%20formula%20the%20result%20is%20incorrect%2C%20the%20first%20thing%20to%20consider%20is%20the%20possibility%20of%20having%20made%20a%20mistake.%20%3CBR%20%2F%3EThe%20purpose%20of%20the%20LET%20()%20function%20is%20precisely%20that%2C%20to%20be%20able%20to%20see%20in%20a%20transparent%20way%20what%20we%20write%20or%20say%20a%20formula%2C%20and%20detect%20these%20inconsistencies.%20%3CBR%20%2F%3ESo%20my%20suggestion%20would%20help%2C%20because%20declaring%20the%20%22name%22%20of%20the%20calculation%20gives%20us%20a%20much%20clearer%20view.%20%3CBR%20%2F%3EGreetings.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1233572%22%20slang%3D%22en-US%22%3EAnnouncing%20LET%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1233572%22%20slang%3D%22en-US%22%3E%3CP%3EHave%20you%20ever%20had%20to%20repeat%20the%20same%20expression%20multiple%20times%20within%20a%20formula%2C%20created%20a%20%3CEM%3Emega%20formula%3C%2FEM%3E%20or%20wished%20that%20you%20had%20a%20way%20to%20reuse%20portions%20of%20your%20formula%20for%20easier%20consumption%3F%20With%20the%20addition%20of%20the%20LET%20function%2C%20now%20you%20can!%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1384971406%22%20id%3D%22toc-hId--1384971406%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId-1102541427%22%20id%3D%22toc-hId-1102541427%22%3EIntroducing%20LET%3C%2FH2%3E%0A%3CP%3ELET%20allows%20you%20to%20associate%20a%20calculation%20or%20value%20in%20your%20formula%20with%20a%20name.%20It's%20names%20except%20on%20a%20formula%20level.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20main%20benefits%20are%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%201.%20Readability%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ENo%20more%20having%20to%20remember%20what%20a%20specific%20range%2Fcell%20reference%20referred%20to%2C%20what%20your%20calculation%20was%20doing%20or%20duplicating%20the%20same%20expression%20within%20a%20formula.%20With%20the%20ability%20to%20name%20expressions%2C%20you%20can%20give%20meaningful%20context%20to%20readers%20of%20your%20formula.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3CSTRONG%3E2.%20Performance%3C%2FSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EIf%20you%20reuse%20the%20same%20expression%20multiple%20times%20in%20a%20formula%2C%20Excel%20calculates%20that%20expression%20multiple%20times.%20LET%20allows%20you%20to%20name%20the%20expression%20and%20refer%20to%20it%20using%20that%20name.%20Any%20named%20expression%20is%20calculated%20only%20once%2C%20even%20if%20it%20is%20referred%20to%20many%20times%20in%20the%20formula.%20This%20can%20significantly%20improve%20performance%20for%20computationally%20complex%20expressions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20function%20definition%20for%20LET%20works%20as%20follows%3A%3C%2FP%3E%0A%3CP%20style%3D%22font-family%3A%20Courier%20New%3B%22%3E%3CBR%20%2F%3ELET(%3CSPAN%20style%3D%22color%3A%20%234472c4%3B%20font-family%3A%20Courier%20New%3B%22%3Ename1%3C%2FSPAN%3E%2C%20%3CSPAN%20style%3D%22color%3A%20%234472c4%3B%20font-family%3A%20Courier%20New%3B%22%3Evalue1%3C%2FSPAN%3E%2C%20%5B%3CSPAN%20style%3D%22color%3A%20%23ff0000%3B%20font-family%3A%20Courier%20New%3B%22%3Ename2%E2%80%A6%3C%2FSPAN%3E%5D%2C%20%5B%3CSPAN%20style%3D%22color%3A%20%23ff0000%3B%20font-family%3A%20Courier%20New%3B%22%3Evalue2%E2%80%A6%3C%2FSPAN%3E%5D%2C%3CFONT%20color%3D%22%23008000%22%3E%3CSPAN%20style%3D%22color%3A%20%23339966%3B%20font-family%3A%20Courier%20New%3B%22%3E%20calculation%3C%2FSPAN%3E%3C%2FFONT%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSPAN%20style%3D%22color%3A%20%234472c4%3B%20font-family%3A%20Courier%20New%3B%22%3Ename1%3A%3C%2FSPAN%3E%26nbsp%3B%3C%2FFONT%3EThe%20name%20for%20the%201%3CSUP%3Est%3C%2FSUP%3E%20value%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CSPAN%20style%3D%22color%3A%20%234472c4%3B%20font-family%3A%20Courier%20New%3B%22%3Evalue1%3A%3C%2FSPAN%3E%3C%2FFONT%3E%26nbsp%3BThe%20value%20to%20associate%20with%20the%201%3CSUP%3Est%3C%2FSUP%3E%20name%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20color%3D%22%23FF6600%22%3E%3CSPAN%20style%3D%22color%3A%20%23ff0000%3B%20font-family%3A%20Courier%20New%3B%22%3Ename2%20(optional)%3A%3C%2FSPAN%3E%3C%2FFONT%3E%26nbsp%3BAdditional%20names%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20style%3D%22color%3A%20%23ff0000%3B%20font-family%3A%20Courier%20New%3B%22%3Evalue2%20(optional)%3A%20%3C%2FSPAN%3EAdditional%20values%3C%2FLI%3E%0A%3CLI%3E%3CFONT%20color%3D%22%23008000%22%3E%3CSPAN%20style%3D%22color%3A%20%23339966%3B%20font-family%3A%20Courier%20New%3B%22%3Ecalculation%3A%20%3C%2FSPAN%3E%3C%2FFONT%3EThe%20calculation%20to%20perform.%20This%20is%20always%20the%20final%20argument%20and%20it%20can%20refer%20to%20any%20of%20the%20defined%20names%20in%20the%20LET.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EDeconstructing%20the%20parameters%2C%20there%20are%20two%20things%20to%20make%20note%20of%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B1.%20The%20names%20and%20their%20values%20must%20be%20in%20pairs.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%20%26nbsp%3BFor%20example%3A%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLET(total%2C%20SUM(A1%3AA10)%2C%20total%20*%203).%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%20%26nbsp%3BIn%20this%20case%2C%20%3CEM%3Ei%3C%2FEM%3E)%20total%20and%20%3CEM%3Eii%3C%2FEM%3E)%20SUM(A1%3AA10)%20are%20a%20pair.%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-60px%22%3ETaking%20this%20one%20step%20forward%2C%20if%20we%20wanted%20to%20add%20another%20name%2C%20we%20just%20need%20to%20define%20a%20new%20pair...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLET(total%2C%20SUM(A1%3AA10)%2C%20count%2C%20COUNT(A1%3AA10)%2C%20total%20%2F%20count)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20start%3D%222%22%3E%0A%3CLI%3EThe%20last%20parameter%20of%20the%20function%20is%20the%20calculation%20which%20can%20use%20the%20values%20you%20named.%20A%20properly%20structured%20LET%20will%20have%20an%20odd%20number%20of%20arguments.%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%20In%20the%20prior%20cases%20this%20is%20%E2%80%9Ctotal%20*3%E2%80%9D%20or%20%E2%80%9Ctotal%20%2F%20count%E2%80%9D%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--704913036%22%20id%3D%22toc-hId--704913036%22%3EExample%3C%2FH2%3E%0A%3CP%3ESuppose%20you%20have%20some%20raw%20sales%20data%2C%20and%20you'd%20like%20to%20filter%20that%20data%20to%20show%20one%20person%2C%20and%20add%20a%20dash%20to%20any%20blank%20cells.%3C%2FP%3E%0A%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%20class%3D%22lia-align-center%22%3EUnfiltered%20Data%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%20class%3D%22lia-align-center%22%3EFiltered%20Data%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%20width%3D%2250%25%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22LETImage1.png%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F177594iE78345C64E280906%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22LETImage1.png%22%20alt%3D%22LETImage1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FTD%3E%0A%3CTD%20width%3D%2250%25%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22LETImage2.png%22%20style%3D%22width%3A%20369px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F177595i0E22B7202EF102A0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22LETImage2.png%22%20alt%3D%22LETImage2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20formula%20can%20be%20authored%20traditionally%20using%20the%20following%20formula.%20However%2C%20this%20formula%20suffers%20from%20a%20common%20occurrence%20where%20you%20have%20to%20make%20use%20of%20the%20same%20expression%20twice%2C%20in%20this%20case%20it%E2%80%99s%20the%20FILTER%20expression.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20using%20a%20LET%20we%20can%20abstract%20on%20this%20value%20and%20even%20add%20an%20addition%20to%20fix%20up%20the%20criteria%2C%20which%20in%20this%20case%20is%20%E2%80%9CFred%E2%80%9D%20but%20you%20might%20want%20to%20change%20to%20%E2%80%9CAmy%E2%80%9D%20in%20the%20future%20or%20point%20it%20at%20a%20cell%20reference.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20other%20thing%20to%20make%20note%20of%20is%20precedence%20when%20referencing%20names%3B%20Name%20definitions%20can%20%3CSTRONG%3Eonly%3C%2FSTRONG%3E%20make%20use%20of%20prior%20and%20not%20subsequent%20names.%20For%20example%2C%20you%20can%20see%20that%20filterCriteria%20is%20able%20to%20be%20used%20by%20the%20filteredRange%20name%20definition%20because%20it%20comes%20afterwards.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20other%20added%20benefit%20of%20using%20a%20LET%20here%20is%20that%20Excel%20will%20calculate%20this%20formula%202x%20as%20fast%20with%20the%20LET%20because%20Excel%20does%20not%20have%20to%20repeat%20the%20same%20calculation.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EOriginal%20Formula%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22623%22%3E%0A%3CP%3E%3DIF(ISBLANK(%3CFONT%20color%3D%22%23FF0000%22%3EFILTER(A2%3AD8%2CA2%3AA8%3D%3CEM%3E%22Fred%22%3C%2FEM%3E%3C%2FFONT%3E))%2C%22-%22%2C%20%3CFONT%20color%3D%22%23FF0000%22%3EFILTER(A2%3AD8%2CA2%3AA8%3D%22Fred%22%3C%2FFONT%3E))%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EFormula%20using%20LET%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CTABLE%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22623%22%3E%0A%3CP%3E%3DLET(%3CFONT%20color%3D%22%23339966%22%3E%3CSTRONG%3EfilterCriteria%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%20%3CEM%3E%E2%80%9CFred%E2%80%9D%3C%2FEM%3E%2C%20%3CFONT%20color%3D%22%233366FF%22%3E%3CSTRONG%3EfilteredRange%3C%2FSTRONG%3E%3C%2FFONT%3E%2C%20%3CFONT%20color%3D%22%23FF0000%22%3EFILTER(A2%3AD8%2CA2%3AA8%3D%3C%2FFONT%3E%3CFONT%20color%3D%22%23339966%22%3E%3CSTRONG%3EfilterCriteria%3C%2FSTRONG%3E%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF0000%22%3E)%3C%2FFONT%3E%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3BIF(ISBLANK(%3CFONT%20color%3D%22%233366FF%22%3E%3CSTRONG%3EfilteredRange%3C%2FSTRONG%3E%3C%2FFONT%3E)%2C%22-%22%2C%3CFONT%20color%3D%22%233366FF%22%3E%3CSTRONG%3EfilteredRange%3C%2FSTRONG%3E%3C%2FFONT%3E))%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1782599797%22%20id%3D%22toc-hId-1782599797%22%3ELearn%20More%3C%2FH2%3E%0A%3CP%3ETo%20learn%20more%20about%20LET%2C%20please%20check%20out%20our%20help%20article.%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Flet-function-34842dd8-b92b-4d3f-b325-b8b8f9908999%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ELET%20Help%3C%2FA%3E%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--24854666%22%20id%3D%22toc-hId--24854666%22%3E%26nbsp%3B%3C%2FH2%3E%0A%3CH2%20id%3D%22toc-hId--1832309129%22%20id%3D%22toc-hId--1832309129%22%3EAvailability%20Notes%3C%2FH2%3E%0A%3CP%3ELET%20is%20now%20available%20to%26nbsp%3B%3CSTRONG%3EOffice%20365%20Subscribers%20in%20the%20Insiders%20Channel%20%3C%2FSTRONG%3Eand%20will%20be%20available%20to%20users%20of%20other%20channels%20later%20this%20year.%26nbsp%3BI%E2%80%99ll%20update%20this%20blog%20as%20LET%20becomes%20available%20to%20more%20channels.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3ETo%20stay%20connected%20to%20Excel%20and%20its%20community%2C%20read%20the%20Excel%20blog%20posts%20and%20send%20us%20ideas%20and%20suggestions%20via%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexcel.uservoice.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EUserVoice%3C%2FA%3E.%20You%20can%20also%20follow%20Excel%20on%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.facebook.com%2Fmicrosoftexcel%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFacebook%3C%2FA%3E%26nbsp%3Band%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftwitter.com%2Fmsexcel%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ETwitter%3C%2FA%3E.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EChris%20Gross%3CBR%20%2F%3EProgram%20Manager%2C%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1233572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22letlogo.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F177598i50D2FAEE652185EA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22letlogo.png%22%20alt%3D%22letlogo.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EHave%20you%20ever%20had%20to%20repeat%20the%20same%20expression%20multiple%20times%20within%20a%20formula%2C%20created%20a%20%3CEM%3Emega%20formula%3C%2FEM%3E%20or%20wished%20that%20you%20had%20a%20way%20to%20reuse%20portions%20of%20your%20formula%20for%20easier%20consumption%3F%20With%20the%20addition%20of%20the%20LET%20function%2C%20now%20you%20can!%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1233572%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBlog%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the addition of the LET function, now you can!

 

Introducing LET

LET allows you to associate a calculation or value in your formula with a name. It's names except on a formula level.

 

The main benefits are:

 

      1. Readability

No more having to remember what a specific range/cell reference referred to, what your calculation was doing or duplicating the same expression within a formula. With the ability to name expressions, you can give meaningful context to readers of your formula.

 

      2. Performance      

If you reuse the same expression multiple times in a formula, Excel calculates that expression multiple times. LET allows you to name the expression and refer to it using that name. Any named expression is calculated only once, even if it is referred to many times in the formula. This can significantly improve performance for computationally complex expressions.

 

The function definition for LET works as follows:


LET(name1, value1, [name2…], [value2…], calculation)

 

  • name1: The name for the 1st value
  • value1: The value to associate with the 1st name
  • name2 (optional): Additional names
  • value2 (optional): Additional values
  • calculation: The calculation to perform. This is always the final argument and it can refer to any of the defined names in the LET.

Deconstructing the parameters, there are two things to make note of

      1. The names and their values must be in pairs.

   For example: 

 

=LET(total, SUM(A1:A10), total * 3).

 

   In this case, i) total and ii) SUM(A1:A10) are a pair.

 

Taking this one step forward, if we wanted to add another name, we just need to define a new pair...

 

=LET(total, SUM(A1:A10), count, COUNT(A1:A10), total / count)

 

  1. The last parameter of the function is the calculation which can use the values you named. A properly structured LET will have an odd number of arguments.

  In the prior cases this is “total *3” or “total / count” 

 

Example

Suppose you have some raw sales data, and you'd like to filter that data to show one person, and add a dash to any blank cells.

Unfiltered Data Filtered Data
LETImage1.png LETImage2.png

 

This formula can be authored traditionally using the following formula. However, this formula suffers from a common occurrence where you have to make use of the same expression twice, in this case it’s the FILTER expression.

 

By using a LET we can abstract on this value and even add an addition to fix up the criteria, which in this case is “Fred” but you might want to change to “Amy” in the future or point it at a cell reference.

 

The other thing to make note of is precedence when referencing names; Name definitions can only make use of prior and not subsequent names. For example, you can see that filterCriteria is able to be used by the filteredRange name definition because it comes afterwards.

 

An added benefit of using a LET in this formula is that Excel will calculate this formula 2x as fast with the LET because Excel does not have to repeat the same calculation.

 

Original Formula

=IF(ISBLANK(FILTER(A2:D8,A2:A8="Fred")),"-", FILTER(A2:D8,A2:A8="Fred"))

 

Formula using LET

=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),

         IF(ISBLANK(filteredRange),"-",filteredRange))

 

Learn More

To learn more about LET, please check out our help article.

LET Help

 

Availability Notes

LET is now available to Office 365 Subscribers in the Insiders Channel and will be available to users of other channels later this year. I’ll update this blog as LET becomes available to more channels. 

 

In the meantime, please provide feedback either in the comments below or through our normal channels and please note the function signature is subject to change based on feedback before moving to further rings.


To stay connected to Excel and its community, read the Excel blog posts and send us ideas and suggestions via UserVoice. You can also follow Excel on Facebook and Twitter

 

 

Chris Gross
Program Manager, Excel

32 Comments

That is great addition to modern Excel. By the way, with such technique formula formatter is even more demanded

=LET(
   filterCriteria, “Fred”,
   filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),
   IF(ISBLANK(filteredRange),"-",filteredRange)
)

 

Trusted Contributor

Hi

 

I'm not sure if it is such a progress.

If it is a complex formula I would use a helper column.

If it is a test for "equals zero" I would use IFERROR(1/(1/number),value_if_error).

 

Für alle, die die neue Funktion ausprobieren wollen: In der deutschen Excel-Version heißt diese Funktion SEI.

(If you are using a German Excel and want to try the new function: LET has been translated to SEI in the German Excel version.)

Occasional Visitor

I just used inside the filter function with Boolean logic  and it worked like a charm. This is a great addition to the excel formulas 

 

=LET(ZeroValue,ISBLANK($H$18),FILTER(C22:F33,(ZeroValue+($D$22:$D$33=H18))*(ZeroValue+($E$22:$E$33=$J$18))))

Senior Member

@chgross 

 

Brilliant - Similar to Variables in DAX.

The Formula team has show enormous vision in the last few months starting with the release of Dynamic array formulas.

I which some of the vision would rub off on the Power Query and Power Pivot team - which are basically hibernating

 

With a little bit more vision this would be ever more brilliant

 

=LET(filterCriteria, “Fred”, filteredRange, FILTER(A2:D8,A2:A8=filterCriteria),

         IFBLANK(filteredRange),"-"))

 

Oh by the way IFBLANK is already an idea on uservoice

 

Regards

Sam

What a great new function. I just demonstrate how to use the new LET function to model... Check this out in my blood link below

 

https://www.exceljetconsult.com.ng/home/blog/modelling-using-new-let-function/

Occasional Visitor

I have been waiting for this for FOREVER!!! Thank you so, so, so, so very much!!!

Microsoft

@Ute Simon With new functions we go live with an initial translation and then update translations based on community feedback. We've already received some feedback from German language users that they prefer the English LET instead of SEI as this is a programming term. What are your thoughts?

Super Contributor

Interesting; this is what I normally achieve by using a sequence of named formulas.  This has the advantage that, what is in reality a nested formula, appears as a sequence of statements on the worksheet rather than hidden away in name manager.  To be readable it really needs the alt+enter carriage return as @Sergei Baklan suggests.  I wonder whether an alternating pattern of separators might help, e.g.

= LET( 
      salesData := Sales[[Region]:[Profit]],
      modifiedSalesData := IF( ISBLANK(salesData), "-", salesData ),
      FILTER( modifiedSalesData, Sales[Rep]=SelectedRep )
  )

Perhaps that is too drastic since both colon and equals have existing meanings.  A less drastic variation might be to alternate comma and semi-colon as if building an array constant (semi-colon and backslash in mainland Europe).

 

I had a quick practice with the function and came to the conclusion that, for me, the most effective way to build a multi-element formula was to start with a single formula and once that checks out, to name it and echo it

= LET( salesData, Sales[[Region]:[Profit]],
       salesData
   )

so allowing the overall formula to be built and tested one line at a time.

Regular Contributor

Awsome.. just done with my YouTube Video demonstration with 4 different example.

https://www.youtube.com/watch?v=xI2WzMXmUq4 

Keep it up.. Good work team

 

One Suggestion to have different color icon for the variable.

If we have name range with the same variable name with different color will easy to to identify the variable.

2020-03-19_00-12-12.png

Regards, Faraz Shaikh

Senior Member

If D is the name of a Rectangular Range with r Rows and c Columns 

then = INDEX(D,X,Y) converts it into a single column vertical array with  r x c  elements

and UNIQUE(INDEX(D,X,Y)) - would give a list of unique values from a rectangular range

 

where X = 1 + MOD( SEQUENCE( ROWS(D)*COLUMNS(D)) - 1, ROWS(D)) 

and  Y = = 1 + QUOTIENT( SEQUENCE( ROWS(D)*COLUMNS(D)) - 1, ROWS(D) ) 

(X, Y - Inspired from @Peter Bartholomew  Brilliant formula in the comments of  the post announcing XLOOKUP)

 

And Now with the new LET  

=LET(

Num,SEQUENCE( ROWS(D)*COLUMNS(D)) - 1,

Den, ROWS(D), 

X , 1 + MOD(Num,Den),

Y, 1 + QUOTIENT(Num,Den),

INDEX(D,X,Y)

)

Fantastic !!

Sam

Annotation 2020-03-19 143916.png

 

Super Contributor

@excelpbi 

Nice application of LET; it brings the elements of the formula together so that they may be viewed as a single logical entity.

Super Contributor

The example

= LET(filterCriteria, “Fred”,

   filteredRange, FILTER(A2:D8, A2:A8=filterCriteria),

   IF(ISBLANK(filteredRange),"-",filteredRange))

troubles me.

 

It has many features of a programming approach to the solution; the downside being that it makes the process inaccessible to most end users, though professional developers should welcome it.  Yet, at the same time, one sees

FILTER( A2:D8, A2:A8=filterCriteria)

which includes direct cell referencing.  If one is encouraging developers to use a programming style to create solutions, is it a good idea to uses techniques that would be condemned as poor practice in a programming environment?  There, one is expected to declare all variables prior to first usage [Option Explicit if one is thinking VBA].  I advocate eliminating all direct referencing in favour of defined names, in order introduce a level of prior declaration that prevents cells that do not form part of the model from being referenced.

 

What may come back to haunt us is Dan Bricklin's comment on VisiCalc:

'It would be possible to do things the programmer's way, but that would be tedious'

 

 

Contributor

Awesome. Now give us the ability to truly format long Excel formulas similarly to how we can in Power BI for long DAX formulas. Linefeeds and indentation are essential to formulas being readable and editable.

Super Contributor

For any masochist out there, the following works for me

= LET(
   grp, {0;1;2;3},
   N, SUBSTITUTE( TEXT( Convert[@Value], REPT(0,9)&".00" ),".","0"),
   H, VALUE( MID( N, 3*grp+1, 1) ),
   T, VALUE( MID( N, 3*grp+2, 1) ),
   U, VALUE( MID( N, 3*grp+3, 1) ),
   H.txt, IF( H, INDEX( Nums, H+1 ) & " Hundred and ", "" ),
   T.txt,  IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
   U.txt, IF( (T+U),  IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),
   CONCAT( IF( H+T+U, H.txt & T.txt & U.txt & Denom, "" ) )
 )

This evaluates as a relative reference formula but also relies upon some named array constants

Denom	= {" Million, ";" Thousand and ";" Dollars ";" Cents"}
Nums	= {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"}
Teens	= {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}
Tens	= {"","Ten","Twenty"," Thirty"," Forty","Fifty","Sixty"," Seventy","Eighty","Ninety"}

What the formula does is convert Dollar amounts up to $ 1 Billion to text.

 

@Peter Bartholomew , excellent!

Let me combine all together

=LET(
   Denom, {" Million, ";" Thousand ";" Dollars ";" Cents"},
   Nums, {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"},
   Teens, {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
   Tens, {"","Ten","Twenty"," Thirty"," Forty","Fifty","Sixty"," Seventy","Eighty","Ninety"},
   grp, {0;1;2;3},
   LET(
      N, SUBSTITUTE( TEXT( A1, REPT(0,9)&".00" ),".","0"),
      H, VALUE( MID( N, 3*grp+1, 1) ),
      T, VALUE( MID( N, 3*grp+2, 1) ),
      U, VALUE( MID( N, 3*grp+3, 1) ),
      H.txt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),
      T.txt,  IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
      U.txt, IF( (T+U),  IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),
   CONCAT( IF( H+T+U, H.txt & T.txt & U.txt & Denom, "" ) )
   )
)
Super Contributor

@Sergei Baklan 

That takes it a step further!  I must confess, I haven't got as far as thinking through the implications of nested LETs or allowing Names to refer to LET formulas.  The formula (I hesitate to say 'final' in case someone takes it in a new direction) reads more as program module than a normal Excel calculation.

I wonder how many times the text-formatted number N would be calculated were it not for the LET function.

Senior Member

Awesome formulas @Peter Bartholomew  @Sergei Baklan . Much of the VBA code i used to write can now be stripped out with the addition of this one function!

 

It'd be great to be able to define formulas as custom functions eg NumberToText( Value, Currency ) like in PQ and improve the Evaluate formula tool to make it easier to handle longer formulas.

@lori_m , yes, it will be great to have at least more advanced editor and more advanced debugger rather than current formula bar and Formulas Evaluate. I hope functions will be the next step.

Occasional Contributor

Buen Día @lori_m  .
Sobre la función LET().

Buen Día. Sobre la función LET(). ¡Por favor les suplico que añadan un primer argumento NombreDeLaFormula! LET(NombreDeLaFormula;Variable;Valor;...;Formula) De esta forma sería posible hacer referencia a ella en otras fórmulas por su nombre y además da una pista imprescindible para poder entender mejor de que va dicha fórmula sin tener que asignar un nombre a la referencia. Con ello quedaría "casi" perfecta. =LET(VolumenParalelepipedo;x;VarAncho;y;VarLargo;z;VarAlto;x*y*z) =LET(x;VarAncho;y;VarLargo;z;VarAlto;VolumenParalelepipedo;x*y*z)

FuncionLet.png

Senior Member
@srdobrais @Sergei Baklan  Yes, function names together with arguments do seem to be the next logical step however one wants to achieve it - ideally with some extra supporting tools. 
 
One possible method for converting formulas to functions at present is to place inside a RETURN function on a macro sheet substituting range inputs for ARGUMENT definitions (and noting macro sheets use implicit intersection evaluation).  See eg

LetFunction.png

 

 

Microsoft

@lori_m Great discussion though I would strongly advise against relying on Macro Sheets in new workbooks, they are there for backcompat reasons only. Macro Sheets are not supported on all endpoints and we will not be extending them to deal with new Excel constructs.   

Senior Member

@Joe McDaid 

Since Excel 2000 there exists a function called BHATTEXT to convert numbers to words but throw the output in Thai language !!

so = BAHTTEXT(101)  gives  หนึ่งร้อยเอ็ดบาทถ้วน

 

Is it possible to have a similar function to return the output in English, so that we don't have to use VBA to create UDF's / do formula gymnastics with LET... Simply say = TEXTNUMBER(101) = "One Hundred One"

Can this be achieved without submitting an Idea and collecting votes ?

 

Best Regards

Sam

Senior Member
@Joe McDaid  Thanks for the caveats and agree with Sam on TEXTNUMBER, MS Word has something similar too
 
For now perhaps adopting a convention with function arguments first and function definition last would help with readability. So the example given by @srdobrais could be translated to:
=LET(
    length, L,
    width, W,
    height, H,
    volume, length * width * height,
    volume
) 
Occasional Contributor

@lori_m 

=LET(
length, L,
width, W,
height, H,
volume, length * width * height
) 

The last parameter "volume" is unnecessary to repeat.

Occasional Visitor

This LET function is awesome!

However, I am worrying that it might make the debugging harder, since Excel formula does not have "Option Explicit" options. 

 

Please consider to warn the user strongly whenever they are using undefined names.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/40021705...

Occasional Contributor

Good day.
Dear @aoki_taichi , we cannot blame any program for our own mistakes, nor pretend that they can correct them for us.
If when we create a formula the result is incorrect, the first thing to consider is the possibility of having made a mistake.
The purpose of the LET () function is precisely that, to be able to see in a transparent way what we write or say a formula, and detect these inconsistencies.
So my suggestion would help, because declaring the "name" of the calculation gives us a much clearer view.
Greetings.

Super Contributor

I remember first discovering that a named formula could form part of a sequence that would be evaluated by Excel as a multiply nested formula but which could be listed to appear as a program segment.  It took me a while to commit to this process and repackage intermediate array formulas, then appearing in helper ranges, as named formulas.  It seemed almost sinful, flying in the face of all the 'tips and tricks' so earnestly published as the right way to use Excel.  Despite that, it has worked just fine for me over a number of years.

 

Now I have a further choice.  I can strip out all of the intermediate named formulas that I use as building blocks to create solutions and repackage them using LET.  The question is 'how far should I go with this?'  Is 10 lines (19 parameters) OK; what about 20 lines?  When I have created a working solution, how easy would it be to find someone to maintain it?

 

As a further example, I draw from a discussion I created on Chandoo

https://chandoo.org/forum/threads/relative-referencing-is-wrong.39875/#post-261748 , Item#15

= LET(
       dutyTable, SIGN(dataRange="x"),
       m, ROWS(dutyTable),
       n, COLUMNS(dutyTable),
       k, SEQUENCE(m*n),
       RNum, 1+QUOTIENT(k-1, n),
       Cnum, 1+MOD( k-1, n ),
       criterion, INDEX(dutyTable,RNum,Cnum),
       combinations, 
            IF( order, INDEX( employee, RNum), INDEX( weekday, Cnum ) ),
       dutyList, FILTER( combinations, criterion,"null"),
    SORT( dutyList ) 
  )

Order is a range containing {0,1} or {1,0} used to order the columns and so change to effect of SORT.

It is another unpivoting exercise, this time matching employee names against shifts by entering 'x' into a crosstab array.  The output is a list, sorted by employee or by date of the shift.

 

Is this a good use of LET or is it simply taking the idea too far?

Senior Member
Is there already a list of the equivalent function name for other languages? I'd like to test it on my italian Excel 365 version (already configured for insider updates).
Senior Member

@Sergei Baklan Thanks for the link, sadly it's just a translation of the standard help page and states that the function is LET for the italian version too while it isn't. The good news is that in the meantime I managed to get from someone else a workbook that used that function and found out the italian name is DEFINISCI so I've already started playing with it

Trusted Contributor

This is what we really need in a long time!

 

But this is a new indication that Microsoft is actually intending to abandon VBA and deprecated it in favor of Javascript, as they prefer to call this function (Let) instead of (Dim).
 
FYI
Let: is a new Javascript keyword introduced in the (ECMAScript 2015 Update) used to define variables in a block scope, and it's now in Excel used to define variables in formula scope.