9.6 LET
=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
When writing a big formula, many students write the same SUMIFS three or four times – it's slower, and if you forget to change it in one place, it goes wrong. In LET, give it a name once and reuse it. Keep names short but meaningful (sales, cnt) – names that look like cell addresses (A1) don't work.
Ravindra Bagale's Tip – मराठी
मोठा formula लिहिताना बरेच students एकच SUMIFS तीन-चार वेळा लिहितात – slow पण होतं आणि एखाद्या ठिकाणी बदल विसरला की चुकतं. LET मध्ये एकदा नाव द्या आणि पुन्हा वापरा. नावं छोटी पण अर्थपूर्ण ठेवा (sales, cnt) – cell address सारखी नावं (A1) चालत नाहीत.
Ravindra Bagale's Tip – हिंदी
बड़ा formula लिखते समय बहुत से students एक ही SUMIFS तीन-चार बार लिखते हैं – slow भी होता है और किसी एक जगह बदलाव भूल गए तो गलत हो जाता है. LET में एक बार नाम दो और दोबारा इस्तेमाल करो. नाम छोटे पर मतलब वाले रखो (sales, cnt) – cell address जैसे नाम (A1) नहीं चलते.
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.