FREE · PRACTICE IN YOUR BROWSER

Excel Practice Tasks Hub

All practice tasks of all Excel chapters in one place. Pick a task, type your formula, and check your answer.

tasks ready · chapters ready · more tasks coming soon

← Excel course home · Free practice playground: Formula Practice Lab. Your done tasks are saved in this browser. All names, cities and amounts in the tasks are made-up practice data.

All Excel practice tasks by lesson

304 lessons · 3585 tasks. Each task opens inside its lesson, where you type your formula and check it.

Practice: 1.3 Relative, Absolute and Mixed References 1. Excel Basics · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Leeds Medium Total of D2:D11 where B is "North" and C is "Leeds".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Liam O'Brien Medium The Deals won for "Liam O'Brien" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 1.8 Named Ranges 1. Excel Basics · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Bristol Medium Total of D2:D11 where B is "Royal Mail" and C is "Bristol".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ishaan Verma Medium The Parcels for "Ishaan Verma" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 2.1 AutoFill and Fill Series 2. Data Entry Tools · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Pune Medium Total of D2:D11 where B is "Snacks" and C is "Pune".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Units sold for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 2.2 Flash Fill 2. Data Entry Tools · 12 tasks

  1. Total quantity Easy Add up Quantity in D2:D11. (sheet "Data")
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Website Easy How many rows have Channel "Website"? (sheet "Data")
  4. Largest quantity Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Quantity for Website Medium Total of D2:D11 where B is "Website". (sheet "Data")
  10. Website in Denver Medium Total of D2:D11 where B is "Website" and C is "Denver". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: 2.3 Data Validation 2. Data Entry Tools · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: 2.4 Drop-down Lists 2. Data Entry Tools · 11 tasks

  1. VLOOKUP name Easy Name for ID "E103" with VLOOKUP.
  2. XLOOKUP value Easy The Amount for ID "E103".
  3. Position Easy Position of "Chloe Dubois" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Chloe Dubois" using INDEX and MATCH.
  5. Not found message Medium Invoices for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "E103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Chloe Dubois" and column "Invoices".
  11. Lookup the top Hard Name with the largest Invoices.

Practice: 2.5 Dependent Drop-down: City › Area 2. Data Entry Tools · 11 tasks

  1. VLOOKUP name Easy Name for ID "B103" with VLOOKUP.
  2. XLOOKUP value Easy The Rate per night for ID "B103".
  3. Position Easy Position of "Chloe Dubois" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Chloe Dubois" using INDEX and MATCH.
  5. Not found message Medium Nights for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "B103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Chloe Dubois" and column "Nights".
  11. Lookup the top Hard Name with the largest Nights.

Practice: 2.6 Conditional Formatting: Highlight Rules and Top/Bottom 2. Data Entry Tools · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: 2.7 Data Bars, Color Scales and Icon Sets 2. Data Entry Tools · 13 tasks

  1. Total clicks Easy Add up Clicks in D2:D11.
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google Easy How many rows have Platform "Google"?
  4. Largest clicks Easy The largest value in D2:D11.
  5. Clicks for Google Medium Total of D2:D11 where B is "Google".
  6. Google in Paris Medium Total of D2:D11 where B is "Google" and C is "Paris".
  7. Best in Google Medium Largest E2:E11 value where B is "Google".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Clicks for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Google Hard Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 2.8 Formula-based Conditional Formatting 2. Data Entry Tools · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: 2.9 Named Lists for Drop-downs 2. Data Entry Tools · 11 tasks

  1. VLOOKUP name Easy Name for ID "C103" with VLOOKUP.
  2. XLOOKUP value Easy The Price per seat for ID "C103".
  3. Position Easy Position of "Aarav Shah" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Aarav Shah" using INDEX and MATCH.
  5. Not found message Medium Seats for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "C103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Aarav Shah" and column "Seats".
  11. Lookup the top Hard Name with the largest Seats.

Practice: 3.1 Formula Basics and Operators 3. Formulas and Functions · 10 tasks

  1. Price after 10% discount Easy Find the amount of order BLK-1001 (G2) after a 10% discount.
  2. Price of one item Easy Find the price of one item in order BLK-1001. Amount is in G2 and Qty is in F2.
  3. Difference between two orders Easy How much more is order BLK-1002 (G3) than order BLK-1001 (G2)?
  4. Hotel room bill Easy Find the room bill of room 101: Nights (Hotel!C2) × Rate (Hotel!D2).
  5. Hotel bill with extras Easy Find the full bill of room 101: Nights × Rate + Extras (Hotel!E2).
  6. Join city and platform Medium Join C2 and D2 with " - " in between.
  7. Is order 2 bigger? Medium Check if the amount in G3 is greater than the amount in G2. (TRUE or FALSE)
  8. GST on a room Medium Find 18% GST on the room bill of room 201 (Hotel!C4 × Hotel!D4).
  9. 5% discount with brackets Hard Find the amount of order BLK-1007 (G8) after a 5% discount.
  10. Discount, then GST Hard Room 202 gets 10% off its room bill (Hotel!C5 × Hotel!D5). Then 18% GST is added. Find the final amount.

Practice: 3.2 SUM, SUMIF and SUMIFS 3. Formulas and Functions · 10 tasks

  1. Total of all orders Easy Find the total amount of all 10 orders (G2:G11).
  2. Total sales of Pune Easy Find the total amount for Pune. City is in column C.
  3. Bank: total money moved Easy Find the total of Bank!D2:D7.
  4. Bank: total deposits Easy Find the total amount where Type (Bank!C2:C7) is "Deposit".
  5. Total of delivered orders Medium Find the total amount of orders with Status "Delivered" (column I).
  6. Big orders only Medium Find the total of orders above ₹200.
  7. Bank: big transactions Medium Find the total of amounts above ₹10,000 in Bank!D2:D7.
  8. Pune and Delivered Hard Find the total amount for Pune orders that are Delivered.
  9. Blinkit delivered sales Hard Find the total amount of Blinkit orders (column D) that are Delivered.
  10. Bank: Pune deposits Hard Find the total deposits of the Pune branch.

Practice: 3.3 COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS 3. Formulas and Functions · 10 tasks

  1. How many amounts? Easy Count the number cells in G2:G11.
  2. Orders from Pune Easy Count how many orders are from Pune (column C).
  3. Clinic: how many ages? Easy Count the number cells in Clinic!C2:C7.
  4. Clinic: heart patients Easy Count patients in "Cardiology" (Clinic!B2:B7).
  5. Orders without a note Medium Count the empty cells in the Note column J2:J11.
  6. Slow deliveries Medium Count orders with Mins (column H) more than 12.
  7. Clinic: no follow-up Medium Count the empty cells in Clinic!E2:E7.
  8. Pune and Delivered Hard Count Pune orders that are Delivered.
  9. Notes written Hard Count how many orders have a note in J2:J11.
  10. Clinic: senior heart patients Hard Count Cardiology patients older than 60.

Practice: 3.4 AVERAGE, AVERAGEIF(S), MINIFS and MAXIFS 3. Formulas and Functions · 10 tasks

  1. Average delivery time Easy Find the average of Mins in H2:H11.
  2. School: average marks Easy Find the average of School!D2:D7.
  3. Average time in Pune Medium Find the average Mins for Pune orders.
  4. Slowest Pune order Medium Find the highest Mins for Pune orders.
  5. Smallest delivered order Medium Find the smallest amount among Delivered orders.
  6. School: class 10A average Medium Find the average marks of class "10A".
  7. School: top Maths mark Medium Find the highest Maths mark.
  8. School: lowest in 10B Medium Find the lowest mark in class "10B".
  9. Biggest Amazon Now order Hard Find the highest amount for Amazon Now (column D).
  10. Pune delivered average time Hard Find the average Mins for Pune orders that are Delivered.

Practice: 3.5 ROUND Family and RANK 3. Formulas and Functions · 10 tasks

  1. Round the average Easy Find the average amount of G2:G11 and round it to 0 decimals.
  2. Round up boxes Easy Order BLK-1002 is ₹270 (G3). One bag holds ₹7 of items. How many bags? Divide G3 by 7 and round up.
  3. Factory: defect % Easy Find the defect % of Line A: Factory!C2/Factory!B2*100, rounded to 2 decimals.
  4. Factory: boxes needed Easy How many boxes for Line B (Factory!B3)? Divide by 24 and round up.
  5. Round to nearest 10 Medium Round G8 (1299) to the nearest 10.
  6. Nearest 50 Medium Round G5 (240) to the nearest 50.
  7. Factory: full boxes only Medium How many FULL boxes of 24 for Line B (Factory!B3)?
  8. Rank of an order Hard Find the rank of G3 among G2:G11 (biggest = 1).
  9. Fastest delivery rank Hard Find the rank of H7 among H2:H11 where the smallest time is rank 1.
  10. Factory: rank of Line C Hard Find the rank of Line C units (Factory!B4) among Factory!B2:B5 (biggest = 1).

Practice: 3.6 Text Functions: LEFT, RIGHT, MID, LEN, FIND and SEARCH 3. Formulas and Functions · 10 tasks

  1. Platform code Easy Get the first 3 characters of A2.
  2. Order number Easy Get the last 4 characters of A4.
  3. IT: ticket type Easy Get the first 3 characters of IT!A2.
  4. IT: length of the ID Easy Count the characters in IT!A2.
  5. Where is the dash? Medium Find the position of "-" in A2.
  6. Length of a city name Medium Count the characters in C9.
  7. IT: ticket number Medium Get 5 characters from IT!A4 starting at position 5.
  8. Middle part Hard Get 4 characters from A8 starting at position 5.
  9. Text before the dash Hard Get the text before "-" in A4 using LEFT and FIND.
  10. IT: user name Hard Get the text before "@" in IT!B2 using LEFT and FIND.

Practice: 3.7 SUBSTITUTE, REPLACE, TRIM, CLEAN and Case Functions 3. Formulas and Functions · 10 tasks

  1. Fix the name case Easy Make A3 look like Shraddha Bagale.
  2. Remove extra spaces Easy Remove the extra spaces from A5.
  3. Insurance: fix the holder name Easy Make Insurance!A3 look like Ruhi Bagale.
  4. Insurance: small letters Easy Make Insurance!A5 all small letters.
  5. Hide the phone number Medium Replace the first 6 digits of A4 with XXXXXX.
  6. Change a word Medium In A6, change "Aurangabad" to "Sambhaji Nagar".
  7. Insurance: clean policy number Medium Make Insurance!A2 look like POL-77812.
  8. Clean product name Hard Make A2 look like Gokul Cow Milk 500 Ml (no extra spaces, proper case).
  9. Upper case code Hard Make the text in A3 all capitals.
  10. Insurance: change the dash Hard In Insurance!A4 change " - " to ": ".

Practice: 3.8 Joining Text: &, CONCAT, TEXTJOIN and TEXT 3. Formulas and Functions · 10 tasks

  1. ID and city Easy Join A2 and C2 with " - ".
  2. CONCAT two cells Easy Join A3, a space, and C3 with CONCAT.
  3. Courier: route label Easy Join Shipments!B2, " to " and Shipments!C2.
  4. List of cities Medium Join C2:C4 with ", " in between.
  5. Amount with comma Medium Show G8 as 1,299 using TEXT.
  6. Courier: list of AWBs Medium Join Shipments!A2:A4 with ", ".
  7. Courier: weight label Medium Show Shipments!D4 as 0.75 kg.
  8. Full order label Hard Make BLK-1007 - Solapur - ₹1,299 from A8, C8 and G8.
  9. Date as text Hard Show the date in B2 as 02-11-2026 using TEXT.
  10. Courier: SMS line Hard Make: AWB-7001 ships on 10-11-2026 from Shipments!A2 and Shipments!E2.

Practice: 3.9 TEXTBEFORE, TEXTAFTER and TEXTSPLIT 3. Formulas and Functions · 10 tasks

  1. City code Easy Get the text before "-" in A2.
  2. Email user name Easy Get the text before "@" in A3.
  3. UK shop: country code Easy Get the text before "-" in Shop!A2.
  4. UK shop: email domain Easy Get the text after "@" in Shop!A3.
  5. Email domain Medium Get the text after "@" in A3.
  6. First name Medium Get the first name from A4.
  7. UK shop: shoe size Medium Get the text after the last "-" in Shop!A2.
  8. Store number Hard Get the text after the last "-" in A2.
  9. Area name Hard Get Kothrud from A2.
  10. UK shop: product Hard Get SHOE from Shop!A2.

Practice: 3.10 Date Functions: DATE, DAY/MONTH/YEAR, EOMONTH, EDATE 3. Formulas and Functions · 10 tasks

  1. Day of the order Easy Get the day number from B8.
  2. Month number Easy Get the month number from B2.
  3. Office: year applied Easy Get the year from Gov!B2.
  4. Make a date Medium Make the date 15-11-2026 with DATE.
  5. Month end Medium Find the last day of the month of B2.
  6. Office: valid until Medium Find the date Gov!C2 months after Gov!B2.
  7. Office: month end Medium Find the last day of the month of Gov!B4.
  8. One month later Hard Find the date one month after B2.
  9. Month and year label Hard Show B2 as Nov-2026 using TEXT.
  10. Office: licence expiry Hard Find the date Gov!C3 months after Gov!B3 (31 March).

Practice: 3.11 Working Days, DATEDIF and WEEKDAY 3. Formulas and Functions · 10 tasks

  1. Working days in November Easy Count working days (Mon to Fri) from B2 to B3.
  2. Project: working days Easy Count working days from Project!B2 to Project!B3.
  3. Working days without the holiday Medium Count working days from B2 to B3, leaving out the holiday in B5.
  4. 10 working days later Medium Find the date 10 working days after B2.
  5. Day of the week Medium Find the weekday number of B4 (Monday = 1).
  6. Project: minus the holiday Medium Count working days from Project!B2 to Project!B3, leaving out Project!B4.
  7. Project: 5 working days later Medium Find the date 5 working days after Project!B2.
  8. Project: holiday weekday Medium Find the weekday number of Project!B4 (Monday = 1).
  9. Days between dates Hard Count the days from B2 to B3 with DATEDIF.
  10. Project: contract months Hard Count full months from Project!B5 to Project!B3.

Practice: 3.12 Time Calculations 3. Formulas and Functions · 10 tasks

  1. Delivery minutes Easy Find the minutes between B2 and C2.
  2. Hour of the order Easy Get the hour from B2.
  3. Calls: login minute Easy Get the minutes from Calls!B4.
  4. Calls: day shift hours Easy Find Ravina's hours from Calls!B2 to Calls!C2.
  5. Make a time Medium Make the time 19:42 with TIME.
  6. Past midnight Medium Find the minutes from B3 to C3.
  7. Calls: night shift hours Medium Find Salman's hours from Calls!B3 to Calls!C3.
  8. Shift hours Hard Find Raja's shift hours from B4 to C4.
  9. Shift pay Hard Find Raja's pay: shift hours times B5.
  10. Calls: shift pay Hard Find Amir's pay: hours from Calls!B4 to Calls!C4 times Calls!B5.

Practice: 3.13 IF, Nested IF and IFS 3. Formulas and Functions · 10 tasks

  1. Regular or Small Easy If G3 is 200 or more show "Regular", else "Small".
  2. Late or On time Easy If H8 is more than 15 show "Late", else "On time".
  3. Loan: approve or review Easy If Loans!B2 is 750 or more show "Approve", else "Review".
  4. Delivery fee Medium If G2 is 500 or more the fee is 0, else 30.
  5. Three bands with nested IF Medium For G8: 1000 or more = "Premium", 200 or more = "Regular", else "Small".
  6. Loan: income check Medium If Loans!C3 is 50000 or more show "Eligible", else "Not eligible".
  7. Loan: risk with nested IF Medium For Loans!B4: 750 or more = "Low risk", 650 or more = "Medium risk", else "High risk".
  8. Three bands with IFS Hard Same bands for G9 using IFS.
  9. Status message Hard If I5 is "Cancelled" show "Refund", else "OK".
  10. Loan: risk with IFS Hard Same risk bands for Loans!B5 using IFS.

Practice: 3.14 AND, OR, NOT and SWITCH 3. Formulas and Functions · 10 tasks

  1. Fast Pune order Easy Check if C7 is "Pune" AND H7 is 10 or less.
  2. Lost order Easy Check if I9 is "Cancelled" OR "Returned".
  3. Claim: fast track Easy Check if Claims!D2 is 7 or less AND Claims!E2 is "Yes".
  4. Claim: needs a check Easy Check if Claims!C3 is more than 100000 OR Claims!D3 is more than 7.
  5. Not delivered Medium Check that I5 is NOT "Delivered".
  6. Hero tag Medium If C7 is "Pune" and H7 is 10 or less show "Pune 10-min hero", else "".
  7. Claim: papers missing Medium Check that Claims!E3 is NOT "Yes".
  8. Platform name from code Hard Look at the first 3 letters of A4. "BLK" means "Blinkit", "AMN" means "Amazon Now". Anything else is "Unknown".
  9. City code Hard Use SWITCH on C3: "Pune" = "PUN", "Nashik" = "NSK", else "Other".
  10. Claim: type name Hard Use SWITCH on Claims!B5: "HLT" = "Health", "MTR" = "Motor", "TRV" = "Travel", else "Other".

Practice: 3.15 IFERROR and IFNA 3. Formulas and Functions · 10 tasks

  1. Unit price Easy Find the price of one item for row 2 (B2/C2).
  2. Shop: price per pack Easy Find Retail!B2/Retail!C2.
  3. Hide the error Medium For row 3, show "Check Qty" if B3/C3 gives an error.
  4. No error case Medium Use the same IFERROR formula for row 4.
  5. Zero instead of error Medium For row 3, show 0 if B3/C3 gives an error.
  6. Shop: no sale message Medium For row 3, show "No sale" if Retail!B3/Retail!C3 is an error.
  7. Shop: zero instead Medium For row 3, show 0 if Retail!B3/Retail!C3 is an error.
  8. Shop: normal row Medium Divide Retail B4 by Retail C4. If it gives an error, show "No sale".
  9. Not found message Hard Look up "BLK-1099" in A2:C4 (column 2). Show "Not found" if it is missing.
  10. Shop: item not stocked Hard Look up "Ghee" in Retail!A2:C5 (column 2). Show "Not stocked" if missing.

Practice: 3.16 SUMPRODUCT 3. Formulas and Functions · 10 tasks

  1. Total qty of Pune Easy Find the total Qty for Pune.
  2. Invoice: bill total Easy Find the total bill: Invoice!C2:C6 times Invoice!D2:D6.
  3. Delivered amount Medium Find the total amount of Delivered orders with SUMPRODUCT.
  4. Count with two conditions Medium Count Pune orders that are Delivered with SUMPRODUCT.
  5. Invoice: Dairy total Medium Find the bill total of "Dairy" items only.
  6. Invoice: count rows Medium Count Stationery rows where Qty is more than 3.
  7. Blinkit qty Hard Find the total Qty of Blinkit orders.
  8. Weighted average time Hard Find the average Mins weighted by Qty.
  9. Invoice: Stationery qty Hard Find the total Qty of Stationery.
  10. Invoice: average price Hard Find the average price per unit: total bill ÷ total Qty.

Practice: 3.17 Error Types and How to Fix Them 3. Formulas and Functions · 10 tasks

  1. Fix #NAME? Easy The broken formula is =SUMM(B2:B4). Write the correct one.
  2. Store: fix #NAME? Easy =AVERAG(Stock!B2:B4) gives #NAME?. Write the correct formula.
  3. Fix #DIV/0! Medium =B3/C3 gives #DIV/0!. Show "No qty" when C3 is 0, else B3/C3.
  4. Fix #VALUE! Medium =D2*2 gives #VALUE!. Remove the ₹ and turn it into a number, then multiply by 2.
  5. Store: fix #DIV/0! Medium =Stock!B3/Stock!C3 gives #DIV/0!. Show "Not used" instead.
  6. Store: fix #VALUE! Medium =Stock!D2/Stock!C2 gives a wrong answer or error. Remove the comma, make it a number, then divide by Stock!C2.
  7. Fix #N/A from a space Hard Look up E2 in A2:C4 and return the Amount (column 2). Use TRIM to fix the space.
  8. Find the hidden space Hard Count the characters in E2.
  9. Store: fix #N/A Hard Look up Stock!E2 in Stock!A2:C4 and return Stock (column 2). Use TRIM.
  10. Store: see the space Hard Count the characters in Stock!E2.

Practice: Practice and Answer Key: SUMIFS, COUNTIFS and IF 3. Formulas and Functions · 10 tasks

  1. Kolhapur delivered sales Easy Find the total Amount for Kolhapur orders that are Delivered.
  2. Solapur delivered sales Easy Find the total Amount for Solapur orders that are Delivered.
  3. Institute: fees received Easy Find the total Fee where Paid (Fees!E2:E6) is "Yes".
  4. Solapur delivered count Medium Count Solapur orders that are Delivered.
  5. Late orders Medium Count orders with Mins (E) more than 15.
  6. Institute: unpaid in Pune Medium Count Pune students who have not paid.
  7. Institute: Excel fees paid Medium Find the total paid fees of the "Excel" course.
  8. Kolhapur average time Hard Find the average Mins for Kolhapur.
  9. Late tag Hard For row 3, show "Late" if E3 is more than 15, else "On time".
  10. Institute: reminder tag Hard For row 6, show "Send reminder" if Fees!E6 is "No", else "Paid".

Practice: 4.1 VLOOKUP with Exact Match 4. Lookup Functions · 10 tasks

  1. City of a store Easy Find the City (column 3) of BLK-NSK-01.
  2. Manager of a store Easy Find the City Manager of AMN-NGP-01.
  3. Pharmacy: medicine name Easy Find the Medicine (column 2) of MED-102.
  4. Pharmacy: price Easy Find the price of MED-104.
  5. Use a cell Medium Find the Area (column 4) of the store in H2.
  6. Platform of a store Medium Find the Platform of AMN-SLP-01.
  7. Pharmacy: stock Medium Find the Stock of MED-103.
  8. Pharmacy: pack size Medium Find the Pack of MED-101.
  9. Year opened Hard Find the year (column 6) the store in H2 opened.
  10. Pharmacy: bill for 3 packs Hard Find the cost of 3 packs of MED-101.

Practice: 4.2 VLOOKUP Approximate Match and the Column-Index Trap 4. Lookup Functions · 10 tasks

  1. Manager with column 5 Easy Find the City Manager of AMN-NGP-01.
  2. FD: rate for 100 days Easy Find the FD rate for 100 days.
  3. FD: rate for 365 days Easy Find the FD rate for 365 days.
  4. Find the column number Medium Find the position of "City Manager" in A1:F1.
  5. Fee for ₹150 Medium Find the fee for an order of ₹150 from H2:I5.
  6. FD: rate for 500 days Medium Find the FD rate for 500 days.
  7. FD: rate for 1000 days Medium Find the FD rate for 1000 days.
  8. Safe lookup Hard Find the City Manager of BLK-KOP-01, using MATCH for the column number.
  9. Fee for ₹1,299 Hard Find the fee for ₹1299.
  10. FD: safe column number Hard Find the rate for 200 days. Find the column number by looking for the heading "FD rate %" in row 1.

Practice: 4.3 HLOOKUP 4. Lookup Functions · 10 tasks

  1. Pune target in Sep Easy Find Pune's target for "Sep".
  2. Pune target in Aug Easy Find Pune's target for "Aug".
  3. Plants: Nashik in Q2 Easy Find the Nashik plant units for "Q2".
  4. Plants: Sambhaji Nagar in Q4 Easy Find the Sambhaji Nagar plant units for "Q4".
  5. Nashik target in Nov Medium Find Nashik's target for "Nov".
  6. Month from a cell Medium Find Pune's target for the month in G1.
  7. Plants: Sambhaji Nagar in Q1 Medium Find the Sambhaji Nagar plant units for "Q1".
  8. Plants: Nashik in Q3 Medium Find the Nashik plant units for "Q3".
  9. Total of two months Hard Find Pune's Sep target plus Oct target.
  10. Plants: first half Hard Find the Nashik plant units for Q1 plus Q2.

Practice: 4.4 INDEX and MATCH 4. Lookup Functions · 10 tasks

  1. Row of a store Easy Find the position of "BLK-KOP-01" in A2:A8.
  2. Value by position Easy Get the 5th value of E2:E8.
  3. Results: position of Ravina Easy Find the position of "Ravina" in Students!B2:B5.
  4. Results: 2nd mark Easy Get the 2nd value of Students!D2:D5.
  5. INDEX and MATCH together Medium Find the City Manager of the store in H2.
  6. City of a store Medium Find the City of BLK-NSK-01.
  7. Results: marks of Salman Medium Find the Marks of "Salman".
  8. Results: class of Shahrukh Medium Find the Class of "Shahrukh".
  9. Look to the left Hard Find the Store ID of the manager "Raja".
  10. Results: roll no of the 85 scorer Hard Find the Roll No of the student with 85 marks.

Practice: 4.5 Two-way Lookup (Row and Column) 4. Lookup Functions · 10 tasks

  1. Row of a city Easy Find the position of "Nagpur" in A2:A4.
  2. Column of a month Easy Find the position of "Nov" in B1:D1.
  3. Resort: row of Suite Easy Find the position of "Suite" in Rates!A2:A4.
  4. Resort: column of Festival Easy Find the position of "Festival" in Rates!B1:D1.
  5. Row and column number Medium Get row 1, column 3 of B2:D4.
  6. Resort: row 2, column 2 Medium Get row 2, column 2 of Rates!B2:D4.
  7. Two-way lookup Hard Find sales for the city in G1 and month in G2.
  8. Nagpur in Sep Hard Find sales for "Nagpur" in "Sep".
  9. Resort: Deluxe at festival Hard Find the price of "Deluxe" in "Festival".
  10. Resort: 2 nights in a Suite Hard Find the cost of 2 nights in a "Suite" in "Peak season".

Practice: 4.6 XLOOKUP Basics and if_not_found 4. Lookup Functions · 10 tasks

  1. City of a store Easy Find the City of BLK-NSK-01.
  2. Manager from a cell Easy Find the City Manager of the store in H2.
  3. Staff: team of E-1003 Easy Find the Team of E-1003.
  4. Not in master Medium Find the manager of H3. If not found show "Not in master".
  5. Area of a store Medium Find the Area of AMN-SLP-01.
  6. Staff: laptop of E-1002 Medium Find the Laptop of E-1002.
  7. Staff: unknown ID Medium Find the Name of E-1009. Show "Not found" if missing.
  8. Look to the left Hard Find the Store ID of manager "Rani".
  9. Year opened Hard Find the year opened of BLK-SBN-01.
  10. Staff: ID of Shahrukh Hard Find the Emp ID of "Shahrukh".

Practice: 4.7 XLOOKUP Match Modes, Search Modes and Multiple Returns 4. Lookup Functions · 10 tasks

  1. First order of Zoya Easy Find the Amount of Zoya's first order.
  2. Amir's order Easy Find the Amount for "Amir".
  3. Tracking: first scan Easy Find the first Status of AWB-7001.
  4. Last order of Zoya Medium Find the Amount of Zoya's last order.
  5. Date of last order Medium Find the Date of Zoya's last order.
  6. Tracking: latest status Medium Find the latest Status of AWB-7001.
  7. Tracking: latest for AWB-7002 Medium Find the latest Status of AWB-7002.
  8. Tracking: date delivered Medium Find the Date of the latest scan of AWB-7001.
  9. No order message Hard Find the Amount for "Raja". If not found show "No order".
  10. Tracking: no scan yet Hard Find the latest Status of AWB-7009. Show "No scan yet" if missing.

Practice: 4.8 XMATCH 4. Lookup Functions · 10 tasks

  1. Position of a month Easy Find the position of "Oct" in B1:E1.
  2. Position of a city Easy Find the position of "Nagpur" in A2:A5.
  3. Café: column of Q3 Easy Find the position of "Q3 ($)" in USStore!B1:D1.
  4. Café: row of Muffins Easy Find the position of "Muffins" in USStore!A2:A5.
  5. Top city in Aug Medium Find the position of the biggest number in B2:B5.
  6. Café: best in Q2 Medium Find the position of the biggest number in USStore!C2:C5.
  7. Name of the top city Hard Find the name of the city with the biggest Aug sales.
  8. Two-way with XMATCH Hard Find sales for the city in H1 and month in H2.
  9. Café: best product in Q3 Hard Find the name of the product with the biggest Q3 sales.
  10. Café: Juice in Q2 Hard Find the sales of "Juice" in "Q2 ($)".

Practice: 4.9 Approximate Lookup: Delivery Fee Slabs 4. Lookup Functions · 10 tasks

  1. Fee for ₹150 Easy Find the fee for an order of ₹150.
  2. Fee for ₹64 Easy Find the fee for ₹64.
  3. Courier: 0.3 kg parcel Easy Find the charge for 0.3 kg.
  4. Exactly on the edge Medium Find the fee for ₹199.
  5. Big order Medium Find the fee for ₹1299.
  6. Courier: 2.5 kg parcel Medium Find the charge for 2.5 kg.
  7. Courier: exactly 5 kg Medium Find the charge for 5 kg.
  8. The VLOOKUP way Hard Find the fee for ₹270 with VLOOKUP.
  9. Customer pays Hard Find what the customer pays for a ₹98 order (98 + fee).
  10. Courier: two parcels Hard Find the charge for 2 parcels of 0.75 kg each.

Practice: 4.10 Common Lookup Errors and Fixes 4. Lookup Functions · 10 tasks

  1. Spot the space Easy Count the characters in H2.
  2. Scheme: spot the space Easy Count the characters in Scheme!F2.
  3. Fix with TRIM Medium Find the City of H2. Use TRIM so it works.
  4. Fix #N/A with IFNA Medium Find the City of H3. Show "Not in master" if missing, using IFNA and VLOOKUP.
  5. Scheme: fix with TRIM Medium Find the Name of Scheme!F2. Use TRIM.
  6. Scheme: not registered Medium Find the Amount (column 4) of Scheme!F3. Show "Not registered" if missing. Use IFNA and VLOOKUP.
  7. Fix #REF! Hard =VLOOKUP("BLK-KOP-01",$A$2:$E$8,6,FALSE) gives #REF!. Fix it to return the City Manager.
  8. Wrong match type Hard Find the City of "BLK-PUN-02" with an exact match.
  9. Scheme: fix #REF! Hard =VLOOKUP("PMK-002",Scheme!$A$2:$C$4,4,FALSE) gives #REF!. Fix it to return the District.
  10. Scheme: safe amount Hard Find the Amount of Scheme!F2 with TRIM. Show 0 if there is any error.

Practice: Practice and Answer Key: XLOOKUP and Delivery Fee Slabs 4. Lookup Functions · 10 tasks

  1. Manager of order 1 Easy Find the manager for the store in B2.
  2. Total order amount Easy Find the total of C2:C6.
  3. Insurance: holder name Easy Find the Holder of POL-502.
  4. Missing store Medium Find the manager for B4. Show "Not in master" if missing.
  5. Fee for order 1 Medium Find the fee for the amount in C2.
  6. Insurance: premium rate Medium Find the premium rate for the age in Policies!C3.
  7. Insurance: unknown policy Medium Find the Holder of POL-509. Show "Not in master" if missing.
  8. Bill for order 2 Hard Find what the customer pays for order 2 (C3 + fee).
  9. Fee for order 5 Hard Find the fee for C6.
  10. Insurance: yearly premium Hard Find the yearly premium of POL-503: Policies!D4/100000 × rate for age Policies!C4.

Practice: 5.1 Duplicates 5. Data Cleaning A–Z · 11 tasks

  1. How many times did P-501 come? Easy Count how many times P-501 is in B2:B11.
  2. Does P-506 repeat? Easy Check if P-506 appears more than once in B2:B11 (TRUE or FALSE).
  3. First or duplicate? (row 4) Easy Is B4 a "Duplicate" or "First"? Count only from B2 down to B4.
  4. First or duplicate? (row 5) Easy Is B5 a "Duplicate" or "First"?
  5. Extra visits of Emma Medium How many extra (repeat) rows does P-502 have?
  6. Number of different patients Medium How many different Patient IDs are in B2:B11?
  7. List of patients (spill) Medium Get the list of different Patient IDs from B2:B11.
  8. List of cities (spill) Medium Get the list of different cities from D2:D11.
  9. How many duplicate rows? Hard How many rows are repeats? (all rows minus different patients)
  10. One row per patient (spill) Hard Get the different rows of B2:D11 (3 columns).
  11. Is the last visit a repeat? Hard Is B11 a "Duplicate" or "First"?

Practice: 5.2 Blank Cells 5. Data Cleaning A–Z · 10 tasks

  1. Filled phone numbers Easy How many phones are filled in C2:C9?
  2. Missing phone numbers Easy How many phone cells are empty in C2:C9?
  3. Missing emails Easy How many emails are empty in D2:D9?
  4. Forms with a premium Easy How many premiums are filled in F2:F9?
  5. Filled boxes in one form Medium How many boxes are filled for F-04 (B5:F5)?
  6. All empty boxes Medium How many cells are empty in B2:F9?
  7. Is F-05 complete? Medium Is the form in B6:F6 complete? (TRUE or FALSE)
  8. Share of forms with a premium Medium What part of forms have a premium? (filled premiums ÷ all forms)
  9. Status message for F-03 Hard For row 4 (B4:F4) show "Complete" if nothing is empty, else "Missing " and the number.
  10. Missing contact details Hard How many cells are empty in the phone and email columns together (C2:D9)?

Practice: 5.3 Extra Spaces and Non-Printable Characters 5. Data Cleaning A–Z · 11 tasks

  1. Clean a name Easy Clean the name in B2.
  2. Length of a raw name Easy How many characters are in B2?
  3. Remove a double space Easy Clean the name in B3.
  4. Clean a city Easy Clean the city in C7.
  5. Count extra spaces Medium How many extra characters does B5 have? (length minus cleaned length)
  6. Is it really Carlos Diaz? Medium Does the cleaned B3 equal "Carlos Diaz"? (TRUE or FALSE)
  7. Hidden space in a city Medium Clean the city in C4.
  8. Count hidden characters Medium How many characters does CLEAN remove from B6?
  9. Line break to space Hard Make B6 read "Neha Deshpande".
  10. Full clean Hard Clean B4 fully.
  11. Clean a tracking number Hard Clean the AWB in D5.

Practice: 5.4 Inconsistent Case 5. Data Cleaning A–Z · 11 tasks

  1. Proper case name Easy Show B2 with each word starting with a capital.
  2. Code in capitals Easy Show A2 in capitals.
  3. Small letters Easy Show C2 in small letters.
  4. Brand in proper case Easy Show C7 in proper case.
  5. Trim and proper Medium Clean B3 (spaces and case).
  6. Trim and upper Medium Clean A5 into capitals.
  7. Clean product name Medium Clean B5 (spaces and case).
  8. Size in small letters Medium Show D3 in small letters.
  9. Fix a unit after PROPER Hard Make D3 read "200 g" using PROPER and SUBSTITUTE.
  10. Shelf label Hard Make "Lindt Dark Chocolate" from C3 and B3.
  11. Fix ml Hard Make D5 read "500 ml".

Practice: 5.5 Inconsistent City Spellings 5. Data Cleaning A–Z · 10 tasks

  1. Correct name for C2 Easy Find the correct city for C2 using CityMap.
  2. Correct name with VLOOKUP Easy Find the correct city for C4 with VLOOKUP.
  3. Correct name for C6 Easy Find the correct city for C6.
  4. Correct name for C8 Easy Find the correct city for C8.
  5. Fix the extra space first Medium Find the correct city for C3. Clean it first.
  6. Name not in the list Medium For C9 show the correct city, or "CHECK: Gurgaon" if not found.
  7. IFNA with VLOOKUP Medium For C9 use VLOOKUP, and show "CHECK" if not found.
  8. Is the name already correct? Medium Is C2 already the correct name? (TRUE or FALSE)
  9. Amount for Mumbai (both names) Hard Total amount for Bombay plus Mumbai (use TRIM so "Mumbai " counts).
  10. Amount for Bengaluru (both names) Hard Total amount for Bangalore plus Bengaluru.

Practice: 5.6 Numbers Stored as Text 5. Data Cleaning A–Z · 11 tasks

  1. Is it a number? Easy Is C3 a real number? (TRUE or FALSE)
  2. Is it text? Easy Is D2 text? (TRUE or FALSE)
  3. Turn text into a number Easy Change C3 into a number.
  4. Quantity as a number Easy Change D6 into a number.
  5. Remove the comma Medium Change C2 into a number by removing the comma first.
  6. Remove the € sign Medium Change C4 into a number.
  7. Spaces and comma Medium Change C5 into a number.
  8. Count text numbers Medium How many cells in C2:C7 are text?
  9. Total quantity Hard Total of D2:D7 as numbers.
  10. Amount still due Hard How much is still due on INV-04 (C5 minus E5)?
  11. Total of all amounts Hard Total of C2:C7 after removing commas, € and spaces.

Practice: 5.7 Text Dates and Mixed Date Formats 5. Data Cleaning A–Z · 11 tasks

  1. Is it a real date? Easy Is C2 a number (real date)? (TRUE or FALSE)
  2. Get the year Easy Take the year text from C2.
  3. Get the day Easy Take the day text from C3.
  4. Get the month Easy Take the month text from C3.
  5. Build the date (Indian style) Medium Make a real date from C2.
  6. Build the date (US style) Medium Make a real date from D3.
  7. Build the date (yyyymmdd) Medium Make a real date from E4.
  8. Month number of a trip Medium Which month (number) is the trip in C5?
  9. Days between two trips Hard How many days from trip B-01 (C2) to trip B-02 (C3)?
  10. Do two websites agree? Hard Is the C6 date equal to the D6 date? (TRUE or FALSE)
  11. Weekday of a trip Hard Which weekday is the trip in E2? (1 = Sunday, 7 = Saturday)

Practice: 5.8 Splitting Columns 5. Data Cleaning A–Z · 12 tasks

  1. Total parcels Easy Add up Parcels in D2:D11. (sheet "Data")
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"? (sheet "Data")
  4. Largest parcels Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail". (sheet "Data")
  10. Royal Mail in Glasgow Medium Total of D2:D11 where B is "Royal Mail" and C is "Glasgow". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: 5.9 Merging Columns 5. Data Cleaning A–Z · 11 tasks

  1. Full name Easy Join A2 and B2 with a space.
  2. Surname first Easy Make "Walker, James" from row 3.
  3. Name with room Easy Make "Riya - 101" from A2 and C2.
  4. CONCAT Easy Join A4, a space and B4 with CONCAT.
  5. Name in capitals Medium Make "MATEO LOPEZ" from row 5.
  6. Name and country Medium Make "Sara Haddad (UAE)".
  7. TEXTJOIN a row Medium Join A2:C2 with " | ".
  8. All first names Medium Join A2:A6 with ", ".
  9. Booking code with date Hard Make "101-20261220" from C2 and D2.
  10. Arrival message Hard Make "Ankit Jain arrives on 24-12-2026 for 5 nights".
  11. Initials Hard Make "J.W." from row 3.

Practice: 5.10 Phone Numbers 5. Data Cleaning A–Z · 11 tasks

  1. Remove spaces Easy Remove all spaces from B4.
  2. Remove dashes Easy Remove dashes from B7.
  3. Length of a clean number Easy How many digits are in C2?
  4. Last 4 digits Easy Get the last 4 digits of C2.
  5. Remove spaces and dash Medium Clean B2 of spaces and dashes.
  6. Last 10 digits Medium From the cleaned B2, keep only the last 10 digits.
  7. Is it 10 digits? Medium Is C7 exactly 10 digits long? (TRUE or FALSE)
  8. Masked number Medium Make masked text for C6: "XXXXXX" and then the last 4 digits.
  9. Valid mobile? Hard Is C4 valid? (TRUE or FALSE)
  10. Clean brackets too Hard Clean B3 of brackets and spaces.
  11. Count bad numbers Hard How many numbers in C2:C7 are NOT 10 digits?

Practice: 5.11 E-mail Addresses 5. Data Cleaning A–Z · 11 tasks

  1. Clean and small letters Easy Clean B2.
  2. Small letters Easy Show B6 in small letters.
  3. Where is the @? Easy Find the position of "@" in B3.
  4. Has an @? Easy Does B5 have an "@"? (TRUE or FALSE)
  5. User name part Medium Get the text before "@" in B3.
  6. Domain part Medium Get the text after "@" in B3.
  7. Count @ signs Medium How many "@" are in B4?
  8. Space inside? Medium Does B7 have a space? (TRUE or FALSE)
  9. Valid or check (B3) Hard Show "Yes" if B3 has exactly one @ and no space, else "Check".
  10. Valid or check (B4) Hard Same test for B4.
  11. Emails with no @ Hard How many emails in B2:B7 have no "@"?

Practice: 5.12 PIN Codes and Codes with Leading Zeros 5. Data Cleaning A–Z · 11 tasks

  1. Fix a US ZIP Easy Show C3 as 5 digits.
  2. Fix a short ZIP Easy Show C5 as 5 digits.
  3. Item code with zeros Easy Show E2 as 6 digits.
  4. Length of a number Easy How many digits does C3 show?
  5. Is it a number or text? Medium Is D3 text? (TRUE or FALSE)
  6. Do they match? Medium Show C5 as a 5-digit code with leading zeros. Is it the same as D5? (TRUE or FALSE)
  7. Item code with prefix Medium Make "SKU-000123" from E4.
  8. Zeros with RIGHT Medium Show E3 as 6 digits using RIGHT.
  9. Right length by country Hard Show C4 with the right length for its country (B4).
  10. Same for a US row Hard Show C3 with the right length for its country.
  11. Count lost zeros Hard How many numbers in C2:C7 are shorter than 5 digits?

Practice: 5.13 Extracting Parts of Text 5. Data Cleaning A–Z · 11 tasks

  1. First word Easy Get the first 5 characters of A2.
  2. Order number Easy Get the order number from A2 (start 7, length 9).
  3. Last word Easy Get the last 3 characters of A3.
  4. Length of the message Easy How many characters are in A4?
  5. Order number as a number Medium Get the order number from A3 as a number.
  6. Position of " in " Medium Find the position of " in " in A2.
  7. Text after "in " Medium Get the text after "in " in A2.
  8. Order number with TEXTBEFORE Medium Get the text before " delivered" in A4, after "ORDER ".
  9. Minutes as a number Hard Get the minutes from A2 as a number.
  10. Minutes for a 1-digit order Hard Get the minutes from A3 as a number.
  11. Late delivery? Hard Is the order in A5 late? (TRUE or FALSE)

Practice: 5.14 Removing Unwanted Characters 5. Data Cleaning A–Z · 11 tasks

  1. Remove stars Easy Remove all * from B2.
  2. Underscore to space Easy Change "_" to " " in B3.
  3. Remove hash signs Easy Remove all # from B4.
  4. Dot to space Easy Change "." to " " in B6.
  5. Remove a note Medium Clean B5 to "Daniel Moore".
  6. Remove the $ Medium Remove "$" from C3.
  7. Price as a number Medium Change C2 into a number.
  8. Count symbols Medium Count "#" in B4.
  9. Total price Hard Total of C2:C6 as numbers.
  10. Clean name, proper case Hard Clean B3 into "Michael Brown".
  11. Remove both symbols Hard Remove "*" and "#" from B2 in one formula.

Practice: 5.15 Outliers 5. Data Cleaning A–Z · 11 tasks

  1. Average Easy Find the average of C2:C11.
  2. Median Easy Find the median of C2:C11.
  3. Biggest value Easy Find the biggest value in C2:C11.
  4. Smallest value Easy Find the smallest value in C2:C11.
  5. First quartile (Q1) Medium Find Q1 of C2:C11.
  6. Third quartile (Q3) Medium Find Q3 of C2:C11.
  7. IQR Medium Find the IQR of C2:C11.
  8. Upper fence Medium Find the upper fence.
  9. Is ATM-07 an outlier? Hard Show "Outlier" if C8 is above the upper fence or below the lower fence, else "OK".
  10. Is ATM-02 an outlier? Hard Same test for C3.
  11. Z-score of ATM-09 Hard Find the z-score of C10.

Practice: 5.16 Error Values in Data 5. Data Cleaning A–Z · 11 tasks

  1. Safe division Easy Divide C3 by D3. If there is an error, show 0.
  2. Normal division Easy Divide C2 by D2. If error, show 0.
  3. Find a manager Easy Find the manager of A3 from Master.
  4. Safe division with a note Easy Divide C3 by D3. If error, show "No visitors".
  5. Missing manager Medium Find the manager of A5. If not found, show "Not in Master".
  6. Is it a number? Medium Is C5 a number? (TRUE or FALSE)
  7. Sum that skips text Medium Total sales in C2:C7.
  8. Count good numbers Medium How many numbers are in E2:E7?
  9. Count error text Hard How many cells in E2:E7 start with "#"?
  10. Average of good values Hard Average of the numbers in E2:E7.
  11. Recalculate safely Hard Total of C2:C7 ÷ D2:D7 for each row, where errors count as 0.

Practice: 5.18 End-to-End Cleaning of a Messy Export 5. Data Cleaning A–Z · 11 tasks

  1. Clean order ID Easy Clean A2.
  2. Clean customer name Easy Clean C2.
  3. Clean city Easy Clean D2.
  4. Clean another ID Easy Clean A4.
  5. Amount as a number Medium Change E4 into a number.
  6. US date to real date Medium Make a real date from B5.
  7. Name with a double space Medium Clean C5.
  8. Orders from Chicago Medium How many cleaned cities in D2:D6 are "Chicago"?
  9. Total amount Hard Total of E2:E6 as numbers.
  10. New York sales Hard Total amount for New York (cleaned D2:D6).
  11. Clean label Hard Make "GX-9002 | Raj Patel | Chicago" from row 3.

Practice: Practice: Cleaning a Messy Export 5. Data Cleaning A–Z · 11 tasks

  1. Clean a name Easy Clean B2.
  2. Missing phones Easy How many phones are empty in C2:C7?
  3. Medicine in proper case Easy Clean D4.
  4. Repeat patient Easy How many bills are for "rekha sharma" after TRIM?
  5. Clean phone Medium Remove spaces and dashes from C3.
  6. Amount as a number Medium Change E4 into a number.
  7. Real date Medium Make a real date from F5.
  8. Is the date missing? Medium Is F7 empty? (TRUE or FALSE)
  9. Total sales Hard Total of E2:E7 as numbers.
  10. Different patients Hard How many different cleaned names are in B2:B7?
  11. Bill line Hard Make "Sanjay Gupta - Ibuprofen 400 - 60" from row 6.

Practice: 6.1 Excel Tables (Ctrl + T) 6. Tables, Sorting and Filtering · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Delhi Medium Total of D2:D11 where B is "Snacks" and C is "Delhi".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Units sold for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 6.2 Structured References 6. Tables, Sorting and Filtering · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Denver Medium Total of D2:D11 where B is "Website" and C is "Denver".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Quantity for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 6.3 The Total Row 6. Tables, Sorting and Filtering · 11 tasks

  1. Total amount Easy Total of D2:D8 like a Total Row.
  2. Average amount Easy Average of D2:D8.
  3. Count claims Easy Count of A2:A8.
  4. Biggest claim Easy Biggest value in D2:D8.
  5. Smallest claim Medium Smallest value in D2:D8.
  6. Count numbers Medium Count numbers in E2:E8.
  7. Average days open Medium Average of E2:E8.
  8. Spread of amounts Medium Standard deviation of D2:D8.
  9. Total rounded Hard Average of D2:D8, rounded to 2 decimals.
  10. Share of the biggest claim Hard What part of the total is the biggest claim?
  11. Range of days Hard Longest open days minus shortest.

Practice: 6.4 Sorting: Multi-level and Custom Sort 6. Tables, Sorting and Filtering · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Admin".
  4. Sum of filtered Medium Sum of D2:D11 where B is "IT", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Admin" or "IT".
  11. Names joined Hard Names where B is "Admin", joined with ", ".

Practice: 6.5 AutoFilter 6. Tables, Sorting and Filtering · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Standard".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Deluxe", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Standard" or "Deluxe".
  11. Names joined Hard Names where B is "Standard", joined with ", ".

Practice: 6.7 SUBTOTAL and AGGREGATE 6. Tables, Sorting and Filtering · 11 tasks

  1. Total cost Easy Total of E2:E8.
  2. Average weight Easy Average of D2:D8.
  3. Heaviest Easy Biggest weight in D2:D8.
  4. Lightest Easy Smallest weight in D2:D8.
  5. Count costs (numbers) Medium Count numbers in E2:E8.
  6. Count all cells Medium Count filled cells in E2:E8.
  7. Pending costs Medium How many costs are not numbers?
  8. Average cost Medium Average of E2:E8.
  9. Cost per kg Hard Visible total of E2 to E8 divided by (visible total of D2 to D8 minus D3).
  10. Hidden-row version Hard Count shipments in A2:A8 with code 103.
  11. Gap between heaviest and lightest Hard Heaviest minus lightest in D2:D8 with 104 and 105.

Practice: 6.10 Range vs Excel Table 6. Tables, Sorting and Filtering · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Milan Medium Total of D2:D11 where B is "Antibiotic" and C is "Milan".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Liam O'Brien Medium The Stock units for "Liam O'Brien" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 6.11 Writing Formulas in a Normal Range 6. Tables, Sorting and Filtering · 11 tasks

  1. Line total Easy Multiply B2 by C2.
  2. Another line Easy Multiply B5 by C5.
  3. Total sales Easy Total of D2:D7.
  4. Pieces sold Easy Total of C2:C7.
  5. Tax on one line Medium Tax on D2 using $H$2.
  6. Tax on another line Medium Tax on D6 using $H$2.
  7. Price with tax Medium Price of B3 with tax.
  8. Average price Medium Average of B2:B7.
  9. Total tax Hard Total of D2:D7 × $H$2.
  10. Total with tax, rounded Hard Total sales plus tax, rounded to 2 decimals.
  11. Check a total Hard Does D4 equal B4*C4? (TRUE or FALSE)

Practice: 6.12 Sorting and Filtering Inside an Excel Table 6. Tables, Sorting and Filtering · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Downtown".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Airport", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Downtown" or "Airport".
  11. Names joined Hard Names where B is "Downtown", joined with ", ".

Practice: Practice: Ranges, Tables and Totals 6. Tables, Sorting and Filtering · 11 tasks

  1. Total units Easy Total of D2:D8 with SUBTOTAL 109.
  2. Count orders Easy Count A2:A8 with SUBTOTAL 103.
  3. Order value Easy Value of row 2 (D2*E2).
  4. Highest price Easy Highest price in E2:E8.
  5. Laptop units Medium Units of "Laptop".
  6. Shipped orders Medium How many orders are "Shipped"?
  7. East shipped units Medium Units in East that are Shipped.
  8. Average price Medium Average of E2:E8 with SUBTOTAL.
  9. Total sales value Hard Total of D2:D8 × E2:E8.
  10. Value not cancelled Hard Total units × price where Status is not "Cancelled".
  11. Pending laptop units Hard Units of Laptop that are Pending.

Practice: 7.1 Creating a PivotTable 7. PivotTables and PivotCharts · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Bengaluru Medium Total of D2:D11 where B is "Snacks" and C is "Bengaluru".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Units sold for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 7.2 Field Areas: Rows, Columns, Values and Filters 7. PivotTables and PivotCharts · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Website".
  4. Sum of filtered Medium Sum of D2:D11 where B is "App", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Website" or "App".
  11. Names joined Hard Names where B is "Website", joined with ", ".

Practice: 7.3 Summarize Values By 7. PivotTables and PivotCharts · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Glasgow Medium Total of D2:D11 where B is "North" and C is "Glasgow".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Kavya Reddy Medium The Deals won for "Kavya Reddy" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 7.4 Show Values As 7. PivotTables and PivotCharts · 13 tasks

  1. Total invoices Easy Add up Invoices in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin Easy How many rows have Department "Admin"?
  4. Largest invoices Easy The largest value in D2:D11.
  5. Invoices for Admin Medium Total of D2:D11 where B is "Admin".
  6. Admin in Paris Medium Total of D2:D11 where B is "Admin" and C is "Paris".
  7. Best in Admin Medium Largest E2:E11 value where B is "Admin".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Invoices for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Admin Hard Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 7.5 Grouping Dates and Numbers 7. PivotTables and PivotCharts · 12 tasks

  1. Total nights Easy Add up Nights in D2:D11. (sheet "Data")
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Standard Easy How many rows have Room type "Standard"? (sheet "Data")
  4. Largest nights Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Nights for Standard Medium Total of D2:D11 where B is "Standard". (sheet "Data")
  10. Standard in Glasgow Medium Total of D2:D11 where B is "Standard" and C is "Glasgow". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: 7.6 Calculated Fields and Calculated Items 7. PivotTables and PivotCharts · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Chicago Medium Total of D2:D11 where B is "Cloud" and C is "Chicago".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sophie Martin Medium The Billable hours for "Sophie Martin" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 7.7 Sorting, Filtering and Top 10 7. PivotTables and PivotCharts · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Google".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Meta", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Google" or "Meta".
  11. Names joined Hard Names where B is "Google", joined with ", ".

Practice: Practice and Answer Key: PivotTable by City and Platform 7. PivotTables and PivotCharts · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Austin Medium Total of D2:D11 where B is "Downtown" and C is "Austin".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Liam O'Brien Medium The Covers for "Liam O'Brien" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 9.1 Spill Ranges and the # Operator 9. Dynamic Arrays · 11 tasks

  1. Sales of every café (spill) Easy Multiply C2:C7 by D2:D7.
  2. Double the cups (spill) Easy Multiply C2:C7 by 2.
  3. Cities list (spill) Easy Show B2:B7.
  4. Unique cities (spill) Easy Show the different cities in B2:B7.
  5. Price with 10% rise (spill) Medium Show D2:D7 × 1.1, rounded to 2 decimals.
  6. Big sellers (spill) Medium Is each C2:C7 above 350?
  7. Labels (spill) Medium Join A2:A7, " - " and B2:B7.
  8. Total sales in one cell Medium Total of C2:C7 × D2:D7 in one cell.
  9. Count unique cities Hard How many different cities?
  10. Hit or miss (spill) Hard Show "Hit" if C2:C7 is 300 or more, else "Miss".
  11. Biggest sale Hard Biggest C2:C7 × D2:D7.

Practice: 9.2 FILTER 9. Dynamic Arrays · 11 tasks

  1. Pune customers (spill) Easy Show customers (B2:B8) where branch is "Pune".
  2. Approved loan IDs (spill) Easy Show A2:A8 where Status is "Approved".
  3. Big loans (spill) Easy Show customers with amount over 1000000.
  4. Personal loan amounts (spill) Easy Show E2:E8 where Type is "Personal".
  5. Full rows for Goa (spill) Medium Show A2:F8 rows where branch is "Goa".
  6. Approved in Pune (spill) Medium Customers in Pune AND Approved.
  7. Home or Car (spill) Medium Customers with Home OR Car loans.
  8. Total approved Medium Total amount of Approved loans.
  9. Nothing found message Hard Show customers from "Delhi", or "None" if none.
  10. Count pending Hard How many loans are Pending?
  11. Name and amount, Kochi (spill) Hard Show customer, branch, type and amount (B2:E8) for Kochi.

Practice: 9.3 SORT and SORTBY 9. Dynamic Arrays · 11 tasks

  1. Courses A to Z (spill) Easy Sort A2:A7.
  2. Students low to high (spill) Easy Sort C2:C7.
  3. Students high to low (spill) Easy Sort C2:C7 big to small.
  4. Courses Z to A (spill) Easy Sort A2:A7 Z to A.
  5. Table by students (spill) Medium Sort A2:E7 by column 3 (Students), big to small.
  6. Table by rating (spill) Medium Sort A2:E7 by column 4, big to small.
  7. Cheapest first (spill) Medium Sort A2:E7 by column 5, small to big.
  8. Ratings best first (spill) Medium Sort D2:D7 big to small.
  9. Udemy courses, most students first (spill) Hard Show only Udemy rows of A2:E7, sorted by column 3 (Students), big to small.
  10. Sorted Udemy courses (spill) Hard Show Udemy course names sorted A to Z.
  11. Platforms A to Z, no repeats (spill) Hard Sorted list of different platforms.

Practice: 9.4 UNIQUE 9. Dynamic Arrays · 11 tasks

  1. Different airlines (spill) Easy UNIQUE of D2:D8.
  2. Starting cities (spill) Easy UNIQUE of B2:B8.
  3. End cities (spill) Easy UNIQUE of C2:C8.
  4. How many airlines? Easy Count different airlines.
  5. Unique routes (spill) Medium UNIQUE of B2:C8.
  6. Sorted airlines (spill) Medium Sorted list of airlines.
  7. Airlines seen once (spill) Medium Airlines that appear exactly once in D2:D8.
  8. How many routes? Medium Count different routes in B2:C8.
  9. Cities going to Rome (spill) Hard Different starting cities for flights to Rome.
  10. Routes flown once (spill) Hard Routes in B2:C8 that appear exactly once.
  11. Passengers per airline (spill) Hard For each airline (no repeats) in D2 to D8, add up E2 to E8.

Practice: 9.5 SEQUENCE and RANDARRAY 9. Dynamic Arrays · 11 tasks

  1. 1 to 10 (spill) Easy Make numbers 1 to 10.
  2. Row numbers for members (spill) Easy Make 1 to 4.
  3. Start at 101 (spill) Easy Make 5 numbers starting at 101.
  4. Steps of 5 (spill) Easy Make 6 numbers: 0, 5, 10 ...
  5. A grid (spill) Medium Make 3 rows and 4 columns of numbers.
  6. Member codes (spill) Medium Make "GYM-1" to "GYM-4".
  7. Fee for 1 to 12 months (spill) Medium Fee in D2 × months 1 to 12.
  8. Next 7 days (spill) Medium 7 dates starting from C2.
  9. Monthly bill dates (spill) Hard Make 6 monthly dates starting from C3 (C3, then 1 month later, up to 5 months later).
  10. Total of 1 to 100 Hard Add the numbers 1 to 100.
  11. Year fee for Premium Hard Make 12 copies of D3 with SEQUENCE (step 0) and add them.

Practice: Practice: FILTER, SORT and UNIQUE 9. Dynamic Arrays · 11 tasks

  1. Different drivers (spill) Easy UNIQUE of C2:C8.
  2. Austin rides (spill) Easy Ride IDs where City is "Austin".
  3. Fares high to low (spill) Easy Sort E2:E8 big to small.
  4. Cities A to Z (spill) Easy List each city in B2 to B8 only once, in A to Z order.
  5. 5-star rides (spill) Medium Full rows A2:F8 where Rating is 5.
  6. Long rides sorted (spill) Medium Fares of rides over 6 miles, big to small.
  7. Amy Chen fares total Medium Total fare of "Amy Chen".
  8. Drivers in Seattle (spill) Medium Different drivers in Seattle, A to Z.
  9. Table sorted by fare (spill) Hard Sort A2:F8 by column 5, big to small.
  10. Fare per city (spill) Hard For each city (no repeats, A to Z) from B2 to B8, add up E2 to E8.
  11. Low-rated in Seattle or Denver (spill) Hard Ride IDs with Rating below 5 in Seattle OR Denver.

Practice: 11.1 Goal Seek 11. What-If Analysis · 10 tasks

  1. Monthly rate Easy Yearly rate in B3 divided by 12.
  2. Number of months Easy Years in B4 times 12.
  3. Monthly EMI Easy EMI for loan B2, yearly rate B3, years B4 (show it as a positive number).
  4. EMI rounded Easy The EMI from the last task, rounded to 0 decimals.
  5. Total paid Medium Total amount paid over the loan.
  6. Total interest Medium Total paid minus the loan amount B2, rounded to 0 decimals.
  7. Gap to target Medium EMI minus the target EMI in B6, rounded to 0 decimals.
  8. Within target? Medium Is the EMI at most B6? (TRUE or FALSE)
  9. Loan that fits the target Hard Loan amount whose EMI is exactly B6 (same rate and years), rounded to 0 decimals.
  10. Months needed at target EMI Hard Months needed to repay B2 at EMI B6, rounded up to a whole month.

Practice: 11.2 Scenario Manager 11. What-If Analysis · 10 tasks

  1. Base revenue Easy Revenue in the base case (column C).
  2. Best revenue Easy Revenue in the best case (column B).
  3. Base variable cost Easy Variable cost in the base case.
  4. Base profit Medium Base case: units × (price − cost per unit) − fixed cost.
  5. Worst profit Medium Worst case profit, same way.
  6. Best minus worst profit Medium Best case profit minus worst case profit.
  7. Base margin per unit Medium Base price minus base cost per unit.
  8. Break-even units (base) Hard Base fixed cost ÷ base margin per unit, rounded up.
  9. Worst case loses money? Hard Is the worst case profit below 0? (TRUE or FALSE)
  10. Best profit margin % Hard Best case profit ÷ best case revenue, rounded to 3 decimals.

Practice: 11.3 One-variable Data Table 11. What-If Analysis · 10 tasks

  1. Revenue at £18 Easy Revenue in row 2.
  2. Revenue at £28 Easy Revenue in row 7.
  3. Profit at £22 Easy Profit in row 4 using cost B8.
  4. All revenues (spill) Medium Revenue for rows 2 to 7 in one formula.
  5. All profits (spill) Medium Profit for rows 2 to 7 using cost B8.
  6. Highest revenue Medium Biggest revenue across rows 2 to 7.
  7. Highest profit Medium Biggest profit across rows 2 to 7.
  8. Best price for profit Hard The price in A2:A7 that gives the highest profit.
  9. Units lost from £18 to £28 Hard Units at £18 minus units at £28.
  10. Prices with profit above £14,000 Hard How many prices give profit above 14000?

Practice: 11.4 Two-variable Data Table 11. What-If Analysis · 10 tasks

  1. Months Easy Months for the years in B5.
  2. €200k at 6% Easy Monthly payment for A2 at rate B2, years B5, as a positive number rounded to 2 decimals.
  3. €300k at 8% Easy Payment for A4 at rate D2, rounded to 2 decimals.
  4. €250k at 7% Medium Payment for A3 at rate C2, rounded to 2 decimals.
  5. Row of payments (spill) Medium Payments for A2 at the three rates B2:D2, rounded to 2 decimals.
  6. Column of payments (spill) Medium Payments for A2:A4 at rate B2, rounded to 2 decimals.
  7. Whole table (spill) Hard Payments for loans A2:A4 (down) and rates B2:D2 (across), rounded to 2 decimals.
  8. Cost of 1% more Hard For A3: payment at C2 minus payment at B2, rounded to 2 decimals.
  9. Total interest €300k at 8% Hard Payment for A4 at D2 × months − A4, rounded to 0 decimals.
  10. Cheapest payment over €1,500? Hard Is the payment for A3 at B2 above 1500? (TRUE or FALSE)

Practice: 11.5 Solver (Brief) 11. What-If Analysis · 10 tasks

  1. Kurta profit Easy Profit from kurtas (row 2).
  2. Total profit (objective) Easy Total profit of all 3 products.
  3. Total units Easy Total of E2:E4.
  4. Machine hours used Medium Machine hours used by the plan.
  5. Labour hours used Medium Labour hours used by the plan.
  6. Machine hours left Medium Machine limit C5 minus machine hours used.
  7. Plan fits machines? Medium Are machine hours used at most C5? (TRUE or FALSE)
  8. Plan fits both limits? Hard Do both machine and labour hours fit their limits? (TRUE or FALSE)
  9. Profit per machine hour Hard Highest profit per machine hour among the 3 products, rounded to 2 decimals.
  10. Best product per labour hour Hard Product with the highest profit per labour hour.

Practice: 13.3 PivotTables that Feed the Dashboard 13. Excel Dashboards · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Bristol Medium Total of D2:D11 where B is "North" and C is "Bristol".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Deals won for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 15.2 Stage 1 – Clean the Raw Export 15. Final Project: Blinkit Maharashtra Monthly Report · 12 tasks

  1. Total quantity Easy Add up Quantity in D2:D11. (sheet "Data")
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Website Easy How many rows have Channel "Website"? (sheet "Data")
  4. Largest quantity Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Quantity for Website Medium Total of D2:D11 where B is "Website". (sheet "Data")
  10. Website in Austin Medium Total of D2:D11 where B is "Website" and C is "Austin". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: 15.3 Stage 2 – Tables and Lookup Formulas 15. Final Project: Blinkit Maharashtra Monthly Report · 11 tasks

  1. VLOOKUP name Easy Name for ID "S103" with VLOOKUP.
  2. XLOOKUP value Easy The Avg deal value for ID "S103".
  3. Position Easy Position of "Sophie Martin" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Sophie Martin" using INDEX and MATCH.
  5. Not found message Medium Deals won for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "S103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Sophie Martin" and column "Deals won".
  11. Lookup the top Hard Name with the largest Deals won.

Practice: 15.4 Stage 3 – PivotTable Analysis 15. Final Project: Blinkit Maharashtra Monthly Report · 13 tasks

  1. Total invoices Easy Add up Invoices in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin Easy How many rows have Department "Admin"?
  4. Largest invoices Easy The largest value in D2:D11.
  5. Invoices for Admin Medium Total of D2:D11 where B is "Admin".
  6. Admin in Paris Medium Total of D2:D11 where B is "Admin" and C is "Paris".
  7. Best in Admin Medium Largest E2:E11 value where B is "Admin".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Invoices for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Admin Hard Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 15.5 Stage 4 – Build the Dashboard 15. Final Project: Blinkit Maharashtra Monthly Report · 13 tasks

  1. Total nights Easy Add up Nights in D2:D11.
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard Easy How many rows have Room type "Standard"?
  4. Largest nights Easy The largest value in D2:D11.
  5. Nights for Standard Medium Total of D2:D11 where B is "Standard".
  6. Standard in Leeds Medium Total of D2:D11 where B is "Standard" and C is "Leeds".
  7. Best in Standard Medium Largest E2:E11 value where B is "Standard".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Nights for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Standard Hard Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 16.1 Modules 1–2: Basics and Data Entry 16. Practice Exercises with Answer Hints · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Delhi Medium Total of D2:D11 where B is "Snacks" and C is "Delhi".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Hannah Lee Medium The Units sold for "Hannah Lee" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 16.2 Module 3: Formulas and Functions 16. Practice Exercises with Answer Hints · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Chicago Medium Total of D2:D11 where B is "Website" and C is "Chicago".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Quantity for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 16.3 Module 4: Lookups 16. Practice Exercises with Answer Hints · 11 tasks

  1. VLOOKUP name Easy Name for ID "S103" with VLOOKUP.
  2. XLOOKUP value Easy The Avg deal value for ID "S103".
  3. Position Easy Position of "Hannah Lee" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Hannah Lee" using INDEX and MATCH.
  5. Not found message Medium Deals won for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "S103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Hannah Lee" and column "Deals won".
  11. Lookup the top Hard Name with the largest Deals won.

Practice: 16.4 Module 5: Data Cleaning 16. Practice Exercises with Answer Hints · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Paris Medium Total of D2:D11 where B is "Admin" and C is "Paris". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: 16.5 Module 6: Tables, Sorting and Filtering 16. Practice Exercises with Answer Hints · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Standard".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Deluxe", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Standard" or "Deluxe".
  11. Names joined Hard Names where B is "Standard", joined with ", ".

Practice: 16.6 Module 7: PivotTables 16. Practice Exercises with Answer Hints · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Denver Medium Total of D2:D11 where B is "Cloud" and C is "Denver".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Billable hours for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 16.8 Module 9: Dynamic Arrays 16. Practice Exercises with Answer Hints · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Royal Mail".
  4. Sum of filtered Medium Sum of D2:D11 where B is "DPD", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Royal Mail" or "DPD".
  11. Names joined Hard Names where B is "Royal Mail", joined with ", ".

Practice: 16.10 Module 11: What-If Analysis 16. Practice Exercises with Answer Hints · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: 16.12 Module 13: Dashboards 16. Practice Exercises with Answer Hints · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Austin Medium Total of D2:D11 where B is "Downtown" and C is "Austin".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Covers for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 16.14 Mixed Scenario Exercises 16. Practice Exercises with Answer Hints · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Seattle Medium Total of D2:D11 where B is "Website" and C is "Seattle".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Quantity for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 16.15 Challenge Exercises 16. Practice Exercises with Answer Hints · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in London Medium Total of D2:D11 where B is "North" and C is "London".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Deals won for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 17.1 Basics and References 17. Interview Questions and Answers · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Delhi Medium Total of D2:D11 where B is "Snacks" and C is "Delhi".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Emma Schmidt Medium The Units sold for "Emma Schmidt" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 17.2 Functions and Formulas 17. Interview Questions and Answers · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Austin Medium Total of D2:D11 where B is "Website" and C is "Austin".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Quantity for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 17.3 Lookups 17. Interview Questions and Answers · 11 tasks

  1. VLOOKUP name Easy Name for ID "S103" with VLOOKUP.
  2. XLOOKUP value Easy The Avg deal value for ID "S103".
  3. Position Easy Position of "Vikram Singh" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Vikram Singh" using INDEX and MATCH.
  5. Not found message Medium Deals won for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "S103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Vikram Singh" and column "Deals won".
  11. Lookup the top Hard Name with the largest Deals won.

Practice: 17.4 Data Cleaning and Preparation 17. Interview Questions and Answers · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Milan Medium Total of D2:D11 where B is "Admin" and C is "Milan". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: 17.6 Performance, Dashboards and Power Tools 17. Interview Questions and Answers · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Austin Medium Total of D2:D11 where B is "Cloud" and C is "Austin".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Rohan Iyer Medium The Billable hours for "Rohan Iyer" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 17.8 Scenario-Based Questions 17. Interview Questions and Answers · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in London Medium Total of D2:D11 where B is "Royal Mail" and C is "London".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Parcels for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.2 Deloitte 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Denver Medium Total of D2:D11 where B is "Website" and C is "Denver".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Quantity for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.3 Accenture 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Leeds Medium Total of D2:D11 where B is "North" and C is "Leeds".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Deals won for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.4 TCS 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total invoices Easy Add up Invoices in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin Easy How many rows have Department "Admin"?
  4. Largest invoices Easy The largest value in D2:D11.
  5. Invoices for Admin Medium Total of D2:D11 where B is "Admin".
  6. Admin in Paris Medium Total of D2:D11 where B is "Admin" and C is "Paris".
  7. Best in Admin Medium Largest E2:E11 value where B is "Admin".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Invoices for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Admin Hard Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.5 EY 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total nights Easy Add up Nights in D2:D11.
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard Easy How many rows have Room type "Standard"?
  4. Largest nights Easy The largest value in D2:D11.
  5. Nights for Standard Medium Total of D2:D11 where B is "Standard".
  6. Standard in Bristol Medium Total of D2:D11 where B is "Standard" and C is "Bristol".
  7. Best in Standard Medium Largest E2:E11 value where B is "Standard".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Nights for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Standard Hard Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.6 Mu Sigma 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Denver Medium Total of D2:D11 where B is "Cloud" and C is "Denver".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Noah Wilson Medium The Billable hours for "Noah Wilson" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.7 Amazon 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Seattle Medium Total of D2:D11 where B is "Website" and C is "Seattle".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Liam O'Brien Medium The Quantity for "Liam O'Brien" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.8 PwC and KPMG 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Leeds Medium Total of D2:D11 where B is "Royal Mail" and C is "Leeds".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Parcels for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.9 Capgemini, Infosys, Cognizant and Wipro 18. Interview Questions Asked in MNC Interviews · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: 18.10 MathCo and FedEx India 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Dublin Medium Total of D2:D11 where B is "Antibiotic" and C is "Dublin".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up James Carter Medium The Stock units for "James Carter" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 18.11 Frequently Asked Across MNC Interviews 18. Interview Questions Asked in MNC Interviews · 13 tasks

  1. Total data gb Easy Add up Data GB in D2:D11.
  2. Average monthly bill Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Prepaid Easy How many rows have Plan "Prepaid"?
  4. Largest data gb Easy The largest value in D2:D11.
  5. Data GB for Prepaid Medium Total of D2:D11 where B is "Prepaid".
  6. Prepaid in Berlin Medium Total of D2:D11 where B is "Prepaid" and C is "Berlin".
  7. Best in Prepaid Medium Largest E2:E11 value where B is "Prepaid".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Data GB for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Prepaid Hard Share of D2:D11 that is "Prepaid", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 19.2 Function Cheat Sheet 19. Keyboard Shortcuts and Function Cheat Sheet · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Boston Medium Total of D2:D11 where B is "Website" and C is "Boston".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Quantity for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.1 Sales vs Target Tracker 21. Real-World Project Briefs · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in London Medium Total of D2:D11 where B is "North" and C is "London".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Deals won for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.2 Budget vs Actual Report 21. Real-World Project Briefs · 13 tasks

  1. Total times Easy Add up Times in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Food Easy How many rows have Category "Food"?
  4. Largest times Easy The largest value in D2:D11.
  5. Times for Food Medium Total of D2:D11 where B is "Food".
  6. Food in Mumbai Medium Total of D2:D11 where B is "Food" and C is "Mumbai".
  7. Best in Food Medium Largest E2:E11 value where B is "Food".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Times for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Food Hard Share of D2:D11 that is "Food", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.3 Inventory Reorder Sheet 21. Real-World Project Briefs · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Manchester Medium Total of D2:D11 where B is "North" and C is "Manchester".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Deals won for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.4 HR Attendance and Payroll Sheet 21. Real-World Project Briefs · 12 tasks

  1. Total leave days Easy Add up Leave days in D2:D11. (sheet "Data")
  2. Average salary Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Sales Easy How many rows have Department "Sales"? (sheet "Data")
  4. Largest leave days Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Leave days for Sales Medium Total of D2:D11 where B is "Sales". (sheet "Data")
  10. Sales in Denver Medium Total of D2:D11 where B is "Sales" and C is "Denver". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: 21.5 GST Invoice Register 21. Real-World Project Briefs · 13 tasks

  1. Total invoices Easy Add up Invoices in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin Easy How many rows have Department "Admin"?
  4. Largest invoices Easy The largest value in D2:D11.
  5. Invoices for Admin Medium Total of D2:D11 where B is "Admin".
  6. Admin in Berlin Medium Total of D2:D11 where B is "Admin" and C is "Berlin".
  7. Best in Admin Medium Largest E2:E11 value where B is "Admin".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Invoices for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Admin Hard Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.6 Bank Reconciliation 21. Real-World Project Briefs · 13 tasks

  1. Total tenure months Easy Add up Tenure months in D2:D11.
  2. Average loan amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Home Easy How many rows have Loan type "Home"?
  4. Largest tenure months Easy The largest value in D2:D11.
  5. Tenure months for Home Medium Total of D2:D11 where B is "Home".
  6. Home in Chennai Medium Total of D2:D11 where B is "Home" and C is "Chennai".
  7. Best in Home Medium Largest E2:E11 value where B is "Home".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Tenure months for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Home Hard Share of D2:D11 that is "Home", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.7 Marketing Campaign Performance 21. Real-World Project Briefs · 13 tasks

  1. Total clicks Easy Add up Clicks in D2:D11.
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google Easy How many rows have Platform "Google"?
  4. Largest clicks Easy The largest value in D2:D11.
  5. Clicks for Google Medium Total of D2:D11 where B is "Google".
  6. Google in Milan Medium Total of D2:D11 where B is "Google" and C is "Milan".
  7. Best in Google Medium Largest E2:E11 value where B is "Google".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Clicks for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Google Hard Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.8 Customer Repeat and Cohort Table 21. Real-World Project Briefs · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Bristol Medium Total of D2:D11 where B is "Royal Mail" and C is "Bristol".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Parcels for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.9 One-Page Executive MIS 21. Real-World Project Briefs · 13 tasks

  1. Total seats Easy Add up Seats in D2:D11.
  2. Average price per seat Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Basic Easy How many rows have Plan "Basic"?
  4. Largest seats Easy The largest value in D2:D11.
  5. Seats for Basic Medium Total of D2:D11 where B is "Basic".
  6. Basic in Austin Medium Total of D2:D11 where B is "Basic" and C is "Austin".
  7. Best in Basic Medium Largest E2:E11 value where B is "Basic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Seats for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Basic Hard Share of D2:D11 that is "Basic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.10 Multi-Outlet Roll-up 21. Real-World Project Briefs · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Dublin Medium Total of D2:D11 where B is "Antibiotic" and C is "Dublin".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up James Carter Medium The Stock units for "James Carter" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 21.11 Salary Slip Creation 21. Real-World Project Briefs · 13 tasks

  1. Total leave days Easy Add up Leave days in D2:D11.
  2. Average salary Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Sales Easy How many rows have Department "Sales"?
  4. Largest leave days Easy The largest value in D2:D11.
  5. Leave days for Sales Medium Total of D2:D11 where B is "Sales".
  6. Sales in Denver Medium Total of D2:D11 where B is "Sales" and C is "Denver".
  7. Best in Sales Medium Largest E2:E11 value where B is "Sales".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Noah Wilson Medium The Leave days for "Noah Wilson" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Sales Hard Share of D2:D11 that is "Sales", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 23.1 Referencing Another Sheet 23. Formulas Across Sheets and Workbooks · 11 tasks

  1. Madrid sales in Jan Easy Get Jan!C2.
  2. Seville covers in Mar Easy Get Mar!B5.
  3. Total Jan sales Easy Total of Jan!C2:C5.
  4. Total Feb covers Easy Total of Feb!B2:B5.
  5. Madrid in 3 months Medium Jan!C2 + Feb!C2 + Mar!C2.
  6. Change Jan to Feb Medium Feb!C3 minus Jan!C3.
  7. Average Mar sales Medium Average of Mar!C2:C5.
  8. Jan target met? Medium Is the total of C2 to C5 on the Jan sheet at least B2? (TRUE or FALSE)
  9. Quarter sales Hard Total sales of Jan, Feb and Mar.
  10. Mar bonus Hard Mar total sales × B3.
  11. Sales per cover in Feb Hard Total Feb sales ÷ total Feb covers, rounded to 2 decimals.

Practice: 23.2 Referencing a Normal Range on Another Sheet 23. Formulas Across Sheets and Workbooks · 11 tasks

  1. Total units Easy Total of Orders!D2:D8.
  2. Total value Easy Total of Orders!E2:E8.
  3. Count orders Easy Count Orders!A2:A8.
  4. Biggest order Easy Biggest value in Orders!E2:E8.
  5. North units Medium Units for A2 (North).
  6. South value Medium Value for A3 (South).
  7. West order count Medium How many orders for A4 (West)?
  8. Insulin units Medium Units of "Insulin".
  9. North insulin value Hard Value for North AND Insulin.
  10. Big South orders Hard How many South orders have value over 50000?
  11. North share of value Hard North value ÷ total value.

Practice: Practice: Quarter Report Across Sheets 23. Formulas Across Sheets and Workbooks · 11 tasks

  1. CRM Pro in April Easy Get Apr!C2.
  2. Total April revenue Easy Total of Apr!C2:C4.
  3. Total June licences Easy Total of Jun!B2:B4.
  4. HR Suite in May Easy Get May!C3.
  5. CRM Pro quarter Medium Apr!C2 + May!C2 + Jun!C2.
  6. Cloud Backup quarter Medium Apr!C4 + May!C4 + Jun!C4.
  7. Growth Apr to Jun Medium Total Jun revenue minus total Apr revenue.
  8. Best month for HR Suite Medium Biggest of Apr!C3, May!C3, Jun!C3.
  9. Quarter revenue Hard Total revenue Apr + May + Jun.
  10. Average price in May Hard Total May revenue ÷ total May licences.
  11. CRM share of June Hard Jun!C2 ÷ total June revenue.

Practice: 26.1 Fast Data Entry Tricks 26. Excel Tricks Every Analyst Should Know · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Delhi Medium Total of D2:D11 where B is "Snacks" and C is "Delhi".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Units sold for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 26.2 Data Cleaning Tricks 26. Excel Tricks Every Analyst Should Know · 11 tasks

  1. Clean a name Easy Clean A2.
  2. Clean another name Easy Clean A4.
  3. Yes/No in one style Easy Clean D4 into proper case.
  4. Fee as a number Easy Change C2 into a number.
  5. Phone digits only Medium Clean E3.
  6. Paid as TRUE/FALSE Medium Is D4 a yes? (TRUE or FALSE)
  7. How many paid? Medium Count D2:D6 that are "yes" after cleaning.
  8. Total fees Medium Total of C2:C6 as numbers.
  9. Fees not yet paid Hard Total fee where Paid is "no" after cleaning.
  10. Reminder line Hard Make "Naina Kapoor (9-B): pay 21000" from row 3.
  11. Unpaid students (spill) Hard Cleaned names where Paid is "no".

Practice: 26.3 Selecting and Navigating Tricks 26. Excel Tricks Every Analyst Should Know · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Bristol Medium Total of D2:D11 where B is "North" and C is "Bristol".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up James Carter Medium The Deals won for "James Carter" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 26.4 Formula Tricks 26. Excel Tricks Every Analyst Should Know · 11 tasks

  1. Net sales Easy C2 minus D2.
  2. Fee with $ Easy Fee on C3 using $F$2.
  3. Total sales Easy Total of C2:C6.
  4. Return rate Easy D4 ÷ C4.
  5. Payout Medium Payout for row 5: C5-D5-C5*$F$2.
  6. Rounded fee Medium Fee on C6 with $F$2, rounded to 0 decimals.
  7. Safe return rate Medium D3 ÷ C3, or 0 if error.
  8. Total returns Medium Total of D2:D6.
  9. Total payout Hard SUM of (C2:C6 - D2:D6 - C2:C6*$F$2).
  10. Overall return rate Hard Total of D2 to D6 divided by the total of C2 to C6.
  11. Sellers with 10% returns Hard How many sellers have returns of 10% or more?

Practice: 26.5 Lookup and Logic Tricks 26. Excel Tricks Every Analyst Should Know · 11 tasks

  1. Name of C-104 Easy Find the name for I3.
  2. Customers in the city Easy How many rows have City = I2?
  3. Plan of C-104 Easy Find the plan for I3.
  4. Heavy user? Easy Show "Heavy" if E6 is over 50, else "Normal".
  5. Not found message Medium Find the name for "C-999", or "Not found".
  6. Is C-103 a duplicate? Medium Does C-103 appear more than once? (TRUE or FALSE)
  7. Zero usage flag Medium Is row 5 a Basic plan AND 0 GB? (TRUE or FALSE)
  8. Data in the city Medium Total GB where City = I2.
  9. Data in the city, no repeats Hard Total GB for the city in I2, but count the repeated C-103 row (row 7) only once.
  10. Plan label Hard For D3 show "Starter" for Basic, "Mid" for Plus, "Top" for Unlimited.
  11. Last row of C-103 Hard Data used for the LAST C-103.

Practice: 26.6 Formatting Tricks 26. Excel Tricks Every Analyst Should Know · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Denver Medium Total of D2:D11 where B is "Cloud" and C is "Denver".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Lucas Rossi Medium The Billable hours for "Lucas Rossi" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 26.7 Table, Sorting and PivotTable Tricks 26. Excel Tricks Every Analyst Should Know · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Google".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Meta", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Google" or "Meta".
  11. Names joined Hard Names where B is "Google", joined with ", ".

Practice: Practice: Clean and Summarise a Sheet 26. Excel Tricks Every Analyst Should Know · 11 tasks

  1. Booking value Easy D2 × E2.
  2. Is TK-02 repeated? Easy Does TK-02 appear more than once?
  3. Total seats Easy Total of D2:D7.
  4. Shows list (spill) Easy UNIQUE of B2:B7.
  5. Paid revenue Medium Total seats × price where Status = "Paid".
  6. Different bookings Medium How many different Ticket IDs?
  7. Hamilton seats Medium Seats for "Hamilton".
  8. Duplicate or first (row 5) Medium Is A5 a "Duplicate" or "First"?
  9. Paid revenue without the repeat Hard Paid revenue minus the repeat row (D5*E5).
  10. Clean rows (spill) Hard UNIQUE of A2:F7.
  11. Seats on 6 Dec Hard Seats on the date in C3.

Practice: 27.1 Retail and Quick Commerce: City Sales and On-Time Delivery 27. 20 Industry Projects · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in London Medium Total of D2:D11 where B is "Royal Mail" and C is "London".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Rohan Iyer Medium The Parcels for "Rohan Iyer" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.2 E-commerce: Gross Sales, Returns and Net Sales 27. 20 Industry Projects · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Manchester Medium Total of D2:D11 where B is "North" and C is "Manchester".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Deals won for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.3 Sales: Target vs Actual by Sales Rep 27. 20 Industry Projects · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Bristol Medium Total of D2:D11 where B is "North" and C is "Bristol".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ishaan Verma Medium The Deals won for "Ishaan Verma" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.4 Finance and Accounting: Budget vs Actual Variance 27. 20 Industry Projects · 11 tasks

  1. Median Easy Median of D2:D11.
  2. Range Easy Max of E2:E11 minus min.
  3. Count Easy Count of numbers in D2:D11.
  4. Spread Medium STDEV.S of D2:D11, rounded to 2 decimals.
  5. 90th percentile Medium 90th percentile of E2:E11, rounded to 1 decimal.
  6. Upper quartile Medium Third quartile of D2:D11, rounded to 2 decimals.
  7. Third largest Medium Third largest in E2:E11.
  8. Correlation Hard Correlation of D2:D11 and E2:E11, rounded to 3 decimals.
  9. Z-score Hard Z-score of D2 vs D2:D11, rounded to 2 decimals.
  10. Coefficient of variation Hard STDEV.S ÷ average of E2:E11, rounded to 3 decimals.
  11. Outliers above Hard How many in D2:D11 exceed average + 1 standard deviation?

Practice: 27.5 Banking: Loan Book and NPA Tracker 27. 20 Industry Projects · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: 27.6 Insurance: Premium, Claims and Loss Ratio 27. 20 Industry Projects · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: 27.7 Healthcare: Hospital Revenue and Length of Stay 27. 20 Industry Projects · 13 tasks

  1. Total days admitted Easy Add up Days admitted in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cardiology Easy How many rows have Department "Cardiology"?
  4. Largest days admitted Easy The largest value in D2:D11.
  5. Days admitted for Cardiology Medium Total of D2:D11 where B is "Cardiology".
  6. Cardiology in Mumbai Medium Total of D2:D11 where B is "Cardiology" and C is "Mumbai".
  7. Best in Cardiology Medium Largest E2:E11 value where B is "Cardiology".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sophie Martin Medium The Days admitted for "Sophie Martin" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cardiology Hard Share of D2:D11 that is "Cardiology", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.8 Pharma: Stock Value and Near-Expiry Batches 27. 20 Industry Projects · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Madrid Medium Total of D2:D11 where B is "Antibiotic" and C is "Madrid".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Stock units for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.9 Manufacturing: Production Attainment and Rejects 27. 20 Industry Projects · 13 tasks

  1. Total units made Easy Add up Units made in D2:D11.
  2. Average rejects Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Line A Easy How many rows have Line "Line A"?
  4. Largest units made Easy The largest value in D2:D11.
  5. Units made for Line A Medium Total of D2:D11 where B is "Line A".
  6. Line A in Pune Medium Total of D2:D11 where B is "Line A" and C is "Pune".
  7. Best in Line A Medium Largest E2:E11 value where B is "Line A".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Units made for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Line A Hard Share of D2:D11 that is "Line A", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.10 Inventory and Supply Chain: Days of Cover and Reorder List 27. 20 Industry Projects · 12 tasks

  1. Total stock units Easy Add up Stock units in D2:D11. (sheet "Data")
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"? (sheet "Data")
  4. Largest stock units Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic". (sheet "Data")
  10. Antibiotic in Madrid Medium Total of D2:D11 where B is "Antibiotic" and C is "Madrid". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: 27.11 Logistics: On-Time Delivery by Route 27. 20 Industry Projects · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in London Medium Total of D2:D11 where B is "Royal Mail" and C is "London".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Parcels for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.12 HR: Headcount, Attrition and Salary 27. 20 Industry Projects · 13 tasks

  1. Total leave days Easy Add up Leave days in D2:D11.
  2. Average salary Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Sales Easy How many rows have Department "Sales"?
  4. Largest leave days Easy The largest value in D2:D11.
  5. Leave days for Sales Medium Total of D2:D11 where B is "Sales".
  6. Sales in Seattle Medium Total of D2:D11 where B is "Sales" and C is "Seattle".
  7. Best in Sales Medium Largest E2:E11 value where B is "Sales".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sophie Martin Medium The Leave days for "Sophie Martin" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Sales Hard Share of D2:D11 that is "Sales", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.13 Marketing and Digital Ads: Campaign ROAS and Cost per Order 27. 20 Industry Projects · 13 tasks

  1. Total clicks Easy Add up Clicks in D2:D11.
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google Easy How many rows have Platform "Google"?
  4. Largest clicks Easy The largest value in D2:D11.
  5. Clicks for Google Medium Total of D2:D11 where B is "Google".
  6. Google in Madrid Medium Total of D2:D11 where B is "Google" and C is "Madrid".
  7. Best in Google Medium Largest E2:E11 value where B is "Google".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Clicks for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Google Hard Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.14 Education: Pass Rate, Scores and Attendance 27. 20 Industry Projects · 12 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail". (sheet "Scores")
  2. Total Easy B2 plus C2. (sheet "Scores")
  3. Passed score 1 Easy How many in B2:B11 are 40 or more? (sheet "Scores")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Passed both Medium TRUE if B3 and C3 are both at least 40. (sheet "Scores")
  9. Passed either Medium TRUE if B4 or C4 is at least 75. (sheet "Scores")
  10. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F". (sheet "Scores")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: 27.15 Hospitality: Occupancy, ADR and RevPAR 27. 20 Industry Projects · 13 tasks

  1. Total days admitted Easy Add up Days admitted in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cardiology Easy How many rows have Department "Cardiology"?
  4. Largest days admitted Easy The largest value in D2:D11.
  5. Days admitted for Cardiology Medium Total of D2:D11 where B is "Cardiology".
  6. Cardiology in Pune Medium Total of D2:D11 where B is "Cardiology" and C is "Pune".
  7. Best in Cardiology Medium Largest E2:E11 value where B is "Cardiology".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Days admitted for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cardiology Hard Share of D2:D11 that is "Cardiology", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.16 Restaurants: Spend per Cover and Food Cost 27. 20 Industry Projects · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Seattle Medium Total of D2:D11 where B is "Downtown" and C is "Seattle".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Covers for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.17 Real Estate: Units Sold and Price per Square Foot 27. 20 Industry Projects · 13 tasks

  1. Total area sq ft Easy Add up Area sq ft in D2:D11.
  2. Average price per sq ft Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Tower 1 Easy How many rows have Tower "Tower 1"?
  4. Largest area sq ft Easy The largest value in D2:D11.
  5. Area sq ft for Tower 1 Medium Total of D2:D11 where B is "Tower 1".
  6. Tower 1 in Delhi Medium Total of D2:D11 where B is "Tower 1" and C is "Delhi".
  7. Best in Tower 1 Medium Largest E2:E11 value where B is "Tower 1".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Area sq ft for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Tower 1 Hard Share of D2:D11 that is "Tower 1", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.18 Telecom: Churn, ARPU and Data Usage 27. 20 Industry Projects · 13 tasks

  1. Total data gb Easy Add up Data GB in D2:D11.
  2. Average monthly bill Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Prepaid Easy How many rows have Plan "Prepaid"?
  4. Largest data gb Easy The largest value in D2:D11.
  5. Data GB for Prepaid Medium Total of D2:D11 where B is "Prepaid".
  6. Prepaid in Paris Medium Total of D2:D11 where B is "Prepaid" and C is "Paris".
  7. Best in Prepaid Medium Largest E2:E11 value where B is "Prepaid".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Data GB for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Prepaid Hard Share of D2:D11 that is "Prepaid", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.19 IT Services: Utilisation and Billing 27. 20 Industry Projects · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Denver Medium Total of D2:D11 where B is "Cloud" and C is "Denver".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Billable hours for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 27.20 Government and NGO: Fund Utilisation and Cost per Beneficiary 27. 20 Industry Projects · 13 tasks

  1. Total donations Easy Add up Donations in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Education Easy How many rows have Fund "Education"?
  4. Largest donations Easy The largest value in D2:D11.
  5. Donations for Education Medium Total of D2:D11 where B is "Education".
  6. Education in Delhi Medium Total of D2:D11 where B is "Education" and C is "Delhi".
  7. Best in Education Medium Largest E2:E11 value where B is "Education".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Emma Schmidt Medium The Donations for "Emma Schmidt" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Education Hard Share of D2:D11 that is "Education", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Practice and Answer Key: Cloud Kitchen Weekly Report 27. 20 Industry Projects · 13 tasks

  1. Total seats Easy Add up Seats in D2:D11.
  2. Average price per seat Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Basic Easy How many rows have Plan "Basic"?
  4. Largest seats Easy The largest value in D2:D11.
  5. Seats for Basic Medium Total of D2:D11 where B is "Basic".
  6. Basic in Chicago Medium Total of D2:D11 where B is "Basic" and C is "Chicago".
  7. Best in Basic Medium Largest E2:E11 value where B is "Basic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Seats for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Basic Hard Share of D2:D11 that is "Basic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: 30.1 Descriptive Statistics with Functions 30. Statistics for Analysts · 11 tasks

  1. Mean basket Easy Average of C2:C11.
  2. Median basket Easy Median of C2:C11.
  3. Range Easy Max of C2:C11 minus min of C2:C11.
  4. Order count Easy How many basket values are there?
  5. Sample standard deviation Medium Sample standard deviation of C2:C11, rounded to 2 decimals.
  6. Sample variance Medium Sample variance of C2:C11, rounded to 1 decimal.
  7. First quartile Medium Quartile 1 of C2:C11.
  8. Interquartile range Medium Quartile 3 minus quartile 1 of C2:C11.
  9. Coefficient of variation Hard Sample SD ÷ mean of C2:C11, rounded to 3 decimals.
  10. Skewed right? Hard Is the mean of C2:C11 bigger than the median? (TRUE or FALSE)
  11. Skewness Hard Skewness of C2:C11, rounded to 2 decimals.

Practice: 30.2 The Analysis ToolPak: Descriptive Statistics Output 30. Statistics for Analysts · 10 tasks

  1. Mean Easy Average spend in D2:D10, rounded to 2 decimals.
  2. Sum Easy Total spend in D2:D10.
  3. Count Easy How many spends are in D2:D10?
  4. Minimum Easy Smallest spend.
  5. Median Medium Median spend.
  6. Standard deviation Medium Sample SD of D2:D10, rounded to 2 decimals.
  7. Sample variance Medium Sample variance of D2:D10, rounded to 0 decimals.
  8. Standard error Hard Sample SD of D2:D10 ÷ square root of the count, rounded to 2 decimals.
  9. Range Hard Max spend minus min spend.
  10. Mean rating Hard Average rating in E2:E10, rounded to 2 decimals.

Practice: 30.3 Distributions, Histograms, NORM.DIST and Outliers 30. Statistics for Analysts · 10 tasks

  1. Parcels up to 12 hours Easy How many hours in C2:C13 are at most 12?
  2. Parcels 12 to 24 hours Easy How many hours are above 12 and at most 24?
  3. All bin counts (spill) Medium For each bin row 2 to 5, count hours above D and at most E.
  4. Over 48 hours Medium How many hours are above 48?
  5. Z-score of P11 Medium Z-score of C12 using the mean and sample SD of C2:C13, rounded to 2 decimals.
  6. Upper fence Medium Quartile 3 + 1.5 × (Q3 − Q1) of C2:C13.
  7. Outlier count Hard How many hours are above the upper fence?
  8. Chance under 24 hours Hard Normal probability of a value up to 24 with the mean and sample SD of C2:C13, rounded to 3 decimals.
  9. Chance over 36 hours Hard Normal probability of a value above 36 (same mean and SD), rounded to 3 decimals.
  10. 90th percentile Hard 90th percentile of C2:C13, rounded to 1 decimal.

Practice: 30.4 CORREL and a Correlation Matrix 30. Statistics for Analysts · 10 tasks

  1. Spend vs orders Easy Correlation of B2:B9 and D2:D9, rounded to 3 decimals.
  2. Spend vs visitors Easy Correlation of B2:B9 and C2:C9, rounded to 3 decimals.
  3. Visitors vs orders Easy Correlation of C2:C9 and D2:D9, rounded to 3 decimals.
  4. Spend vs returns Medium Correlation of B2:B9 and E2:E9, rounded to 3 decimals.
  5. Negative link? Medium Is the correlation of B2:B9 and E2:E9 below 0? (TRUE or FALSE)
  6. Strong link? Medium Is the size of the correlation of C2:C9 and D2:D9 above 0.8? (TRUE or FALSE)
  7. R squared Medium Correlation of B2:B9 and D2:D9, squared, rounded to 3 decimals.
  8. Same with RSQ Hard R squared of orders D2:D9 on spend B2:B9, rounded to 3 decimals.
  9. Covariance Hard Sample covariance of B2:B9 and D2:D9, rounded to 1 decimal.
  10. Order rate vs spend Hard Correlation of spend B2:B9 with orders ÷ visitors, rounded to 3 decimals.

Practice: 30.5 Simple Regression with SLOPE and INTERCEPT 30. Statistics for Analysts · 10 tasks

  1. Slope Easy Slope of orders C2:C9 on spend B2:B9, rounded to 3 decimals.
  2. Intercept Easy Intercept of orders C2:C9 on spend B2:B9, rounded to 2 decimals.
  3. R squared Easy R squared of C2:C9 on B2:B9, rounded to 3 decimals.
  4. Predict for ₹50k Medium Predicted orders for spend B10 with FORECAST.LINEAR, rounded to 0 decimals.
  5. Predict by hand Medium Intercept + slope × B10, rounded to 0 decimals.
  6. Fitted value week 3 Medium Predicted orders for B4, rounded to 1 decimal.
  7. Residual week 3 Medium C4 minus the predicted orders for B4, rounded to 1 decimal.
  8. Spend for 600 orders Hard (600 − intercept) ÷ slope, rounded to 1 decimal.
  9. Pune-only average orders Hard Average orders where City is "Pune".
  10. Orders per ₹1,000 overall Hard Total orders ÷ total spend, rounded to 2 decimals.

Practice: 30.6 The ToolPak Regression Output 30. Statistics for Analysts · 10 tasks

  1. Multiple R Easy Correlation of B2:B11 and C2:C11, rounded to 4 decimals.
  2. R Square Easy R squared of C2:C11 on B2:B11, rounded to 4 decimals.
  3. Observations Easy How many months of sales are there?
  4. X coefficient Medium Slope of C2:C11 on B2:B11, rounded to 4 decimals.
  5. Intercept coefficient Medium Intercept of C2:C11 on B2:B11, rounded to 4 decimals.
  6. Adjusted R Square Medium 1 − (1 − R²) × (n − 1) ÷ (n − 2), rounded to 4 decimals.
  7. Standard error of estimate Hard STEYX of C2:C11 on B2:B11, rounded to 3 decimals.
  8. Total sum of squares Hard DEVSQ of C2:C11, rounded to 1 decimal.
  9. Regression SS Hard R squared × DEVSQ of C2:C11, rounded to 1 decimal.
  10. Predict £20k month Hard Predicted sales for marketing of 20, rounded to 1 decimal.

Practice: 30.7 T.TEST and an A/B Test 30. Statistics for Analysts · 10 tasks

  1. Old page mean Easy Average of A2:A11.
  2. New page mean Easy Average of B2:B11.
  3. Lift in dollars Easy Average of B2:B11 minus average of A2:A11.
  4. Lift % Medium Lift ÷ average of A2:A11, rounded to 3 decimals.
  5. p-value (equal variance) Medium T.TEST of A2:A11 and B2:B11, 2 tails, type 2, rounded to 4 decimals.
  6. p-value (unequal variance) Medium T.TEST with 2 tails and type 3, rounded to 4 decimals.
  7. Significant at 5%? Medium Is the type 3 two-tailed p-value below 0.05? (TRUE or FALSE)
  8. Paired p-value Hard T.TEST with 2 tails and type 1 (paired), rounded to 5 decimals.
  9. Pairs where new won Hard How many rows have New page above Old page?
  10. Verdict text Hard Show "Launch" if the type 3 p-value is below 0.05, else "Keep testing".

Practice: 30.8 CHISQ.TEST: Comparing Conversion Rates 30. Statistics for Analysts · 10 tasks

  1. Rate A Easy B2 ÷ D2.
  2. Rate B Easy B3 ÷ D3.
  3. Overall rate Easy Total bought ÷ total visitors.
  4. Expected buyers A Medium D2 × pooled rate.
  5. Expected non-buyers A Medium D2 × total did-not-buy ÷ all visitors.
  6. Expected table (spill) Medium Row totals D2:D3 × column totals of B2:C3 ÷ grand total.
  7. p-value Hard CHISQ.TEST of B2:C3 against the expected table, rounded to 4 decimals.
  8. Significant? Hard Is that p-value below 0.05? (TRUE or FALSE)
  9. Relative lift Hard Rate B ÷ rate A − 1, rounded to 2 decimals.
  10. Extra buyers per 1,000 Hard (Rate B − rate A) × 1000.

Practice: 30.9 Common Statistics Mistakes (and the Fix) 30. Statistics for Analysts · 10 tasks

  1. Mean sales Easy Average of C2:C10, rounded to 1 decimal.
  2. Median sales Easy Median of C2:C10.
  3. Mean without the outlier Medium Average of sales below 100.
  4. Sample SD Medium Sample SD of E2:E10, rounded to 3 decimals.
  5. Population SD Medium Population SD of E2:E10, rounded to 3 decimals.
  6. Wrong: average of ratios Medium Average of C2:C10 ÷ D2:D10, rounded to 2 decimals.
  7. Right: ratio of totals Hard Total sales ÷ total staff, rounded to 2 decimals.
  8. Correlation is not cause Hard Correlation of D2:D10 and C2:C10, rounded to 3 decimals.
  9. South average sales Hard Average sales where Region is "South", rounded to 1 decimal.
  10. Small group warning Hard How many branches are in "North"?

Practice: Practice: Describe, Correlate, Predict and Test a Kolhapur Store 30. Statistics for Analysts · 10 tasks

  1. Mean orders Easy Average of C2:C11.
  2. Median basket Easy Median of D2:D11.
  3. SD of visitors Easy Sample SD of B2:B11, rounded to 1 decimal.
  4. Visitors vs orders Medium Correlation of B2:B11 and C2:C11, rounded to 3 decimals.
  5. Orders per visitor Medium Slope of C2:C11 on B2:B11, rounded to 4 decimals.
  6. Predict 450 visitors Medium Predicted orders for 450 visitors, rounded to 0 decimals.
  7. Promo average orders Medium Average orders where Promo is "Yes".
  8. Promo vs no-promo basket gap Hard Average basket with "Yes" minus with "No", rounded to 1 decimal.
  9. Conversion rate Hard Total orders ÷ total visitors, rounded to 4 decimals.
  10. Weekend z-score Hard Z-score of B7 with the mean and sample SD of B2:B11, rounded to 2 decimals.

Practice: 31.1 Forecasting Basics: Trend, Seasonality and a Clean Series 31. Forecasting in Excel · 10 tasks

  1. Weeks with data Easy How many numbers are in B2:B13?
  2. Missing weeks Easy How many cells in B2:B13 are empty?
  3. Fill week 3 Easy Average of B3 and B5.
  4. Median weekly orders Medium Median of B2:B13.
  5. Spike size Medium B7 ÷ median of B2:B13, rounded to 2 decimals.
  6. Spike flag Medium Is B7 above 1.5 × the median? (TRUE or FALSE)
  7. Trend per week Medium Slope of B8:B13 on A8:A13, rounded to 2 decimals.
  8. Growth first to last Hard B13 ÷ B2 − 1, rounded to 3 decimals.
  9. Average without spike Hard Average of B2:B13 for values below 1000, rounded to 1 decimal.
  10. Average weekly change Hard (B13 − B2) ÷ 11, rounded to 2 decimals.

Practice: 31.2 FORECAST.LINEAR: Straight-Line Forecasts 31. Forecasting in Excel · 10 tasks

  1. Month 11 forecast Easy Forecast revenue for month 11, rounded to 1 decimal.
  2. Month 12 forecast Easy Forecast for month 12, rounded to 1 decimal.
  3. Monthly growth Easy Slope of B2:B11 on A2:A11, rounded to 3 decimals.
  4. Starting level Medium Intercept of B2:B11 on A2:A11, rounded to 2 decimals.
  5. Fit quality Medium R squared, rounded to 4 decimals.
  6. Year-end forecast Medium FORECAST for 12, rounded to 1 decimal.
  7. Fitted month 10 Medium Forecast for A11, rounded to 1 decimal.
  8. Next 3 months total Hard Sum of forecasts for months 11, 12, 13, rounded to 1 decimal.
  9. When does it pass $130k? Hard (130 − intercept) ÷ slope, rounded up to a whole month.
  10. Growth % per month Hard Slope ÷ average of B2:B11, rounded to 4 decimals.

Practice: 31.6 Seasonality by Hand: Trend plus Weekday Offsets 31. Forecasting in Excel · 10 tasks

  1. Average covers Easy Average of C2:C15, rounded to 1 decimal.
  2. Saturday average Easy Average covers where Weekday is "Sat".
  3. Monday average Easy Average covers where Weekday is "Mon".
  4. Saturday offset Medium Saturday average minus overall average, rounded to 1 decimal.
  5. Daily trend Medium Slope of C2:C15 on A2:A15, rounded to 3 decimals.
  6. Trend value day 20 Medium Forecast for day 20, rounded to 1 decimal.
  7. Day 20 (Sat) forecast Hard Forecast for day 20 + Saturday offset, rounded to 0 decimals.
  8. Day 16 (Tue) forecast Hard Forecast for day 16 + Tuesday offset, rounded to 0 decimals.
  9. Weekend share Hard Covers on Fri, Sat, Sun ÷ all covers, rounded to 3 decimals.
  10. Seasonal index Fri Hard Friday average ÷ overall average, rounded to 3 decimals.

Practice: 31.7 Forecast Accuracy: MAE, MAPE, RMSE 31. Forecasting in Excel · 10 tasks

  1. Week 1 error Easy B2 − C2.
  2. Errors (spill) Easy B2:B9 − C2:C9.
  3. Total actual Easy Sum of B2:B9.
  4. MAE Medium Average of the absolute errors, rounded to 2 decimals.
  5. Bias Medium Average of B2:B9 − C2:C9, rounded to 2 decimals.
  6. MAPE Medium Average of |error| ÷ actual, rounded to 4 decimals.
  7. RMSE Hard Square root of the average squared error, rounded to 2 decimals.
  8. Weighted error % Hard Sum of absolute errors ÷ sum of B2:B9, rounded to 4 decimals.
  9. Weeks within 5% Hard How many weeks have |error| ÷ actual at most 0.05?
  10. Worst miss Hard Largest absolute error.

Practice: Practice: Forecast Weekly Orders and Test the Forecast 31. Forecasting in Excel · 10 tasks

  1. Train slope Easy Slope of B2:B9 on A2:A9, rounded to 3 decimals.
  2. Train intercept Easy Intercept of B2:B9 on A2:A9, rounded to 2 decimals.
  3. Week 9 forecast Easy Forecast for A10 using training rows, rounded to 1 decimal.
  4. Week 12 forecast Medium Forecast for A13 using training rows, rounded to 1 decimal.
  5. Week 9 error Medium B10 − week 9 forecast, rounded to 1 decimal.
  6. Test MAE Medium Average absolute error for rows 10 to 13 with intercept + slope × week, rounded to 2 decimals.
  7. Test MAPE Hard Average of |error| ÷ actual for rows 10 to 13, rounded to 4 decimals.
  8. Total test forecast Hard Sum of intercept + slope × weeks 9 to 12, rounded to 0 decimals.
  9. Week 16 forecast (all data) Hard Forecast for week 16 using B2:B13, rounded to 0 decimals.
  10. Good enough? Hard Is the test MAPE below 0.02? (TRUE or FALSE)

Practice: Excel Basics for Beginners: Workbook, Sheets, Cells, AutoFill and Date Series Excel Guides & Daily-Life Articles · 12 tasks

  1. Total deals won Easy Add up Deals won in D2:D11. (sheet "Data")
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in North Easy How many rows have Region "North"? (sheet "Data")
  4. Largest deals won Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Deals won for North Medium Total of D2:D11 where B is "North". (sheet "Data")
  10. North in London Medium Total of D2:D11 where B is "North" and C is "London". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Excel Flash Fill (Ctrl+E) with Real Examples: Names, Emails, Phone Numbers and Masking Excel Guides & Daily-Life Articles · 12 tasks

  1. Total nights Easy Add up Nights in D2:D11. (sheet "Data")
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Standard Easy How many rows have Room type "Standard"? (sheet "Data")
  4. Largest nights Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Nights for Standard Medium Total of D2:D11 where B is "Standard". (sheet "Data")
  10. Standard in Leeds Medium Total of D2:D11 where B is "Standard" and C is "Leeds". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: Excel COUNT, COUNTA, COUNTBLANK and COUNTIF: Total Students, Absentees and Pass Percentage (with MAX and MIN) Excel Guides & Daily-Life Articles · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: Excel IF, Nested IF and AND Function: Pass or Fail in All Subjects Excel Guides & Daily-Life Articles · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: Relative vs Absolute vs Mixed Cell Reference in Excel (F4 Key) with GST Example Excel Guides & Daily-Life Articles · 13 tasks

  1. Total invoices Easy Add up Invoices in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin Easy How many rows have Department "Admin"?
  4. Largest invoices Easy The largest value in D2:D11.
  5. Invoices for Admin Medium Total of D2:D11 where B is "Admin".
  6. Admin in Berlin Medium Total of D2:D11 where B is "Admin" and C is "Berlin".
  7. Best in Admin Medium Largest E2:E11 value where B is "Admin".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Invoices for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Admin Hard Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Excel IFS Function: Student Grades A+, A, B, C with Total and Percentage (IFS vs Nested IF) Excel Guides & Daily-Life Articles · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: Excel VLOOKUP Explained: Exact Match, Column Number, Duplicates and #N/A (with HLOOKUP and TRANSPOSE) Excel Guides & Daily-Life Articles · 11 tasks

  1. VLOOKUP name Easy Name for ID "M103" with VLOOKUP.
  2. XLOOKUP value Easy The Unit cost for ID "M103".
  3. Position Easy Position of "Mateo Garcia" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Mateo Garcia" using INDEX and MATCH.
  5. Not found message Medium Stock units for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "M103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Mateo Garcia" and column "Stock units".
  11. Lookup the top Hard Name with the largest Stock units.

Practice: Excel XLOOKUP Tutorial: Search Right to Left, Last Match, Not Found Message and Drop-Down List Excel Guides & Daily-Life Articles · 12 tasks

  1. VLOOKUP name Easy Name for ID "S103" with VLOOKUP. (sheet "Lookup")
  2. XLOOKUP value Easy The Monthly bill for ID "S103". (sheet "Lookup")
  3. Position Easy Position of "Neha Gupta" in B2:B9. (sheet "Lookup")
  4. Clean name Easy Trim A2. (sheet "Text")
  5. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  6. Code prefix Easy First 3 characters of B2. (sheet "Text")
  7. Code number Easy Last 4 characters of B3. (sheet "Text")
  8. INDEX and MATCH Medium ID for name "Neha Gupta" using INDEX and MATCH. (sheet "Lookup")
  9. Not found message Medium Data GB for ID "Z999", or "Not found". (sheet "Lookup")
  10. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing". (sheet "Lookup")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: Excel INDEX and MATCH Function Explained (with XMATCH): Lookup Without XLOOKUP Excel Guides & Daily-Life Articles · 11 tasks

  1. VLOOKUP name Easy Name for ID "P103" with VLOOKUP.
  2. XLOOKUP value Easy The Price for ID "P103".
  3. Position Easy Position of "Emma Schmidt" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Emma Schmidt" using INDEX and MATCH.
  5. Not found message Medium Units sold for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "P103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Emma Schmidt" and column "Units sold".
  11. Lookup the top Hard Name with the largest Units sold.

Practice: Excel FILTER Function: Return All Matching Rows, Multiple Conditions, Starts With and #SPILL! Error Excel Guides & Daily-Life Articles · 12 tasks

  1. VLOOKUP name Easy Name for ID "O103" with VLOOKUP. (sheet "Lookup")
  2. XLOOKUP value Easy The Unit price for ID "O103". (sheet "Lookup")
  3. Position Easy Position of "Emma Schmidt" in B2:B9. (sheet "Lookup")
  4. Distinct groups Easy How many different values in B2:B11? (sheet "Arrays")
  5. List groups Easy Unique list of B2:B11. (sheet "Arrays")
  6. Filter names Easy Names in A2:A11 where B is "Website". (sheet "Arrays")
  7. INDEX and MATCH Medium ID for name "Emma Schmidt" using INDEX and MATCH. (sheet "Lookup")
  8. Not found message Medium Quantity for ID "Z999", or "Not found". (sheet "Lookup")
  9. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing". (sheet "Lookup")
  10. Sum of filtered Medium Sum of D2:D11 where B is "App", using FILTER. (sheet "Arrays")
  11. Count filtered Medium Rows where D is above the average of D2:D11. (sheet "Arrays")
  12. Sorted values Medium D2:D11 sorted largest first. (sheet "Arrays")

Practice: Analyse Blinkit Orders in Excel: Total Orders, Cities, Products and Payment Mode with COUNTA, UNIQUE and COUNTIF Excel Guides & Daily-Life Articles · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Snacks".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Dairy", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Snacks" or "Dairy".
  11. Names joined Hard Names where B is "Snacks", joined with ", ".

Practice: Excel SORT, FILTER and TAKE Together: Top 3 Profit Orders and Biggest Losses by City Excel Guides & Daily-Life Articles · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Standard".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Deluxe", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Standard" or "Deluxe".
  11. Names joined Hard Names where B is "Standard", joined with ", ".

Practice: Excel SUMIF, SUMIFS, COUNTIFS, AVERAGEIF(S) and MAXIFS: Profit Analysis of Delivered Orders Excel Guides & Daily-Life Articles · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Leeds Medium Total of D2:D11 where B is "Royal Mail" and C is "Leeds".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Parcels for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: How to Highlight an Entire Row in Excel with Conditional Formatting (Formula Rule) Excel Guides & Daily-Life Articles · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: Employee Salary Calculation in Excel: Basic, HRA, DA, TA, Gross, PF, Net Salary and CTC Excel Guides & Daily-Life Articles · 13 tasks

  1. Total leave days Easy Add up Leave days in D2:D11.
  2. Average salary Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Sales Easy How many rows have Department "Sales"?
  4. Largest leave days Easy The largest value in D2:D11.
  5. Leave days for Sales Medium Total of D2:D11 where B is "Sales".
  6. Sales in Denver Medium Total of D2:D11 where B is "Sales" and C is "Denver".
  7. Best in Sales Medium Largest E2:E11 value where B is "Sales".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Leave days for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Sales Hard Share of D2:D11 that is "Sales", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Excel Data Validation Tutorial: Allow Only Numbers, Dates and Text Length with Input Message and Error Alert Excel Guides & Daily-Life Articles · 12 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail". (sheet "Scores")
  2. Total Easy B2 plus C2. (sheet "Scores")
  3. Passed score 1 Easy How many in B2:B11 are 40 or more? (sheet "Scores")
  4. Clean name Easy Trim A2. (sheet "Text")
  5. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  6. Code prefix Easy First 3 characters of B2. (sheet "Text")
  7. Code number Easy Last 4 characters of B3. (sheet "Text")
  8. Passed both Medium TRUE if B3 and C3 are both at least 40. (sheet "Scores")
  9. Passed either Medium TRUE if B4 or C4 is at least 75. (sheet "Scores")
  10. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F". (sheet "Scores")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: Split Quantity and Unit in Excel (2 kg, 500 gram) with TEXTBEFORE, TEXTAFTER and NUMBERVALUE: Convert Grams to Kg Excel Guides & Daily-Life Articles · 12 tasks

  1. Total units sold Easy Add up Units sold in D2:D11. (sheet "Data")
  2. Average price Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Snacks Easy How many rows have Category "Snacks"? (sheet "Data")
  4. Largest units sold Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks". (sheet "Data")
  10. Snacks in Mumbai Medium Total of D2:D11 where B is "Snacks" and C is "Mumbai". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: Excel Structured References Explained: [@Column] This Row, Calculated Columns and Table Name Inside vs Outside the Table Excel Guides & Daily-Life Articles · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Leeds Medium Total of D2:D11 where B is "North" and C is "Leeds".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Hannah Lee Medium The Deals won for "Hannah Lee" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: SUBTOTAL vs SUM in Excel: Ignore Hidden and Filtered Rows (Function Codes 9 vs 109) and the Table Total Row Excel Guides & Daily-Life Articles · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Admin".
  4. Sum of filtered Medium Sum of D2:D11 where B is "IT", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Admin" or "IT".
  11. Names joined Hard Names where B is "Admin", joined with ", ".

Practice: How to Create a PivotTable in Excel for Beginners: Rows, Values, Count vs Sum and Report Filter (Blinkit Orders Example) Excel Guides & Daily-Life Articles · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Snacks".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Dairy", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Snacks" or "Dairy".
  11. Names joined Hard Names where B is "Snacks", joined with ", ".

Practice: Excel PivotTable with Multiple Values, Nested Rows and Columns: Count and Sum Side by Side, City by Payment Mode Excel Guides & Daily-Life Articles · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Manchester Medium Total of D2:D11 where B is "Royal Mail" and C is "Manchester".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Parcels for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: How to Make a Monthly Home Budget in Excel (Planned vs Actual, Savings and Overspending Alerts) Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Daily Expense Tracker in Excel: Log Spending by Category and See Monthly Totals Automatically Excel Guides & Daily-Life Articles · 12 tasks

  1. Total times Easy Add up Times in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Food Easy How many rows have Category "Food"? (sheet "Data")
  4. Largest times Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Times for Food Medium Total of D2:D11 where B is "Food". (sheet "Data")
  10. Food in Delhi Medium Total of D2:D11 where B is "Food" and C is "Delhi". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: How to Calculate Home Loan EMI in Excel with PMT (and Make a Month-by-Month Repayment Schedule) Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Bill Payment Reminder in Excel: Highlight Due and Overdue Bills Automatically with TODAY Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Grocery List in Excel with Prices: Auto Total, Category Drop-Down and Tick Items as You Shop Excel Guides & Daily-Life Articles · 11 tasks

  1. VLOOKUP name Easy Name for ID "P103" with VLOOKUP.
  2. XLOOKUP value Easy The Price for ID "P103".
  3. Position Easy Position of "Neha Gupta" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Neha Gupta" using INDEX and MATCH.
  5. Not found message Medium Units sold for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "P103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Neha Gupta" and column "Units sold".
  11. Lookup the top Hard Name with the largest Units sold.

Practice: Savings Goal Calculator in Excel: How Much to Save Each Month (PMT, NPER and Goal Seek) Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Trip Budget in Excel: Plan vs Actual, Expense Log and Split Costs Among Friends (Who Owes Whom) Excel Guides & Daily-Life Articles · 12 tasks

  1. Total times Easy Add up Times in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Food Easy How many rows have Category "Food"? (sheet "Data")
  4. Largest times Easy The largest value in D2:D11. (sheet "Data")
  5. Clean name Easy Trim A2. (sheet "Text")
  6. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  7. Code prefix Easy First 3 characters of B2. (sheet "Text")
  8. Code number Easy Last 4 characters of B3. (sheet "Text")
  9. Times for Food Medium Total of D2:D11 where B is "Food". (sheet "Data")
  10. Food in Bengaluru Medium Total of D2:D11 where B is "Food" and C is "Bengaluru". (sheet "Data")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: SIP Calculator in Excel: Future Value of a Monthly SIP with the FV Function (and a Yearly Step-Up) Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: FD Calculator in Excel: Maturity Amount and Interest with Quarterly Compounding (Compare 3 Offers) Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Compare Personal Loan Offers in Excel: EMI, Total Interest, Processing Fee and the Cheapest Loan Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Fuel Mileage Tracker in Excel: km per Litre and Cost per km for Your Bike or Car Excel Guides & Daily-Life Articles · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Seattle Medium Total of D2:D11 where B is "Cloud" and C is "Seattle".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Rohan Iyer Medium The Billable hours for "Rohan Iyer" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Milk Bill Calculator in Excel: Daily Litres Calendar, Monthly Bill and Missed Days Excel Guides & Daily-Life Articles · 12 tasks

  1. Total clicks Easy Add up Clicks in D2:D11. (sheet "Data")
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Google Easy How many rows have Platform "Google"? (sheet "Data")
  4. Largest clicks Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Clicks for Google Medium Total of D2:D11 where B is "Google". (sheet "Data")
  10. Google in Paris Medium Total of D2:D11 where B is "Google" and C is "Paris". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Birthday and Anniversary Reminder in Excel: Age, Next Birthday and Days Left (DATEDIF) Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Clean name Easy Trim A2. (sheet "Text")
  5. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  6. Code prefix Easy First 3 characters of B2. (sheet "Text")
  7. Code number Easy Last 4 characters of B3. (sheet "Text")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: Home Inventory in Excel: Appliance List with Warranty Expiry Dates and Alerts (EDATE) Excel Guides & Daily-Life Articles · 12 tasks

  1. Total times Easy Add up Times in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Food Easy How many rows have Category "Food"? (sheet "Data")
  4. Largest times Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Times for Food Medium Total of D2:D11 where B is "Food". (sheet "Data")
  10. Food in Delhi Medium Total of D2:D11 where B is "Food" and C is "Delhi". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Exam Study Planner in Excel: Countdown to the Exam, Syllabus Progress and a Weekly Study Log Excel Guides & Daily-Life Articles · 13 tasks

  1. Total data gb Easy Add up Data GB in D2:D11.
  2. Average monthly bill Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Prepaid Easy How many rows have Plan "Prepaid"?
  4. Largest data gb Easy The largest value in D2:D11.
  5. Data GB for Prepaid Medium Total of D2:D11 where B is "Prepaid".
  6. Prepaid in Milan Medium Total of D2:D11 where B is "Prepaid" and C is "Milan".
  7. Best in Prepaid Medium Largest E2:E11 value where B is "Prepaid".
  8. Value of row 2 Medium D2 times E2.
  9. Look up James Carter Medium The Data GB for "James Carter" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Prepaid Hard Share of D2:D11 that is "Prepaid", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Rent Collection Tracker in Excel: Monthly Rent Received, Pending Dues and Late Tenants Excel Guides & Daily-Life Articles · 12 tasks

  1. Total area sq ft Easy Add up Area sq ft in D2:D11. (sheet "Data")
  2. Average price per sq ft Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Tower 1 Easy How many rows have Tower "Tower 1"? (sheet "Data")
  4. Largest area sq ft Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Area sq ft for Tower 1 Medium Total of D2:D11 where B is "Tower 1". (sheet "Data")
  10. Tower 1 in Bengaluru Medium Total of D2:D11 where B is "Tower 1" and C is "Bengaluru". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Guest List and Budget in Excel for a Function: RSVP Count, Plates to Order and Total Cost Excel Guides & Daily-Life Articles · 13 tasks

  1. Total times Easy Add up Times in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Food Easy How many rows have Category "Food"?
  4. Largest times Easy The largest value in D2:D11.
  5. Times for Food Medium Total of D2:D11 where B is "Food".
  6. Food in Delhi Medium Total of D2:D11 where B is "Food" and C is "Delhi".
  7. Best in Food Medium Largest E2:E11 value where B is "Food".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Kavya Reddy Medium The Times for "Kavya Reddy" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Food Hard Share of D2:D11 that is "Food", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Medicine Refill Reminder in Excel: Tablets Left, Days of Stock and Refill-by Dates Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Clean name Easy Trim A2. (sheet "Text")
  5. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  6. Code prefix Easy First 3 characters of B2. (sheet "Text")
  7. Code number Easy Last 4 characters of B3. (sheet "Text")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  12. Name length Medium Length of A3. (sheet "Text")

Practice: Pocket Money Tracker in Excel for Kids: Running Balance, Spending and a Savings Goal Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Car and Bike Service Log in Excel: Next Service Due by km or Date, and Maintenance Cost Excel Guides & Daily-Life Articles · 12 tasks

  1. Total nights Easy Add up Nights in D2:D11. (sheet "Data")
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Standard Easy How many rows have Room type "Standard"? (sheet "Data")
  4. Largest nights Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Nights for Standard Medium Total of D2:D11 where B is "Standard". (sheet "Data")
  10. Standard in Leeds Medium Total of D2:D11 where B is "Standard" and C is "Leeds". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Gas Cylinder Tracker in Excel: How Many Days Each LPG Cylinder Lasts and When to Book the Next Excel Guides & Daily-Life Articles · 12 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11. (sheet "Data")
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Cloud Easy How many rows have Practice "Cloud"? (sheet "Data")
  4. Largest billable hours Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud". (sheet "Data")
  10. Cloud in Chicago Medium Total of D2:D11 where B is "Cloud" and C is "Chicago". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Sleep Tracker in Excel: Calculate Hours Slept (Even Past Midnight) and Weekly Averages Excel Guides & Daily-Life Articles · 13 tasks

  1. Total clicks Easy Add up Clicks in D2:D11.
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google Easy How many rows have Platform "Google"?
  4. Largest clicks Easy The largest value in D2:D11.
  5. Clicks for Google Medium Total of D2:D11 where B is "Google".
  6. Google in Paris Medium Total of D2:D11 where B is "Google" and C is "Paris".
  7. Best in Google Medium Largest E2:E11 value where B is "Google".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ishaan Verma Medium The Clicks for "Ishaan Verma" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Google Hard Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Household Chores Roster in Excel: Rotate Chores Every Week Automatically (INDEX and MOD) Excel Guides & Daily-Life Articles · 11 tasks

  1. VLOOKUP name Easy Name for ID "S103" with VLOOKUP.
  2. XLOOKUP value Easy The Cost for ID "S103".
  3. Position Easy Position of "Mateo Garcia" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Mateo Garcia" using INDEX and MATCH.
  5. Not found message Medium Parcels for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "S103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Mateo Garcia" and column "Parcels".
  11. Lookup the top Hard Name with the largest Parcels.

Practice: Recipe Cost Calculator in Excel: Scale a Recipe for More Guests and Find the Cost per Plate Excel Guides & Daily-Life Articles · 13 tasks

  1. Total seats Easy Add up Seats in D2:D11.
  2. Average price per seat Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Basic Easy How many rows have Plan "Basic"?
  4. Largest seats Easy The largest value in D2:D11.
  5. Seats for Basic Medium Total of D2:D11 where B is "Basic".
  6. Basic in Seattle Medium Total of D2:D11 where B is "Basic" and C is "Seattle".
  7. Best in Basic Medium Largest E2:E11 value where B is "Basic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Seats for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Basic Hard Share of D2:D11 that is "Basic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Credit Card Bill Tracker in Excel: Due Dates, Pending Amount and Credit Utilisation Excel Guides & Daily-Life Articles · 12 tasks

  1. Total stock units Easy Add up Stock units in D2:D11. (sheet "Data")
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"? (sheet "Data")
  4. Largest stock units Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic". (sheet "Data")
  10. Antibiotic in Madrid Medium Total of D2:D11 where B is "Antibiotic" and C is "Madrid". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Insurance Policy Renewal Tracker in Excel: Next Premium Due Date with EDATE and Yearly Cost Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Walking Steps Tracker in Excel: Daily Goal, Weekly Average with AVERAGEIFS and Km Walked Excel Guides & Daily-Life Articles · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Denver Medium Total of D2:D11 where B is "Downtown" and C is "Denver".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Covers for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Customer Credit (Khata) Book in Excel: Udhaar Balance per Customer with SUMIFS Excel Guides & Daily-Life Articles · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Delhi Medium Total of D2:D11 where B is "Snacks" and C is "Delhi".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Units sold for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Unit Price Comparison in Excel: Which Pack Size Is Cheaper per Kg or Litre? Excel Guides & Daily-Life Articles · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Chicago Medium Total of D2:D11 where B is "Website" and C is "Chicago".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Quantity for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Blood Pressure Log in Excel: Morning and Evening Averages and High Readings Flagged Excel Guides & Daily-Life Articles · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: Reading Log in Excel: Pages per Day, % Done and Expected Book Finish Date Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Paris Medium Total of D2:D11 where B is "Admin" and C is "Paris". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Flatmate Expense Split in Excel: Share Rent by Room Size and Bills by Days Stayed Excel Guides & Daily-Life Articles · 12 tasks

  1. Clean name Easy Trim A2. (sheet "Text")
  2. Proper case Easy A2 trimmed and in proper case. (sheet "Text")
  3. Code prefix Easy First 3 characters of B2. (sheet "Text")
  4. Code number Easy Last 4 characters of B3. (sheet "Text")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. City part Medium Characters 5 to 7 of B4. (sheet "Text")
  10. Name length Medium Length of A3. (sheet "Text")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Freelance Timesheet in Excel: Hours Worked, Overtime and Invoice Total per Project Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Berlin Medium Total of D2:D11 where B is "Admin" and C is "Berlin". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: CAGR Calculator in Excel: Compare Investment Returns with a Formula and the RRI Function Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Emergency Fund Calculator in Excel: How Many Months of Expenses Are You Covered For? Excel Guides & Daily-Life Articles · 13 tasks

  1. Total times Easy Add up Times in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Food Easy How many rows have Category "Food"?
  4. Largest times Easy The largest value in D2:D11.
  5. Times for Food Medium Total of D2:D11 where B is "Food".
  6. Food in Mumbai Medium Total of D2:D11 where B is "Food" and C is "Mumbai".
  7. Best in Food Medium Largest E2:E11 value where B is "Food".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Times for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Food Hard Share of D2:D11 that is "Food", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Shopping Offer Comparison in Excel: Buy 2 Get 1 Free vs % Off vs Flat ₹ Off Excel Guides & Daily-Life Articles · 13 tasks

  1. Total seats Easy Add up Seats in D2:D11.
  2. Average price per seat Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Basic Easy How many rows have Plan "Basic"?
  4. Largest seats Easy The largest value in D2:D11.
  5. Seats for Basic Medium Total of D2:D11 where B is "Basic".
  6. Basic in Denver Medium Total of D2:D11 where B is "Basic" and C is "Denver".
  7. Best in Basic Medium Largest E2:E11 value where B is "Basic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Seats for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Basic Hard Share of D2:D11 that is "Basic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Gym Workout Log in Excel: Sets, Reps, Volume and Personal Bests with MAXIFS Excel Guides & Daily-Life Articles · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Berlin Medium Total of D2:D11 where B is "Antibiotic" and C is "Berlin".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Stock units for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Gift Register for a Function in Excel: Cash Gifts, Items and What We Gave Them Earlier Excel Guides & Daily-Life Articles · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Austin Medium Total of D2:D11 where B is "Downtown" and C is "Austin".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Aarav Shah Medium The Covers for "Aarav Shah" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Money Lent Tracker in Excel: Simple Interest by Days, Total Due and Overdue Loans Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Vegetable Stall Profit Tracker in Excel: Daily Profit, Margin, Waste % and Break-even Price Excel Guides & Daily-Life Articles · 13 tasks

  1. Total quantity Easy Add up Quantity in D2:D11.
  2. Average unit price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Website Easy How many rows have Channel "Website"?
  4. Largest quantity Easy The largest value in D2:D11.
  5. Quantity for Website Medium Total of D2:D11 where B is "Website".
  6. Website in Boston Medium Total of D2:D11 where B is "Website" and C is "Boston".
  7. Best in Website Medium Largest E2:E11 value where B is "Website".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sophie Martin Medium The Quantity for "Sophie Martin" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Website Hard Share of D2:D11 that is "Website", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Cricket Batting Stats in Excel: Batting Average, Strike Rate and Player Rank Excel Guides & Daily-Life Articles · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Manchester Medium Total of D2:D11 where B is "North" and C is "Manchester".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Noah Wilson Medium The Deals won for "Noah Wilson" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Appliance Electricity Cost Calculator in Excel: Watts to Units (kWh) and Monthly Cost Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Berlin Medium Total of D2:D11 where B is "Admin" and C is "Berlin". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Own Car vs Cab Cost in Excel: Monthly Cost per Km and Break-even Distance Excel Guides & Daily-Life Articles · 12 tasks

  1. Total nights Easy Add up Nights in D2:D11. (sheet "Data")
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Standard Easy How many rows have Room type "Standard"? (sheet "Data")
  4. Largest nights Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Nights for Standard Medium Total of D2:D11 where B is "Standard". (sheet "Data")
  10. Standard in Manchester Medium Total of D2:D11 where B is "Standard" and C is "Manchester". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Employee Attendance Management System in Excel: Monthly Codes, Leave Balance, Late Marks and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total leave days Easy Add up Leave days in D2:D11. (sheet "Data")
  2. Average salary Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Sales Easy How many rows have Department "Sales"? (sheet "Data")
  4. Largest leave days Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Leave days for Sales Medium Total of D2:D11 where B is "Sales". (sheet "Data")
  10. Sales in Austin Medium Total of D2:D11 where B is "Sales" and C is "Austin". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Payroll Calculator in Excel with PF, ESI and Professional Tax: Employer Cost, Statutory Dues and Bank Transfer List Excel Guides & Daily-Life Articles · 12 tasks

  1. Total tenure months Easy Add up Tenure months in D2:D11. (sheet "Data")
  2. Average loan amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Home Easy How many rows have Loan type "Home"? (sheet "Data")
  4. Largest tenure months Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Tenure months for Home Medium Total of D2:D11 where B is "Home". (sheet "Data")
  10. Home in Pune Medium Total of D2:D11 where B is "Home" and C is "Pune". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Vendor Management System in Excel: Purchase Orders, Bills, Payments, Payable Ageing and Vendor Scorecard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total stock units Easy Add up Stock units in D2:D11. (sheet "Data")
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"? (sheet "Data")
  4. Largest stock units Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic". (sheet "Data")
  10. Antibiotic in Paris Medium Total of D2:D11 where B is "Antibiotic" and C is "Paris". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Hotel Management System in Excel: Room Bookings, Daily Occupancy, ADR, RevPAR, GST Room Bill and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total nights Easy Add up Nights in D2:D11.
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard Easy How many rows have Room type "Standard"?
  4. Largest nights Easy The largest value in D2:D11.
  5. Nights for Standard Medium Total of D2:D11 where B is "Standard".
  6. Standard in Leeds Medium Total of D2:D11 where B is "Standard" and C is "Leeds".
  7. Best in Standard Medium Largest E2:E11 value where B is "Standard".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Nights for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Standard Hard Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Salon Management System in Excel: Appointments, Client History, Repeat Clients, Stylist Sales and Commission Excel Guides & Daily-Life Articles · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Glasgow Medium Total of D2:D11 where B is "North" and C is "Glasgow".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Deals won for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Shop Billing and Inventory System in Excel: Items, Purchases, Sales, Stock Reorder, Profit and Daily Cash Book Excel Guides & Daily-Life Articles · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Bristol Medium Total of D2:D11 where B is "North" and C is "Bristol".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Deals won for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Clinic Patient Management System in Excel: Patient Register, Visits, Fees, Follow-up Reminders and Receipts Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Gym Membership Management System in Excel: Plans, Renewals, Check-ins, Receipts and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Milan Medium Total of D2:D11 where B is "Admin" and C is "Milan". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: School Fee Management System in Excel: Fee Structure, Instalments, Overdue List, Receipts and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total marks Easy Add up Marks in D2:D11. (sheet "Data")
  2. Average fees paid Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in A Easy How many rows have Section "A"? (sheet "Data")
  4. Largest marks Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Marks for A Medium Total of D2:D11 where B is "A". (sheet "Data")
  10. A in Chennai Medium Total of D2:D11 where B is "A" and C is "Chennai". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Tuition Centre Management System in Excel: Groups, Monthly Fees, Test Marks, Progress Card and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11. (sheet "Data")
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Cloud Easy How many rows have Practice "Cloud"? (sheet "Data")
  4. Largest billable hours Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud". (sheet "Data")
  10. Cloud in Boston Medium Total of D2:D11 where B is "Cloud" and C is "Boston". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Inventory and Warehouse Management System in Excel: Stock In, Stock Out, Days of Cover, Reorder, ABC and Challan Excel Guides & Daily-Life Articles · 12 tasks

  1. Total clicks Easy Add up Clicks in D2:D11. (sheet "Data")
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Google Easy How many rows have Platform "Google"? (sheet "Data")
  4. Largest clicks Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Clicks for Google Medium Total of D2:D11 where B is "Google". (sheet "Data")
  10. Google in Milan Medium Total of D2:D11 where B is "Google" and C is "Milan". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Rental Property Management System in Excel: Units, Rent Roll, Arrears, Lease Expiry, Receipts and Net Yield Excel Guides & Daily-Life Articles · 13 tasks

  1. Total area sq ft Easy Add up Area sq ft in D2:D11.
  2. Average price per sq ft Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Tower 1 Easy How many rows have Tower "Tower 1"?
  4. Largest area sq ft Easy The largest value in D2:D11.
  5. Area sq ft for Tower 1 Medium Total of D2:D11 where B is "Tower 1".
  6. Tower 1 in Mumbai Medium Total of D2:D11 where B is "Tower 1" and C is "Mumbai".
  7. Best in Tower 1 Medium Largest E2:E11 value where B is "Tower 1".
  8. Value of row 2 Medium D2 times E2.
  9. Look up James Carter Medium The Area sq ft for "James Carter" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Tower 1 Hard Share of D2:D11 that is "Tower 1", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Event Management System in Excel: Budget vs Actual, Vendor Expenses, Task List, Quotation and Profit Excel Guides & Daily-Life Articles · 13 tasks

  1. Total times Easy Add up Times in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Food Easy How many rows have Category "Food"?
  4. Largest times Easy The largest value in D2:D11.
  5. Times for Food Medium Total of D2:D11 where B is "Food".
  6. Food in Bengaluru Medium Total of D2:D11 where B is "Food" and C is "Bengaluru".
  7. Best in Food Medium Largest E2:E11 value where B is "Food".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Times for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Food Hard Share of D2:D11 that is "Food", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Transport and Fleet Management System in Excel: Trips, Km per Litre, Cost per Km, Document Expiry and Profit Excel Guides & Daily-Life Articles · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Paris Medium Total of D2:D11 where B is "Antibiotic" and C is "Paris".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Stock units for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Construction Site Management System in Excel: BOQ, Progress, Materials, Labour, Budget vs Actual and RA Bill Excel Guides & Daily-Life Articles · 13 tasks

  1. Total times Easy Add up Times in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Food Easy How many rows have Category "Food"?
  4. Largest times Easy The largest value in D2:D11.
  5. Times for Food Medium Total of D2:D11 where B is "Food".
  6. Food in Bengaluru Medium Total of D2:D11 where B is "Food" and C is "Bengaluru".
  7. Best in Food Medium Largest E2:E11 value where B is "Food".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Times for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Food Hard Share of D2:D11 that is "Food", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: CA Office Compliance Management System in Excel: GST and TDS Due Dates, Filing Status, Fees and Staff Report Excel Guides & Daily-Life Articles · 12 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail". (sheet "Scores")
  2. Total Easy B2 plus C2. (sheet "Scores")
  3. Passed score 1 Easy How many in B2:B11 are 40 or more? (sheet "Scores")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Passed both Medium TRUE if B3 and C3 are both at least 40. (sheet "Scores")
  9. Passed either Medium TRUE if B4 or C4 is at least 75. (sheet "Scores")
  10. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F". (sheet "Scores")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Beauty Parlour Management System in Excel: Packages, Sessions, Product Stock, Staff Incentive and Bills Excel Guides & Daily-Life Articles · 13 tasks

  1. Total units sold Easy Add up Units sold in D2:D11.
  2. Average price Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Snacks Easy How many rows have Category "Snacks"?
  4. Largest units sold Easy The largest value in D2:D11.
  5. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks".
  6. Snacks in Bengaluru Medium Total of D2:D11 where B is "Snacks" and C is "Bengaluru".
  7. Best in Snacks Medium Largest E2:E11 value where B is "Snacks".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Units sold for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Snacks Hard Share of D2:D11 that is "Snacks", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Bakery Management System in Excel: Recipe Costing, Daily Production, Sales, Waste and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total units made Easy Add up Units made in D2:D11.
  2. Average rejects Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Line A Easy How many rows have Line "Line A"?
  4. Largest units made Easy The largest value in D2:D11.
  5. Units made for Line A Medium Total of D2:D11 where B is "Line A".
  6. Line A in Delhi Medium Total of D2:D11 where B is "Line A" and C is "Delhi".
  7. Best in Line A Medium Largest E2:E11 value where B is "Line A".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Units made for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Line A Hard Share of D2:D11 that is "Line A", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Pharmacy Management System in Excel: Items, Sales Bills, Stock, Expiry Alerts and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total stock units Easy Add up Stock units in D2:D11.
  2. Average unit cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Antibiotic Easy How many rows have Category "Antibiotic"?
  4. Largest stock units Easy The largest value in D2:D11.
  5. Stock units for Antibiotic Medium Total of D2:D11 where B is "Antibiotic".
  6. Antibiotic in Madrid Medium Total of D2:D11 where B is "Antibiotic" and C is "Madrid".
  7. Best in Antibiotic Medium Largest E2:E11 value where B is "Antibiotic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Hannah Lee Medium The Stock units for "Hannah Lee" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Antibiotic Hard Share of D2:D11 that is "Antibiotic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Cloud Kitchen Management System in Excel: Menu Costing, Orders, App Commission, Net Margin and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total nights Easy Add up Nights in D2:D11.
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard Easy How many rows have Room type "Standard"?
  4. Largest nights Easy The largest value in D2:D11.
  5. Nights for Standard Medium Total of D2:D11 where B is "Standard".
  6. Standard in London Medium Total of D2:D11 where B is "Standard" and C is "London".
  7. Best in Standard Medium Largest E2:E11 value where B is "Standard".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Nights for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Standard Hard Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Boutique and Tailoring Management System in Excel: Measurements, Orders, Delivery Dates, Job Cards and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total parcels Easy Add up Parcels in D2:D11. (sheet "Data")
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"? (sheet "Data")
  4. Largest parcels Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail". (sheet "Data")
  10. Royal Mail in Bristol Medium Total of D2:D11 where B is "Royal Mail" and C is "Bristol". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Hardware Store Management System in Excel: GST Invoices, Contractor Credit, Ageing, Stock and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Paris Medium Total of D2:D11 where B is "Admin" and C is "Paris". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Mobile Repair Job Card System in Excel: Jobs, Parts Stock, Status Flags, Warranty and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail". (sheet "Scores")
  2. Total Easy B2 plus C2. (sheet "Scores")
  3. Passed score 1 Easy How many in B2:B11 are 40 or more? (sheet "Scores")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Passed both Medium TRUE if B3 and C3 are both at least 40. (sheet "Scores")
  9. Passed either Medium TRUE if B4 or C4 is at least 75. (sheet "Scores")
  10. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F". (sheet "Scores")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Car Service Garage Management System in Excel: Job Cards, Parts and Labour, GST Invoice, Service Reminders and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Laundry Management System in Excel: Price List, Order Lines, Express Surcharge, Due Dates, Receipts and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. VLOOKUP name Easy Name for ID "M103" with VLOOKUP. (sheet "Lookup")
  2. XLOOKUP value Easy The Unit cost for ID "M103". (sheet "Lookup")
  3. Position Easy Position of "James Carter" in B2:B9. (sheet "Lookup")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. INDEX and MATCH Medium ID for name "James Carter" using INDEX and MATCH. (sheet "Lookup")
  9. Not found message Medium Stock units for ID "Z999", or "Not found". (sheet "Lookup")
  10. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing". (sheet "Lookup")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Hostel and PG Management System in Excel: Rooms, Residents, Rent, Late Fee, Occupancy and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total nights Easy Add up Nights in D2:D11.
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard Easy How many rows have Room type "Standard"?
  4. Largest nights Easy The largest value in D2:D11.
  5. Nights for Standard Medium Total of D2:D11 where B is "Standard".
  6. Standard in Leeds Medium Total of D2:D11 where B is "Standard" and C is "Leeds".
  7. Best in Standard Medium Largest E2:E11 value where B is "Standard".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Nights for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Standard Hard Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Co-working Space Management System in Excel: Plans, Members, Meeting Room Bookings, Monthly Billing and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total units sold Easy Add up Units sold in D2:D11. (sheet "Data")
  2. Average price Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Snacks Easy How many rows have Category "Snacks"? (sheet "Data")
  4. Largest units sold Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Units sold for Snacks Medium Total of D2:D11 where B is "Snacks". (sheet "Data")
  10. Snacks in Bengaluru Medium Total of D2:D11 where B is "Snacks" and C is "Bengaluru". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Housing Society Maintenance System in Excel: Quarterly Bills, Sinking Fund, Interest on Arrears, Receipts, Expenses and Dashboard Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Travel Agency Management System in Excel: Tour Packages, Bookings, Payments, Balance Due and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total deals won Easy Add up Deals won in D2:D11. (sheet "Data")
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in North Easy How many rows have Region "North"? (sheet "Data")
  4. Largest deals won Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Deals won for North Medium Total of D2:D11 where B is "North". (sheet "Data")
  10. North in Manchester Medium Total of D2:D11 where B is "North" and C is "Manchester". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Catering Management System in Excel: Packages, Events, Payments, Kitchen Quantities, Margin and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Denver Medium Total of D2:D11 where B is "Downtown" and C is "Denver".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Vikram Singh Medium The Covers for "Vikram Singh" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Photography Studio Management System in Excel: Packages, Bookings, Date Clash Check, Delivery Tracking and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Total parcels Easy Add up Parcels in D2:D11. (sheet "Data")
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"? (sheet "Data")
  4. Largest parcels Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail". (sheet "Data")
  10. Royal Mail in Manchester Medium Total of D2:D11 where B is "Royal Mail" and C is "Manchester". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Interior Design Costing System in Excel: Rate Card, Room Estimate, Budget vs Actual, Payment Schedule and Quotation Excel Guides & Daily-Life Articles · 11 tasks

  1. VLOOKUP name Easy Name for ID "E103" with VLOOKUP.
  2. XLOOKUP value Easy The Amount for ID "E103".
  3. Position Easy Position of "Aarav Shah" in B2:B9.
  4. INDEX and MATCH Medium ID for name "Aarav Shah" using INDEX and MATCH.
  5. Not found message Medium Times for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP Medium VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH Medium Position of ID "E103" in A2:A9.
  8. Slab rate Hard Rate for E2 from the slab table G2:H6.
  9. Slab amount Hard E3 times its slab rate.
  10. Two-way lookup Hard Value in A2:E9 at the row of "Aarav Shah" and column "Times".
  11. Lookup the top Hard Name with the largest Times.

Practice: Manufacturing Production Planning System in Excel: Machine Hours, Planned Finish Date, Progress, Rejects and Machine Load Excel Guides & Daily-Life Articles · 12 tasks

  1. Total units made Easy Add up Units made in D2:D11. (sheet "Data")
  2. Average rejects Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Line A Easy How many rows have Line "Line A"? (sheet "Data")
  4. Largest units made Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Units made for Line A Medium Total of D2:D11 where B is "Line A". (sheet "Data")
  10. Line A in Mumbai Medium Total of D2:D11 where B is "Line A" and C is "Mumbai". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Farm Management System in Excel: Plot-wise Expenses, Crop Sales, Profit per Acre, Cost per Quintal and Dashboard Excel Guides & Daily-Life Articles · 13 tasks

  1. Total times Easy Add up Times in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Food Easy How many rows have Category "Food"?
  4. Largest times Easy The largest value in D2:D11.
  5. Times for Food Medium Total of D2:D11 where B is "Food".
  6. Food in Pune Medium Total of D2:D11 where B is "Food" and C is "Pune".
  7. Best in Food Medium Largest E2:E11 value where B is "Food".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Arjun Mehta Medium The Times for "Arjun Mehta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Food Hard Share of D2:D11 that is "Food", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Poultry Farm Management System in Excel: Flocks, Daily Deaths and Feed, Bird Sales, Mortality %, FCR and Profit Excel Guides & Daily-Life Articles · 13 tasks

  1. Total quintals Easy Add up Quintals in D2:D11.
  2. Average rate per quintal Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Wheat Easy How many rows have Crop "Wheat"?
  4. Largest quintals Easy The largest value in D2:D11.
  5. Quintals for Wheat Medium Total of D2:D11 where B is "Wheat".
  6. Wheat in Pune Medium Total of D2:D11 where B is "Wheat" and C is "Pune".
  7. Best in Wheat Medium Largest E2:E11 value where B is "Wheat".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Quintals for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Wheat Hard Share of D2:D11 that is "Wheat", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Petrol Pump Shift Management System in Excel: Meter Readings, Litres Sold, Cash and UPI Collection, Shortage and Tank Dip Excel Guides & Daily-Life Articles · 13 tasks

  1. Total data gb Easy Add up Data GB in D2:D11.
  2. Average monthly bill Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Prepaid Easy How many rows have Plan "Prepaid"?
  4. Largest data gb Easy The largest value in D2:D11.
  5. Data GB for Prepaid Medium Total of D2:D11 where B is "Prepaid".
  6. Prepaid in Dublin Medium Total of D2:D11 where B is "Prepaid" and C is "Dublin".
  7. Best in Prepaid Medium Largest E2:E11 value where B is "Prepaid".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Data GB for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Prepaid Hard Share of D2:D11 that is "Prepaid", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Library Management System in Excel: Books, Members, Issue and Return, Due Dates, Late Fines and Availability Excel Guides & Daily-Life Articles · 12 tasks

  1. Total covers Easy Add up Covers in D2:D11. (sheet "Data")
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"? (sheet "Data")
  4. Largest covers Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Covers for Downtown Medium Total of D2:D11 where B is "Downtown". (sheet "Data")
  10. Downtown in Boston Medium Total of D2:D11 where B is "Downtown" and C is "Boston". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Day-care Management System in Excel: Children, Daily Attendance, Late Pickup Charges, Monthly Fees and Fee Bill Excel Guides & Daily-Life Articles · 12 tasks

  1. Total leave days Easy Add up Leave days in D2:D11. (sheet "Data")
  2. Average salary Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Sales Easy How many rows have Department "Sales"? (sheet "Data")
  4. Largest leave days Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Leave days for Sales Medium Total of D2:D11 where B is "Sales". (sheet "Data")
  10. Sales in Chicago Medium Total of D2:D11 where B is "Sales" and C is "Chicago". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: NGO Donor and Fund Management System in Excel: Donors, Donations, 80G Checks, Fund-wise Spending, Balance and Receipt Excel Guides & Daily-Life Articles · 13 tasks

  1. Total donations Easy Add up Donations in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Education Easy How many rows have Fund "Education"?
  4. Largest donations Easy The largest value in D2:D11.
  5. Donations for Education Medium Total of D2:D11 where B is "Education".
  6. Education in Pune Medium Total of D2:D11 where B is "Education" and C is "Pune".
  7. Best in Education Medium Largest E2:E11 value where B is "Education".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Donations for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Education Hard Share of D2:D11 that is "Education", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Sales CRM System in Excel: Leads, Stages, Weighted Pipeline, Follow-up Reminders, Win Rate by Source and Dashboard Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Complaint Ticket Management System in Excel: Priorities, Due-by Time, Hours Taken, Met or Breached and Agent Report Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Madrid Medium Total of D2:D11 where B is "Admin" and C is "Madrid". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: IT Asset Management System in Excel: Asset Register, Assigned To, Warranty Alerts, Book Value, Repairs and Report Excel Guides & Daily-Life Articles · 12 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12. (sheet "Plan")
  2. Months Easy B4 times 12. (sheet "Plan")
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals. (sheet "Plan")
  4. Days taken Easy C2 minus B2. (sheet "Dates")
  5. Start year Easy Year of B3. (sheet "Dates")
  6. Due month Easy Month number of C3. (sheet "Dates")
  7. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  8. Total repaid Medium Total paid over the loan, rounded to 0 decimals. (sheet "Plan")
  9. Total interest Medium Total paid minus B2, rounded to 0 decimals. (sheet "Plan")
  10. Processing fee Medium B2 times B6. (sheet "Plan")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Expense Reimbursement System in Excel: Claims, Km Rates, Category Limits, Bill Check, Approval and Payment Status Excel Guides & Daily-Life Articles · 11 tasks

  1. Pass or fail Easy "Pass" if B2 is at least 40, else "Fail".
  2. Total Easy B2 plus C2.
  3. Passed score 1 Easy How many in B2:B11 are 40 or more?
  4. Passed both Medium TRUE if B3 and C3 are both at least 40.
  5. Passed either Medium TRUE if B4 or C4 is at least 75.
  6. Grade Medium Grade for the average of B2:C2: "A" 75+, "B" 60+, "C" 40+, else "F".
  7. Eligible Medium "Eligible" if D5 is at least 75 and B5 at least 40, else "Not eligible".
  8. Count passed both Hard How many have both scores at least 40?
  9. Pass percentage Hard Share passing score 1, rounded to 2 decimals.
  10. Nested IF band Hard "High" if C6 ≥ 75, "Mid" if ≥ 50, else "Low", using nested IF.
  11. Top scorer Hard Name with the highest score 1.

Practice: Small Business Accounts Receivable System in Excel: Invoices, Part Payments, Due Dates, Ageing Buckets, Credit Limits and Statement Excel Guides & Daily-Life Articles · 12 tasks

  1. Total invoices Easy Add up Invoices in D2:D11. (sheet "Data")
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals. (sheet "Data")
  3. Rows in Admin Easy How many rows have Department "Admin"? (sheet "Data")
  4. Largest invoices Easy The largest value in D2:D11. (sheet "Data")
  5. Days taken Easy C2 minus B2. (sheet "Dates")
  6. Start year Easy Year of B3. (sheet "Dates")
  7. Due month Easy Month number of C3. (sheet "Dates")
  8. Earliest start Easy Earliest date in B2:B9 (as a date number). (sheet "Dates")
  9. Invoices for Admin Medium Total of D2:D11 where B is "Admin". (sheet "Data")
  10. Admin in Berlin Medium Total of D2:D11 where B is "Admin" and C is "Berlin". (sheet "Data")
  11. Working days Medium Working days from B2 to C2. (sheet "Dates")
  12. Weekday of due Medium Weekday number of C4 (Sunday = 1). (sheet "Dates")

Practice: Excel for Banking: Every File, Formula and Report Banks Use (With Templates) Excel Guides & Daily-Life Articles · 13 tasks

  1. Total tenure months Easy Add up Tenure months in D2:D11.
  2. Average loan amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Home Easy How many rows have Loan type "Home"?
  4. Largest tenure months Easy The largest value in D2:D11.
  5. Tenure months for Home Medium Total of D2:D11 where B is "Home".
  6. Home in Mumbai Medium Total of D2:D11 where B is "Home" and C is "Mumbai".
  7. Best in Home Medium Largest E2:E11 value where B is "Home".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Ananya Nair Medium The Tenure months for "Ananya Nair" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Home Hard Share of D2:D11 that is "Home", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Excel for Insurance: Every File, Formula and Report Insurers Use (With Templates) Excel Guides & Daily-Life Articles · 13 tasks

  1. Total claims Easy Add up Claims in D2:D11.
  2. Average premium Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Health Easy How many rows have Product "Health"?
  4. Largest claims Easy The largest value in D2:D11.
  5. Claims for Health Medium Total of D2:D11 where B is "Health".
  6. Health in Chicago Medium Total of D2:D11 where B is "Health" and C is "Chicago".
  7. Best in Health Medium Largest E2:E11 value where B is "Health".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Claims for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Health Hard Share of D2:D11 that is "Health", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Excel for Mechanical Industries: BOM, Job Cards, Maintenance and Plant Files (With Templates) Excel Guides & Daily-Life Articles · 13 tasks

  1. Total covers Easy Add up Covers in D2:D11.
  2. Average bill amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Downtown Easy How many rows have Outlet "Downtown"?
  4. Largest covers Easy The largest value in D2:D11.
  5. Covers for Downtown Medium Total of D2:D11 where B is "Downtown".
  6. Downtown in Chicago Medium Total of D2:D11 where B is "Downtown" and C is "Chicago".
  7. Best in Downtown Medium Largest E2:E11 value where B is "Downtown".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sophie Martin Medium The Covers for "Sophie Martin" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Downtown Hard Share of D2:D11 that is "Downtown", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Blinkit Dashboard in Excel: Quick Commerce KPIs, 10-Minute Delivery, AOV and Cancellations Excel Guides & Daily-Life Articles · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Glasgow Medium Total of D2:D11 where B is "Royal Mail" and C is "Glasgow".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Parcels for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Swiggy Dashboard in Excel: Food Delivery KPIs, Kitchen Prep Time, On-Time % and Rain Delays Excel Guides & Daily-Life Articles · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in Bristol Medium Total of D2:D11 where B is "Royal Mail" and C is "Bristol".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sophie Martin Medium The Parcels for "Sophie Martin" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Zomato Dashboard in Excel: Restaurant Partner KPIs, Take Rate, Dining-Out, Complaints and Refunds Excel Guides & Daily-Life Articles · 11 tasks

  1. Distinct groups Easy How many different values in B2:B11?
  2. List groups Easy Unique list of B2:B11.
  3. Filter names Easy Names in A2:A11 where B is "Downtown".
  4. Sum of filtered Medium Sum of D2:D11 where B is "Airport", using FILTER.
  5. Count filtered Medium Rows where D is above the average of D2:D11.
  6. Sorted values Medium D2:D11 sorted largest first.
  7. Top value via SORT Medium The largest E2:E11 value using SORT and INDEX.
  8. Second largest Hard Second largest D2:D11 using SORT and INDEX.
  9. Numbers 1 to 5 Hard A spill of numbers 1 to 5.
  10. Two-condition filter Hard Sum of D2:D11 where B is "Downtown" or "Airport".
  11. Names joined Hard Names where B is "Downtown", joined with ", ".

Practice: PhonePe Dashboard in Excel: UPI Payment KPIs, Success Rate, TPV, Failures and Latency Excel Guides & Daily-Life Articles · 13 tasks

  1. Total invoices Easy Add up Invoices in D2:D11.
  2. Average amount Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Admin Easy How many rows have Department "Admin"?
  4. Largest invoices Easy The largest value in D2:D11.
  5. Invoices for Admin Medium Total of D2:D11 where B is "Admin".
  6. Admin in Milan Medium Total of D2:D11 where B is "Admin" and C is "Milan".
  7. Best in Admin Medium Largest E2:E11 value where B is "Admin".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Neha Gupta Medium The Invoices for "Neha Gupta" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Admin Hard Share of D2:D11 that is "Admin", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Paytm Dashboard in Excel: Merchant Payments, Soundbox, Settlement Time and Loan KPIs Excel Guides & Daily-Life Articles · 11 tasks

  1. Monthly rate Easy Yearly rate B3 divided by 12.
  2. Months Easy B4 times 12.
  3. Monthly EMI Easy EMI on amount B2 at B3 for B4 years, as a positive number, rounded to 2 decimals.
  4. Total repaid Medium Total paid over the loan, rounded to 0 decimals.
  5. Total interest Medium Total paid minus B2, rounded to 0 decimals.
  6. Processing fee Medium B2 times B6.
  7. Savings value Medium Value after B4 years of saving B5 each month at B3, positive, rounded to 0 decimals.
  8. Interest earned Hard Savings value minus B5 times months, rounded to 0 decimals.
  9. Months to repay at double EMI Hard Months to clear B2 at B3 paying twice the EMI, rounded up.
  10. Loan you can afford Hard Loan size for an EMI equal to B5 over B4 years at B3, positive, rounded to 0 decimals.
  11. Interest share Hard Total interest ÷ total paid, rounded to 3 decimals.

Practice: Facebook Ads Dashboard in Excel: Spend, CPM, CTR, CPC, Cost per Lead and ROAS Excel Guides & Daily-Life Articles · 13 tasks

  1. Total clicks Easy Add up Clicks in D2:D11.
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google Easy How many rows have Platform "Google"?
  4. Largest clicks Easy The largest value in D2:D11.
  5. Clicks for Google Medium Total of D2:D11 where B is "Google".
  6. Google in Dublin Medium Total of D2:D11 where B is "Google" and C is "Dublin".
  7. Best in Google Medium Largest E2:E11 value where B is "Google".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Clicks for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Google Hard Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Instagram Creator Dashboard in Excel: Reach, Engagement Rate, Saves, Followers and Brand Deals Excel Guides & Daily-Life Articles · 13 tasks

  1. Total clicks Easy Add up Clicks in D2:D11.
  2. Average spend Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Google Easy How many rows have Platform "Google"?
  4. Largest clicks Easy The largest value in D2:D11.
  5. Clicks for Google Medium Total of D2:D11 where B is "Google".
  6. Google in Dublin Medium Total of D2:D11 where B is "Google" and C is "Dublin".
  7. Best in Google Medium Largest E2:E11 value where B is "Google".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Lucas Rossi Medium The Clicks for "Lucas Rossi" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Google Hard Share of D2:D11 that is "Google", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Netflix Dashboard in Excel: Subscribers, Churn Rate, ARPU and Watch Hours Excel Guides & Daily-Life Articles · 13 tasks

  1. Total seats Easy Add up Seats in D2:D11.
  2. Average price per seat Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Basic Easy How many rows have Plan "Basic"?
  4. Largest seats Easy The largest value in D2:D11.
  5. Seats for Basic Medium Total of D2:D11 where B is "Basic".
  6. Basic in Denver Medium Total of D2:D11 where B is "Basic" and C is "Denver".
  7. Best in Basic Medium Largest E2:E11 value where B is "Basic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Priya Kulkarni Medium The Seats for "Priya Kulkarni" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Basic Hard Share of D2:D11 that is "Basic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Snapchat Dashboard in Excel: Daily Engagement, Snap Open Rate, Streaks, Lenses and Ad Revenue Excel Guides & Daily-Life Articles · 13 tasks

  1. Total seats Easy Add up Seats in D2:D11.
  2. Average price per seat Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Basic Easy How many rows have Plan "Basic"?
  4. Largest seats Easy The largest value in D2:D11.
  5. Seats for Basic Medium Total of D2:D11 where B is "Basic".
  6. Basic in Austin Medium Total of D2:D11 where B is "Basic" and C is "Austin".
  7. Best in Basic Medium Largest E2:E11 value where B is "Basic".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Seats for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Basic Hard Share of D2:D11 that is "Basic", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: WhatsApp Business Dashboard in Excel: Read Rate, First Reply Time, Resolution and Chat Sales Excel Guides & Daily-Life Articles · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Manchester Medium Total of D2:D11 where B is "North" and C is "Manchester".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Kavya Reddy Medium The Deals won for "Kavya Reddy" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Reliance Dashboard in Excel: Segment Revenue, EBITDA Margin, Capex and Revenue Mix Excel Guides & Daily-Life Articles · 13 tasks

  1. Total data gb Easy Add up Data GB in D2:D11.
  2. Average monthly bill Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Prepaid Easy How many rows have Plan "Prepaid"?
  4. Largest data gb Easy The largest value in D2:D11.
  5. Data GB for Prepaid Medium Total of D2:D11 where B is "Prepaid".
  6. Prepaid in Berlin Medium Total of D2:D11 where B is "Prepaid" and C is "Berlin".
  7. Best in Prepaid Medium Largest E2:E11 value where B is "Prepaid".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Lucas Rossi Medium The Data GB for "Lucas Rossi" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Prepaid Hard Share of D2:D11 that is "Prepaid", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Infosys Dashboard in Excel: IT Services Revenue, Utilisation, Attrition and Operating Margin Excel Guides & Daily-Life Articles · 13 tasks

  1. Total billable hours Easy Add up Billable hours in D2:D11.
  2. Average rate per hour Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cloud Easy How many rows have Practice "Cloud"?
  4. Largest billable hours Easy The largest value in D2:D11.
  5. Billable hours for Cloud Medium Total of D2:D11 where B is "Cloud".
  6. Cloud in Seattle Medium Total of D2:D11 where B is "Cloud" and C is "Seattle".
  7. Best in Cloud Medium Largest E2:E11 value where B is "Cloud".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Chloe Dubois Medium The Billable hours for "Chloe Dubois" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Cloud Hard Share of D2:D11 that is "Cloud", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Tata Motors Dashboard in Excel: Car Sales, EV Share, Fuel Mix, Discount and Delivery Wait Excel Guides & Daily-Life Articles · 13 tasks

  1. Total parcels Easy Add up Parcels in D2:D11.
  2. Average cost Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Royal Mail Easy How many rows have Carrier "Royal Mail"?
  4. Largest parcels Easy The largest value in D2:D11.
  5. Parcels for Royal Mail Medium Total of D2:D11 where B is "Royal Mail".
  6. Royal Mail in London Medium Total of D2:D11 where B is "Royal Mail" and C is "London".
  7. Best in Royal Mail Medium Largest E2:E11 value where B is "Royal Mail".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Olivia Brown Medium The Parcels for "Olivia Brown" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Royal Mail Hard Share of D2:D11 that is "Royal Mail", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: Taj Hotels Dashboard in Excel: Occupancy, ADR, RevPAR, F&B and Guest KPIs Excel Guides & Daily-Life Articles · 13 tasks

  1. Total nights Easy Add up Nights in D2:D11.
  2. Average rate per night Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in Standard Easy How many rows have Room type "Standard"?
  4. Largest nights Easy The largest value in D2:D11.
  5. Nights for Standard Medium Total of D2:D11 where B is "Standard".
  6. Standard in London Medium Total of D2:D11 where B is "Standard" and C is "London".
  7. Best in Standard Medium Largest E2:E11 value where B is "Standard".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Sneha Patil Medium The Nights for "Sneha Patil" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of Standard Hard Share of D2:D11 that is "Standard", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".

Practice: IPL Cricket Dashboard in Excel: Ball-by-Ball Run Rate, Strike Rate, Powerplay and Death Overs Excel Guides & Daily-Life Articles · 13 tasks

  1. Total deals won Easy Add up Deals won in D2:D11.
  2. Average avg deal value Easy Average of E2:E11, rounded to 2 decimals.
  3. Rows in North Easy How many rows have Region "North"?
  4. Largest deals won Easy The largest value in D2:D11.
  5. Deals won for North Medium Total of D2:D11 where B is "North".
  6. North in Glasgow Medium Total of D2:D11 where B is "North" and C is "Glasgow".
  7. Best in North Medium Largest E2:E11 value where B is "North".
  8. Value of row 2 Medium D2 times E2.
  9. Look up Mateo Garcia Medium The Deals won for "Mateo Garcia" (use a lookup on column A).
  10. Total value Hard Sum of D times E for all rows.
  11. Share of North Hard Share of D2:D11 that is "North", rounded to 3 decimals.
  12. Rank of row 2 Hard Rank of D2 among D2:D11 (largest = 1).
  13. Above average? Hard "Above" if E2 is above the average of E2:E11, else "Below".