Formula bar and cell preview in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Understanding Formula Bar Output

You enter the formula =SUM(A1:A3) in cell B1. The cells A1, A2, and A3 contain the values 5, 10, and 15 respectively.

What will the formula bar and the cell preview show in cell B1?

A Formula bar: 30<br>Cell preview: 30

B Formula bar: 30<br>Cell preview: =SUM(A1:A3)

C Formula bar: =SUM(A1:A3)<br>Cell preview: 30

D Formula bar: =SUM(A1:A3)<br>Cell preview: =SUM(A1:A3)

💡 Hint

Remember, the formula bar shows the formula itself, while the cell preview shows the result of the formula.

The formula bar always displays the formula you typed, such as =SUM(A1:A3). The cell preview shows the calculated result, which is 5 + 10 + 15 = 30.

📊 Formula Result

intermediate

Formula Bar and Text Values

Cell A1 contains the text "Hello". You enter the formula =A1 in cell B1.

What will the formula bar and cell preview show in cell B1?

A Formula bar: =A1<br>Cell preview: Hello

B Formula bar: Hello<br>Cell preview: =A1

C Formula bar: ="Hello"<br>Cell preview: Hello

D Formula bar: Hello<br>Cell preview: Hello

💡 Hint

The formula bar shows the formula entered, even if it references text.

The formula bar shows the formula =A1 exactly as entered. The cell preview shows the value in A1, which is the text Hello.

❓ Function Choice

advanced

Choosing the Correct Formula for Cell Preview

You want cell B1 to show the average of numbers in cells A1 through A5. Which formula entered in B1 will show the correct average in the cell preview and the formula in the formula bar?

A =AVERAGE(A1:A5)

B =SUM(A1:A5)/5

C =MEAN(A1:A5)

D =AVG(A1:A5)

💡 Hint

Excel has a specific function for average calculation.

The correct Excel function for average is AVERAGE. SUM divided by 5 works only if all cells have values, but AVERAGE is the standard function. MEAN and AVG are not valid Excel functions.

❓ data_analysis

advanced

Interpreting Formula Bar and Cell Preview with Errors

You enter the formula =A1/B1 in cell C1. Cell A1 contains 10, and cell B1 is empty.

What will the formula bar and cell preview show in cell C1?

A Formula bar: 10/0<br>Cell preview: #VALUE!

B Formula bar: #DIV/0!<br>Cell preview: =A1/B1

C Formula bar: =A1/B1<br>Cell preview: 0

D Formula bar: =A1/B1<br>Cell preview: #DIV/0!

💡 Hint

Dividing by zero or empty cell causes a specific error in Excel.

The formula bar always shows the formula =A1/B1. Since B1 is empty (treated as zero), the cell preview shows the division error #DIV/0!.

🎯 Scenario

expert

Predicting Cell Preview with Nested Formulas

You enter the formula =IF(SUM(A1:A3)>20, "High", "Low") in cell B1.
Cells A1, A2, and A3 contain 8, 7, and 6 respectively.

What will the formula bar and cell preview show in cell B1?

A Formula bar: =IF(SUM(A1:A3)>20, "High", "Low")<br>Cell preview: Low

B Formula bar: =IF(SUM(A1:A3)>20, "High", "Low")<br>Cell preview: High

C Formula bar: Low<br>Cell preview: =IF(SUM(A1:A3)>20, "High", "Low")

D Formula bar: High<br>Cell preview: High

💡 Hint

Add the numbers in A1 to A3 and compare to 20 to decide the IF result.

The formula bar always shows the formula =IF(SUM(A1:A3)>20, "High", "Low"). Sum of 8 + 7 + 6 = 21 > 20, so IF returns "High". Cell preview shows High.