3.13 IF, Nested IF and IFS
=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 |
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 |
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.