Ravindra BagaleCourses & study guides

16. Practice Exercises with Answer Hints

16.2 Module 3: Formulas and Functions

By Last updated:

  1. Total sales of Pune from the mini dataset. Hint: =SUMIF(C2:C11,"Pune",G2:G11) → 544.
  2. Pune sales where Status = Delivered. Hint: =SUMIFS(G2:G11,C2:C11,"Pune",I2:I11,"Delivered") → 324.
  3. Count delivered orders. Hint: =COUNTIF(I2:I11,"Delivered") → 7.
  4. 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.
  5. Extract the platform code (BLK/AMN) from the Order ID. Hint: =LEFT(A2,3).
  6. Days between order date and today; and the weekday name. Hint: =TODAY()-B2; =TEXT(B2,"dddd").
  7. Weighted average delivery time by Qty. Hint: =SUMPRODUCT(H2:H11,F2:F11)/SUM(F2:F11).
  8. Show "–" instead of #DIV/0! when there are no orders. Hint: =IFERROR(A/B,"–").

Ravindra Bagale's Tip

Khup students SUMIFS madhe range chya size vegvegalya detat (G2:G11 aani C2:C12) aani #VALUE! yeto. Saglya ranges same size aani same rows chya asavya. Uttar aalyavar manually 2–3 rows check kara – formula challa mhanje barobar asa nahi.