Excel on the web - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate total sales with mixed currency conversion

You have sales amounts in USD in column A and a conversion rate to EUR in cell B1. Which formula in cell B2 correctly calculates the total sales in EUR for the range A2:A5?

A =SUM(A2:A5)*B1

B =SUM(A2:A5)/B1

C =SUM(A2:A5+B1)

D =SUM(A2:A5*B1)

💡 Hint

Remember to sum the amounts first, then multiply by the conversion rate.

Option A sums the sales amounts first, then multiplies by the conversion rate, giving the correct total in EUR. Option A adds the conversion rate to each amount before summing, which is incorrect. Option A divides the sum by the rate, which reverses the conversion. Option A attempts to multiply the range by the rate inside SUM, which is valid in Excel as it performs element-wise multiplication and then sums, but it is less clear and not the recommended approach.

❓ Function Choice

intermediate

Choose the function to count non-empty cells

You want to count how many cells in the range C2:C10 contain any data (numbers or text). Which function should you use?

A COUNTA(C2:C10)

B COUNT(C2:C10)

C COUNTBLANK(C2:C10)

D COUNTIF(C2:C10, "<>")

💡 Hint

Think about which function counts all non-empty cells regardless of content type.

COUNTA counts all non-empty cells including text and numbers. COUNT counts only numbers. COUNTBLANK counts empty cells. COUNTIF with "<>" counts non-empty cells but is less direct than COUNTA.

📊 Formula Result

advanced

Find the output of a nested IF formula

Given cell A1 contains the value 75, what is the result of this formula?

=IF(A1>90, "Excellent", IF(A1>60, "Good", "Needs Improvement"))

A "Excellent"

B "Good"

C "Needs Improvement"

D 0

💡 Hint

Check the conditions in order and see where 75 fits.

The formula checks if A1 is greater than 90 first, which is false. Then it checks if A1 is greater than 60, which is true, so it returns "Good".

🎯 Scenario

advanced

Identify the correct formula to extract the year from a date

You have a date in cell D3 (e.g., 2024-06-15). Which formula correctly extracts the year as a number?

A =MONTH(D3)

B =TEXT(D3, "YYYY")

C =DATEVALUE(D3)

D =YEAR(D3)

💡 Hint

Look for the function that returns the year part as a number.

YEAR returns the year number from a date. TEXT returns a text string, DATEVALUE converts a date string to a serial number, and MONTH returns the month number.

❓ data_analysis

expert

Determine the number of unique values in a range

You want to find how many unique values are in the range E2:E20. Which formula will give the correct count of unique values in Excel on the web?

A =COUNT(E2:E20)

B =COUNTIF(E2:E20, UNIQUE(E2:E20))

C =COUNTA(UNIQUE(E2:E20))

D =SUMPRODUCT(1/COUNTIF(E2:E20, E2:E20))

💡 Hint

Use the UNIQUE function combined with a counting function.

UNIQUE returns the distinct values. COUNTA counts how many items are in that list. COUNTIF and SUMPRODUCT formulas are more complex and may not work correctly in Excel on the web. COUNT counts only numbers, not unique values.