This quiz has 10 real-world formula challenges — SUMIF, AVERAGEIF, VLOOKUP, MAXIFS, and text extraction. Each question shows a small dataset and asks which formula gets the right result. Good practice if you’re prepping for an Excel-heavy job interview.
📝 Excel Online Test 4 – Start the Challenge!
A Few Answers Explained
Q1 (products sold under ₹500): The answer is =COUNTIF(D2:D6,"<500") — not SUMIF. The question asks how many products sold under ₹500, not the total value of those sales. SUMIF adds numbers that match a condition; COUNTIF just counts how many cells match.
Q3 (VLOOKUP with TRUE): =VLOOKUP(2500,A2:B6,2,TRUE) uses TRUE as the last argument, which tells VLOOKUP to find an approximate match instead of an exact one. If your lookup column isn’t sorted in ascending order, this can silently return the wrong row instead of an error — which is why most people should use FALSE unless they specifically need range-based lookups (like tax brackets or grading scales).
Q7 (extracting the email domain): =RIGHT(A2, LEN(A2) - FIND("@", A2)) works in two steps: FIND("@", A2) locates the position of the @ symbol, then RIGHT grabs everything after it. This FIND + RIGHT (or LEFT) combo is one of the most useful text-formula patterns in Excel — worth remembering beyond just this question.
Q10 (formula referencing deleted cells): =SUM(A2:D2) copied down will return 0 if you delete the values in A2:D6, not an error. Excel formulas keep working even when referenced cells go empty — they just calculate with nothing there. This trips people up because they expect an error like #REF!, which only happens if you delete the cells themselves (not just their contents).
Want More Practice?
Try Test 1 for Excel basics, or Test 19 for financial functions like PMT and NPV. For a full breakdown of how VLOOKUP works, see our VLOOKUP guide.
Very useful this task
Thanks again for your positive feedback!