Ravindra BagaleCourses & study guides मराठी Track your progress

16. Practice Exercises with Answer Hints

16.15 Challenge Exercises

By Last updated:

  1. A Diwali-week report (fictional dates 06-11-2026 to 12-11-2026): day-wise sales per city with a spike chart and a slicer for Platform. Hint: Timeline filtered to the week; line chart from pivot.
  2. A single dynamic formula that returns the top 3 products per city. Hint: TAKE(SORT(FILTER(...),2,-1),3) inside a per-city layout or with REDUCE/VSTACK (Microsoft 365 / Excel 2024).
  3. A macro that splits tblOrders into one sheet per city and saves each as a separate workbook. Hint: extend 12.12 C; Worksheets(city).Copy then ActiveWorkbook.SaveAs.
  4. Build the bank-statement folder query of Module 10 for three months and reconcile with a manual total. Hint: From Folder › Combine; compare SUM of each file.
  5. Create a LAMBDA named DELIVERYFEE that returns the slab fee. Hint: Formulas › Name Manager › New: =LAMBDA(amt,XLOOKUP(amt,{0;99;199;499},{30;25;15;0},,-1)) (Microsoft 365 / Excel 2024).

Ravindra Bagale's Tip

Many students give up when they get stuck on the challenge exercises. Break a big question into small steps – do it for one city first, and once it works, for all cities. If you get stuck, look at the hint, but type the answer yourself instead of copying it.

Thodkyaat sangaycha tar (quick recap)

  • Pratyek exercise swatah Excel madhe kara; hint shevti bagha.
  • Formula challa tari 2–3 rows manually check kara.
  • Scenario prashnat aadhi approach, mag formula.

Aata pudhe jaauya – interview questions!

Loading practice tasks...

  1. Total deals won (Easy): Add up Deals won in the "Deals won" column.
  2. Average avg deal value (Easy): Average of the Avg deal value (£) column, rounded to 2 decimals.
  3. Rows in North (Easy): How many rows have Region "North"?
  4. Largest deals won (Easy): The largest value in the "Deals won" column.
  5. Deals won for North (Medium): Total of the Deals won column where Region is "North".
  6. North in London (Medium): Total of the Deals won column where Region is "North" and City is "London".
  7. Best in North (Medium): Largest value in the "Avg deal value (£)" column where "Region" is "North".
  8. Value of row 2 (Medium): Multiply the first-row Deals won by the first-row Avg deal value (£).
  9. Look up Arjun Mehta (Medium): The Deals won for "Arjun Mehta" (use a lookup on the "Name" column).
  10. Total value (Hard): Sum of "Deals won" times "Avg deal value (£)" for all rows.
  11. Share of North (Hard): Share of the "Deals won" column that is "North", rounded to 3 decimals.
  12. How many rows are above the average "Avg deal value (£)" (Hard): Count the rows where "Avg deal value (£)" is above the average "Avg deal value (£)".
  13. Above average? (Hard): "Above" if the "Avg deal value (£)" value in the first row is above the average of the "Avg deal value (£)" column, else "Below".

See all practice tasks in the Task Hub →

Worked example — challenge exercises

Why

Meera Joshi in Pune and Sophie Martin abroad both need a clear, repeatable way to use challenge exercises on a Orbit Logistics sheet without breaking totals.

What

Challenge Exercises is the Excel idea this page teaches. We will walk one tiny example end to end.

How

  1. Open a blank sheet. In A1 type a short label for challenge exercises.
  2. Enter a small made-up value for Meera Joshi using ₹ where money appears.
  3. Add a second value for Sophie Martin using ₹ so you see mixed currencies stay numeric.
  4. Apply the method from this lesson step by step (menu or formula as shown above).
  5. 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).
Result: The demo cell shows the expected result for Orbit Logistics's sample row.
Meaning: Once challenge exercises 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.

Revision card 1: What is Challenge Exercises?
Card 1: What is Challenge Exercises? · Download PNG
Revision card 2: Tiny example — Challenge Exercises
Card 2: Tiny example — Challenge Exercises · Download PNG
Revision card 3: Checklist — Challenge Exercises
Card 3: Checklist — Challenge Exercises · Download PNG