9.6 LET

By Ravindra Bagale Last updated: 25 September 2026

=LET(name1, value1, [name2, value2, …], calculation) gives names to parts of a formula: easier to read, and each part is calculated once.

Without LET:

=IF(SUMIFS(tblMini[Amount],tblMini[City],M1)=0,"No sales",SUMIFS(tblMini[Amount],tblMini[City],M1)/COUNTIFS(tblMini[City],M1))

With LET:

=LET(city,  M1,
     sales, SUMIFS(tblMini[Amount], tblMini[City], city),
     cnt,   COUNTIFS(tblMini[City], city),
     IF(sales=0, "No sales", sales/cnt))

For M1 = Pune: sales 544, cnt 4 → AOV 136. Use Alt + Enter in the formula bar to put each name on its own line.

Ravindra Bagale's Tip

Motha formula lihitana khup students ekach SUMIFS teen-char vela lihitat – slow pan hota aani ekhadya thikani badal visarla ki chukta. LET madhe ekda nav dya aani punha vapra. Nava chhoti pan arthapurna theva (sales, cnt) – cell address sarkhi nava (A1) chalat nahit.

Practice task

Rewrite the delivery-fee formula and the AOV formula with LET. Add a variable for the GST rate and show the AOV including GST.