Microsoft Office Specialist: Excel Exam Preparation: Perform operations by using formulas and functions

1. Frage 1 von 20

   Which function counts the cells in a range that are not empty, including cells with text?

   • ACOUNT
   • BCOUNTBLANK
   • CCOUNTA
   • DSUM
   Antwort anzeigen

   Antwort: C. COUNTA

   COUNT counts only numbers. Use COUNTA to count any cell that is not empty, such as text, numbers or errors.

2. Frage 2 von 20

   What does =SUM(B2:B6) return?

   	A	B
   1	Day	Calls
   2	Mon	14
   3	Tue	9
   4	Wed	17
   5	Thu	11
   6	Fri	20

   • A14.20
   • B71
   • C61
   • D20
   Antwort anzeigen

   Antwort: B. 71

   SUM adds every number in the range: 14 + 9 + 17 + 11 + 20 = 71.

3. Frage 3 von 20

   D2 contains =B2*C2. You copy D2 down to D3. What formula is in D3?

   • A=B2*C2
   • B=B3*C2
   • C=B3*C3
   • D=C2*D2
   Antwort anzeigen

   Antwort: C. =B3*C3

   Relative references change by the same number of rows and columns as the move. One row down, B2 and C2 become B3 and C3.

4. Frage 4 von 20

   The tax rate is in B1. C4 contains =B4*$B$1. You copy C4 to C5. What formula is in C5?

   • A=B5*B2
   • B=B4*$B$1
   • C=B5*$B$2
   • D=B5*$B$1
   Antwort anzeigen

   Antwort: D. =B5*$B$1

   B4 is relative, so it becomes B5. $B$1 is absolute, so it stays the same.

5. Frage 5 von 20

   You type =A1 and, with A1 selected in the formula, press F4 twice. What reference do you get?

   • A$A1
   • BA$1
   • CA1
   • D$A$1
   Antwort anzeigen

   Antwort: B. A$1

   F4 cycles through $A$1, A$1, $A1 and back to A1. The second press gives A$1 (row locked).

6. Frage 6 von 20

   What does =AVERAGE(B2:B7) return? (B4 is empty.)

   	A	B
   1	Week	Hours
   2	Wk 1	8
   3	Wk 2	6
   4	Wk 3
   5	Wk 4	10
   6	Wk 5	0
   7	Wk 6	6

   • A5
   • B7.5
   • C30
   • D6
   Antwort anzeigen

   Antwort: D. 6

   AVERAGE ignores empty cells but counts zeros: 30 ÷ 5 = 6.

7. Frage 7 von 20

   What does =MAX(B2:B7)-MIN(B2:B7) return?

   	A	B
   1	Guard	Rounds
   2	Ari	42
   3	Bo	57
   4	Cy	38
   5	Di	61
   6	Ed	45
   7	Flo	50

   • A23
   • B19
   • C99
   • D61
   Antwort anzeigen

   Antwort: A. 23

   MAX finds 61 and MIN finds 38; 61 − 38 = 23.

8. Frage 8 von 20

   What does =COUNT(B2:B9) return?

   	A	B
   1	Client	Gebühr
   2	C1	25
   3	C2	N/A
   4	C3
   5	C4	0
   6	C5	40
   7	C6
   8	C7	15
   9	C8	TBD

   • A8
   • B4
   • C3
   • D6
   Antwort anzeigen

   Antwort: B. 4

   COUNT counts only cells with numbers (25, 0, 40 and 15), so the result is 4. Text such as N/A and empty cells are not counted.

9. Frage 9 von 20

   What does =COUNTBLANK(B2:B9) return?

   	A	B
   1	Client	Gebühr
   2	C1	25
   3	C2	N/A
   4	C3
   5	C4	0
   6	C5	40
   7	C6
   8	C7	15
   9	C8	TBD

   • A3
   • B2
   • C4
   • D6
   Antwort anzeigen

   Antwort: B. 2

   COUNTBLANK counts empty cells only. B4 and B7 are empty; the 0 in B5 is a value, so it is not counted. The result is 2.

10. Frage 10 von 20

    D2 contains =IF(C2>=B2,"Met","Short") and is filled down to D5. Who gets "Short"?

    	A	B	C	D
    1	Name	Target	Actual	Result
    2	Ali	50	52
    3	Bo	40	40
    4	Cy	45	44
    5	Di	30	36

    • ACy
    • BDi
    • CBo
    • DAli
    Antwort anzeigen

    Antwort: A. Cy

    IF returns "Met" when Actual is greater than or equal to Target. Only Cy (44 against 45) is below target; Bo is equal, so Bo is Met.

11. Frage 11 von 20

    In a table, a formula reads =[@Hours]*[@Rate]. What does [@Hours] refer to?

    • AThe whole Hours column
    • BThe Hours cell in the same row as the formula
    • CThe Hours value shown in the table's total row
    • DThe Hours header cell
    Antwort anzeigen

    Antwort: B. The Hours cell in the same row as the formula

    The @ sign means "this row", so [@Hours] is the Hours value in the formula's own row.

12. Frage 12 von 20

    What does =SUM(Shifts[Hours]) add up?

    • AEvery cell on the Shifts sheet
    • BThe Hours column including the header and total row
    • COnly the Hours cell in the current row
    • DThe data cells in the Hours column of table Shifts
    Antwort anzeigen

    Antwort: D. The data cells in the Hours column of table Shifts

    Table[Column] refers to the data cells of that column; the header and total row are not included.

13. Frage 13 von 20

    B2 contains =$A2*B$1. You copy B2 to D4. What formula is in D4?

    • A=$A4*D$1
    • B=$A2*D$1
    • C=$A4*B$1
    • D=C4*D3
    Antwort anzeigen

    Antwort: A. =$A4*D$1

    $A2 keeps column A but its row moves down 2 (A4). B$1 keeps row 1 but its column moves right 2 (D$1).

14. Frage 14 von 20

    A2 contains CAL-2041-N. What does =MID(A2,5,4) return?

    • A041-
    • B-204
    • C2041
    • DCAL-
    Antwort anzeigen

    Antwort: C. 2041

    MID starts at character 5 and returns 4 characters. Characters 1–4 are "CAL-", so the result is 2041.

15. Frage 15 von 20

    A2 contains Bow River Patrol. What does =LEN(A2) return?

    • A3
    • B14
    • C15
    • D16
    Antwort anzeigen

    Antwort: D. 16

    LEN counts every character, including the two spaces, so the result is 16.

16. Frage 16 von 20

    A2 contains Priya and B2 contains Sandhu. What does =CONCAT(LOWER(LEFT(A2,1)),".",LOWER(B2)) return?

    • Apsandhu
    • Bpriya.sandhu
    • Cs.priya
    • Dp.sandhu
    Antwort anzeigen

    Antwort: D. p.sandhu

    LEFT(A2,1) takes "P", LOWER makes the parts lower case, and CONCAT joins them in order with the full stop between: p.sandhu.

17. Frage 17 von 20

    A2:A5 hold Ana, (empty), Raj and Li. What does =TEXTJOIN(", ",TRUE,A2:A5) return?

    • AAna, Raj, Li
    • BAnaRajLi
    • CAna, , Raj, Li
    • DAna,Raj,Li
    Antwort anzeigen

    Antwort: A. Ana, Raj, Li

    TEXTJOIN puts the delimiter (comma and space) between items. TRUE tells it to skip empty cells, so no extra comma appears.

18. Frage 18 von 20

    D2 contains =SORT(A2:B6,2,-1). Which name appears in D2?

    	A	B	C	D
    1	Name	Score
    2	Ivy	72
    3	Jon	88
    4	Kai	65
    5	Lea	91
    6	Max	80

    • AIvy
    • BJon
    • CKai
    • DLea
    Antwort anzeigen

    Antwort: D. Lea

    Sort index 2 sorts by the Score column, and -1 means descending, so the highest score (91, Lea) comes first.

19. Frage 19 von 20

    How many values does =UNIQUE(A2:A9) return?

    	A
    1	Site
    2	North
    3	South
    4	North
    5	East
    6	West
    7	South
    8	North
    9	East

    • A4
    • B1
    • C3
    • D8
    Antwort anzeigen

    Antwort: A. 4

    UNIQUE returns each distinct value once: North, South, East, West. (Only with exactly_once set to TRUE would it return just West.)

20. Frage 20 von 20

    You enter =UNIQUE(A2:A40) in C2. It should return 8 names, but C5 already contains text. What does C2 show?

    • A#N/A
    • B#VALUE!
    • C#SPILL!
    • D#NAME?
    Antwort anzeigen

    Antwort: C. #SPILL!

    UNIQUE and SORT spill their results into the cells below. The 8 results need C2:C9, and C5 is not empty, so the formula shows #SPILL! until you clear C5.

Tastatur: 1–4 zum Antworten, Eingabetaste für die nächste Frage.