Skip to content

Module quiz: Formulas and functions :Preparing Data for Analysis with Microsoft Excel (Microsoft Power BI Data Analyst Professional Certificate) Answers 2025

Question 1

True or False:
Cell A2 = United, Cell B2 = States
Formula: =CONCAT(A2,B2)
Result displayed: United States

❌ True
✅ False

Explanation:
CONCAT(A2,B2) joins text without adding a space.
Actual result → UnitedStates (no space).

Question 2

Cell B2 contains: mOUNTAIN Bike
Formula: =LOWER(B2)

❌ MOUNTAIN BIKE
❌ Mountain Bike
✅ mountain bike

Explanation:
The LOWER function converts all characters to lowercase.

Question 3

Cell C2 contains:
32MainAvenueChicagoUSA
Formula: =LEFT(C2,12)

❌ 32Main
✅ 32MainAvenue
❌ 32MainAvenueChicago

Explanation:
LEFT(text,12) extracts the first 12 characters from the left.

Question 4

True or False:
You can display the serial number behind a date by changing the cell format to General.

✅ True
❌ False

Explanation:
Dates in Excel are stored as serial numbers.
Changing the format to General reveals that number.

Question 5

You typed 05/30/23 in cell A2 (MM/DD/YY).
What date will be displayed two days later when you reopen the worksheet?

❌ 05/31/23
✅ 05/30/23
❌ 06/01/23

Explanation:
A typed date is a static value, not dynamic.
It does not change automatically over time.

Question 6

Cell D2 = 05/30/23
Formula: =YEAR(D2)

❌ 5
✅ 2023
❌ 30

Explanation:
The YEAR function extracts only the year from a date.

Question 7

You need to calculate the number of weekdays between two dates.
Which function should you use?

❌ DATE
✅ NETWORKDAYS
❌ DATEDIF

Explanation:
NETWORKDAYS counts working days (Mon–Fri) between two dates.

Question 8

A2 = 100, B2 = 200
Formula: =AND(A2>=100,B2>=250)
Result is TRUE

❌ True
✅ False

Explanation:

  • A2>=100 → TRUE

  • B2>=250 → FALSE
    AND returns TRUE only if all conditions are TRUE.

Question 9

You need an IF formula that runs multiple tests, moving to the next test if one fails.
Which function must be nested?

❌ AND
❌ OR
✅ IF

Explanation:
Multiple conditions require nested IF functions (or the IFS function).

Question 10

Cell A2 = 200
Formula:

=IFS(
A2=50,"First Message",
A2=100,"Second Message",
A2=150,"Third Message",
TRUE,"No Message"
)

❌ First Message
❌ Second Message
❌ Third Message
✅ No Message

Explanation:
None of the conditions match A2 = 200,
so the final TRUE condition returns “No Message”.

🧾 Summary Table

Question Correct Answer Correct Option Key Concept
Q1 False Option B CONCAT spacing
Q2 mountain bike Option C LOWER function
Q3 32MainAvenue Option B LEFT function
Q4 True Option A Date serial numbers
Q5 05/30/23 Option B Static dates
Q6 2023 Option B YEAR function
Q7 NETWORKDAYS Option B Weekday calculation
Q8 False Option B AND logic
Q9 IF function Option C Nested IF
Q10 No Message Option D IFS evaluation