Use LET to simplify a long IF formula
=LET(m,(B2-C2)/B2,IF(m<0,"Loss",IF(m<$G$1,"Thin","Healthy")))Excel · result on the sample sheet: 6 rows (D2:D7)
Describe what you need and get a working LET formula for Excel and Google Sheets, tested on real data before you copy it. LET gives names to values or calculations inside a single formula, then uses those names in a final calculation.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=LET(m,(B2-C2)/B2,IF(m<0,"Loss",IF(m<$G$1,"Thin","Healthy")))Excel · result on the sample sheet: 6 rows (D2:D7)
=LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))Excel · result on the sample sheet: 5 rows (F2:F6)
=(B2-MIN($B$2:$B$9))/(MAX($B$2:$B$9)-MIN($B$2:$B$9))Excel · result on the sample sheet: 8 rows (C2:C9)
=LET(n,COUNTIF(C2:C7,"<0"),n-1-INDEX(C2:C7,n)/INDEX(B2:B7,n+1))Excel · result on the sample sheet: 3.31
=LET(days,$G$1-B2,IFS(days<=0,"Current",days<=30,"1-30",days<=60,"31-60",days<=90,"61-90",TRUE,"Over 90"))Excel · result on the sample sheet: 7 rows (D2:D8)
=LET(i,MATCH(E1,A2:A8,1),dLo,INDEX(A2:A8,i),dHi,INDEX(A2:A8,i+1),vLo,INDEX(B2:B8,i),vHi,INDEX(B2:B8,i+1),vLo+(E1-dLo)*(vHi-vLo)/(dHi-dLo))Excel · result on the sample sheet: 262.3
=LET(p,XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6),IF(p="","",p))Excel · result on the sample sheet: 4 rows (F2:F5)
=LET(first,EOMONTH(A2,-1)+1,first+MOD(2-WEEKDAY(first),7))Excel · result on the sample sheet: 6 rows (B2:B7)
You describe the task in plain English, and it writes the formula, using LET where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
LET gives names to values or calculations inside a single formula, then uses those names in a final calculation.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the LET function page has a practice exercise.