9.6 LET
LET to name parts of a formula with example — in 30 seconds
What: LET gives names to parts of a formula. Each part is calculated once and the formula reads like a sentence.
Why: Without LET, the same SUMIFS is typed twice: once to test for zero and once to divide. That is slow and easy to break.
Tiny example: Average order value for Pune: s = ₹544, n = 4, so s/n = 136.
LET to name parts of a formula with example — 30 सेकंदात
काय: LET formula च्या भागांना नावं देतो. प्रत्येक भाग एकदाच calculate होतो आणि formula वाक्यासारखा वाचता येतो.
का: LET नसेल तर एकच SUMIFS दोनदा type करावा लागतो: एकदा शून्य तपासायला आणि एकदा भागायला. ते slow आहे आणि सहज तुटतं.
छोटं उदाहरण: Pune ची average order value: s = ₹544, n = 4, म्हणून s/n = 136.
LET to name parts of a formula with example — 30 सेकंड में
क्या: LET formula के हिस्सों को नाम देता है. हर हिस्सा एक बार calculate होता है और formula वाक्य जैसा पढ़ा जाता है.
क्यों: LET के बिना एक ही SUMIFS दो बार type करना पड़ता है: एक बार zero जाँचने और एक बार भाग देने के लिए. यह slow है और आसानी से टूटता है.
छोटा उदाहरण: Pune की average order value: s = ₹544, n = 4, इसलिए s/n = 136.
First look at the data (before → after):
Expected result — Average order value (₹)
| M1 | s | n | M2 = s/n |
|---|---|---|---|
| Pune | 544 | 4 | 136 |
| Nashik | 360 | 2 | 180 |
| Solapur | 1,299 | 1 | 1299 |
About this data: Teaching sample for a fictional shop “Pearl Textiles”. Figures and names (e.g. Neha Shah) are made up for this lesson on let.
Before table and formula as text
Before — Inputs: M1 holds the city
| M1 | Total s | Orders n |
|---|---|---|
| Pune | 544 | 4 |
| Nashik | 360 | 2 |
| Solapur | 1,299 | 1 |
About this data: Demo table only — not real customer or company data. Built to practise let.
Formula / click path
M2: =LET(s, SUMIFS(tblMini[Amount],tblMini[City],M1), n, COUNTIFS(tblMini[City],M1), IF(n=0,"No orders",s/n))
Do it step by step:
- Type Pune in M1.
- In M2, type
=LET(and name the first part:s, SUMIFS(tblMini[Amount],tblMini[City],M1),. - Name the second part:
n, COUNTIFS(tblMini[City],M1),. - Finish with the result:
IF(n=0,"No orders",s/n))and press Enter. - Change M1 to Nashik and then Solapur. Check 180 and 1299.
स्टेप बाय स्टेप करून बघा:
- M1 मध्ये Pune type करा.
- M2 मध्ये
=LET(type करा आणि पहिल्या भागाला नाव द्या:s, SUMIFS(tblMini[Amount],tblMini[City],M1),. - दुसऱ्या भागाला नाव द्या:
n, COUNTIFS(tblMini[City],M1),. - शेवटी result लिहा:
IF(n=0,"No orders",s/n))आणि Enter दाबा. - M1 ला Nashik आणि मग Solapur करा. 180 आणि 1299 check करा.
स्टेप बाय स्टेप करके देखें:
- M1 में Pune type करें.
- M2 में
=LET(type करें और पहले हिस्से को नाम दें:s, SUMIFS(tblMini[Amount],tblMini[City],M1),. - दूसरे हिस्से को नाम दें:
n, COUNTIFS(tblMini[City],M1),. - आख़िर में result लिखें:
IF(n=0,"No orders",s/n))और Enter दबाएँ. - M1 को Nashik और फिर Solapur करें. 180 और 1299 check करें.
=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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of let on a blank sheet for Skyline Clinics.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Sana Sheikh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Yuki Tanaka.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply let to a 5-row made-up Skyline Clinics table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with let.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as SkylineClin_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
