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 चल गया मतलब सही है, ऐसा नहीं.
Loading practice tasks...
- Total quantity (Easy): Add up the "Quantity" column.
- Average unit price (Easy): Average of the Unit price ($) column, rounded to 2 decimals.
- Rows in Website (Easy): How many rows have Channel "Website"?
- Largest quantity (Easy): The largest value in the "Quantity" column.
- Quantity for Website (Medium): Total of the Quantity column where Channel is "Website".
- Website in Chicago (Medium): Total of the Quantity column where Channel is "Website" and City is "Chicago".
- Best in Website (Medium): Largest value in the "Unit price ($)" column where "Channel" is "Website".
- Value of row 2 (Medium): Multiply the first-row Quantity by the first-row Unit price ($).
- Look up Ananya Nair (Medium): The Quantity for "Ananya Nair" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Quantity" times "Unit price ($)" for all rows.
- Share of Website (Hard): Share of the "Quantity" column that is "Website", rounded to 3 decimals.
- Share of the total "Unit price ($)" with a locked range (Hard): Find what share of the total "Unit price ($)" the first row has. Round it to 3 decimals.
- Above average? (Hard): "Above" if the "Unit price ($)" value in the first row is above the average of the "Unit price ($)" column, else "Below".
Worked example — module 3 formulas and functions
Why
Neha Shah in Pune and Sophie Martin abroad both need a clear, repeatable way to use module 3 formulas and functions on a CityBasket Retail sheet without breaking totals.
What
Module 3 Formulas And Functions is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for module 3 formulas and functions.
- Enter a small made-up value for Neha Shah using ₹ where money appears.
- Add a second value for Sophie Martin using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once module 3 formulas and functions is correct on one row, copy the same pattern down the column.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
