3.13 IF, Nested IF and IFS
Quick answer: the short, self-contained version of this lesson is the guide Excel IF Function for Decisions (Pass/Fail, High/Low).
IF and nested IF with example — in 30 seconds
What: IF checks a condition and returns one value if TRUE and another if FALSE. A nested IF puts one IF inside another for three or more bands.
Why: Order bands decide the delivery fee, the offer and the report group. One formula tags every row the same way.
Tiny example: ₹1,299 is Premium, ₹270 is Regular, ₹195 is Small (it is below 200).
IF and nested IF with example — 30 सेकंदात
काय: IF एक condition तपासतो: TRUE असेल तर एक value, FALSE असेल तर दुसरी. तीन किंवा जास्त bands साठी एका IF मध्ये दुसरा IF टाकतात, त्याला nested IF म्हणतात.
का: Order band वरून delivery fee, offer आणि report group ठरतो. एक formula प्रत्येक row ला एकाच पद्धतीने tag करतो.
छोटं उदाहरण: ₹1,299 Premium, ₹270 Regular, ₹195 Small (कारण तो 200 पेक्षा कमी आहे).
IF and nested IF with example — 30 सेकंड में
क्या: IF एक condition जाँचता है: TRUE हो तो एक value, FALSE हो तो दूसरी. तीन या ज़्यादा bands के लिए एक IF के अंदर दूसरा IF डालते हैं, इसे nested IF कहते हैं.
क्यों: Order band से delivery fee, offer और report group तय होता है. एक formula हर row को एक ही तरीके से tag करता है.
छोटा उदाहरण: ₹1,299 Premium, ₹270 Regular, ₹195 Small (क्योंकि यह 200 से कम है).
First look at the data (before → after):
Expected result — Band in column J
| A: Order ID | G: Amount | J: Band |
|---|---|---|
| BLK-1001 | 64 | Small |
| BLK-1002 | 270 | Regular |
| BLK-1007 | 1299 | Premium |
| AMN-1008 | 195 | Small |
About this data: Teaching sample for a fictional shop “NovaCraft Tools”. Figures and names (e.g. Neha Shah) are made up for this lesson on if nested if and ifs.
Before table and formula as text
Before — Amounts to tag
| A: Order ID | G: Amount |
|---|---|
| BLK-1001 | 64 |
| BLK-1002 | 270 |
| BLK-1007 | 1299 |
| AMN-1008 | 195 |
About this data: Demo table only — not real customer or company data. Built to practise if nested if and ifs.
Formula / click path
J2: =IF(G2>=1000,"Premium",IF(G2>=200,"Regular","Small"))
Do it step by step:
- Click J2.
- Type
=IF(G2>=1000,"Premium",IF(G2>=200,"Regular","Small")). - Check the biggest band first (1000), then the next (200). Order matters.
- Press Enter and copy down.
- Test the edge: an amount of exactly 200 must say Regular.
स्टेप बाय स्टेप करून बघा:
- J2 वर click करा.
=IF(G2>=1000,"Premium",IF(G2>=200,"Regular","Small"))type करा.- आधी सगळ्यात मोठा band (1000) तपासा, मग पुढचा (200). क्रम महत्त्वाचा आहे.
- Enter दाबा आणि खाली copy करा.
- Edge test करा: बरोबर 200 amount ला Regular यायला हवं.
स्टेप बाय स्टेप करके देखें:
- J2 पर click करें.
=IF(G2>=1000,"Premium",IF(G2>=200,"Regular","Small"))type करें.- पहले सबसे बड़ा band (1000) जाँचें, फिर अगला (200). क्रम ज़रूरी है.
- Enter दबाएँ और नीचे copy करें.
- Edge test करें: ठीक 200 amount पर Regular आना चाहिए.
=IF(logical_test, value_if_true, [value_if_false])
| Goal | Formula | Row 11 result |
|---|---|---|
| Late or on time | =IF(H2>15,"Late","On time") |
Late (17 mins) |
| Delivery fee: free above ₹199 | =IF(G2>=199,0,25) |
25 |
About this data: Made-up rows so you can copy the pattern for if nested if and ifs. Currencies may use ₹, £, € or $.
Nested IF (एकात एक गुंफलेले IF) – an IF inside another IF – for more than two outcomes:
=IF(G2>=1000,"Premium",IF(G2>=200,"Regular","Small"))
IFS (Excel 2019+) is easier to read: tests are checked in order and the first TRUE wins.
=IFS(G2>=1000,"Premium", G2>=200,"Regular", TRUE,"Small")
| Amount | Result |
|---|---|
| 1,299 | Premium |
| 270 | Regular |
| 90 | Small |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
Ravindra Bagale's Tip
In a nested IF, if the order of conditions is wrong, the result is wrong – many students write G2>=200 first, and then even ₹1,299 gets "Regular". Keep one direction, from the biggest value to the smallest (or the other way round). In IFS, add TRUE at the end; otherwise, when nothing matches, you get #N/A. If you have more than 4–5 levels, use a lookup table (Module 4).
Ravindra Bagale's Tip – मराठी
Nested IF मध्ये conditions ची order चुकली की result चुकतो – बरेच students G2>=200 आधी लिहितात, मग ₹1,299 ला पण "Regular" येतं. मोठ्या value पासून लहानाकडे (किंवा उलट) एकच दिशा ठेवा. IFS मध्ये शेवटी TRUE घाला, नाहीतर काहीच match नसेल तर #N/A येतो. 4-5 पेक्षा जास्त levels असतील तर lookup table (Module 4) वापरा.
Ravindra Bagale's Tip – हिंदी
Nested IF में conditions का order गलत हुआ तो result गलत आता है – बहुत से students G2>=200 पहले लिखते हैं, फिर ₹1,299 पर भी "Regular" आ जाता है. बड़ी value से छोटी की तरफ़ (या उल्टा) एक ही दिशा रखो. IFS में आख़िर में TRUE डालो, वरना कुछ match न हो तो #N/A आता है. 4-5 से ज़्यादा levels हों तो lookup table (Module 4) इस्तेमाल करो.
Practice task
Bonus for store managers: 10% of sales if sales > ₹1,00,000, else 5% (this style of question is reported in interviews – see Module 18). Grade delivery time: ≤ 10 Excellent, ≤ 15 Good, else Needs Improvement.
Loading practice tasks...
- Regular or Small (Easy): If G3 is 200 or more show "Regular", else "Small".
- Late or On time (Easy): If H8 is more than 15 show "Late", else "On time".
- Loan: approve or review (Easy): If Loans!B2 is 750 or more show "Approve", else "Review".
- Delivery fee (Medium): If G2 is 500 or more the fee is 0, else 30.
- Three bands with nested IF (Medium): For G8: 1000 or more = "Premium", 200 or more = "Regular", else "Small".
- Loan: income check (Medium): If Loans!C3 is 50000 or more show "Eligible", else "Not eligible".
- Loan: risk with nested IF (Medium): For Loans!B4: 750 or more = "Low risk", 650 or more = "Medium risk", else "High risk".
- Three bands with IFS (Hard): Same bands for G9 using IFS.
- Status message (Hard): If I5 is "Cancelled" show "Refund", else "OK".
- Loan: risk with IFS (Hard): Same risk bands for Loans!B5 using IFS.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
