16. Practice Exercises with Answer Hints
16.2 Module 3: Formulas and Functions
- Total sales of Pune from the mini dataset.
Hint:
=SUMIF(C2:C11,"Pune",G2:G11)→ 544. - Pune sales where Status = Delivered.
Hint:
=SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered")→ 324. - Count delivered orders.
Hint:
=COUNTIF(I2:I11,"Delivered")→ 7. - Label an order "High" if Amount ≥ 500, "Medium" if ≥ 100, else "Low".
Hint:
=IFS(G2>=500,"High",G2>=100,"Medium",TRUE,"Low")(Excel 2019+) or nested IF. - Extract the platform code (BLK/AMN) from the Order ID.
Hint:
=LEFT(A2,3). - Days between order date and today; and the weekday name.
Hint:
=TODAY()-B2;=TEXT(B2,"dddd"). - Weighted average delivery time by Qty.
Hint:
=SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11). - Show "–" instead of #DIV/0! when there are no orders.
Hint:
=IFERROR(A/B,"–").
Ravindra Bagale's Tip
Many students give ranges of different sizes in SUMIFS (G2:G11 and C2:C12) and get #VALUE!. All ranges should be the same size and cover the same rows. Once you get an answer, check 2–3 rows manually – a formula that runs is not necessarily correct.
Ravindra Bagale's Tip – मराठी
बरेच students SUMIFS मध्ये range च्या size वेगवेगळ्या देतात (G2:G11 आणि C2:C12) आणि #VALUE! येतो. सगळ्या ranges same size आणि same rows च्या असाव्यात. उत्तर आल्यावर हाताने 2–3 rows check करा – formula चालला म्हणजे बरोबर असं नाही.
Ravindra Bagale's Tip – हिंदी
बहुत से students SUMIFS में अलग-अलग size की ranges दे देते हैं (G2:G11 और C2:C12) और #VALUE! आता है. सारी ranges same size और same rows की होनी चाहिए. जवाब आने पर हाथ से 2–3 rows check करो – formula चल गया मतलब सही है, ऐसा नहीं.