Org charts in Google Sheets - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to get the manager's name from cell B2.

Google Sheets

=VLOOKUP([1], A2:B10, 2, FALSE)

Drag options to blanks, or click blank then click option'

AB2

BC2

CA2

DD2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the lookup value.

Confusing the lookup range columns.

The formula looks up the value in cell B2 (employee ID) in the range A2:B10 to find the manager's name.

2fill in blank

medium

Complete the formula to count how many employees report directly to the manager in cell A2.

Google Sheets

=COUNTIF(B2:B20, [1])

Drag options to blanks, or click blank then click option'

AB2

BD2

CC2

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the condition.

Counting the wrong range.

The COUNTIF function counts how many times the manager's name in A2 appears in the range B2:B20, which lists managers for employees.

3fill in blank

hard

Fix the error in the formula to display the employee's full reporting chain separated by arrows.

Google Sheets

=A2 & " -> " & [1]

Drag options to blanks, or click blank then click option'

AVLOOKUP(B2, B2:C10, 2, FALSE)

BVLOOKUP(A2, B2:C10, 2, FALSE)

CVLOOKUP(A2, A2:B10, 2, FALSE)

DVLOOKUP(B2, A2:B10, 2, FALSE)

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong lookup value cell.

Using incorrect lookup range columns.

The formula should look up the manager of the employee in A2, so the lookup value must be A2, not B2.

4fill in blank

hard

Fill both blanks to create a formula that lists employees who report to the manager in A2.

Google Sheets

=FILTER([1], [2] = A2)

Drag options to blanks, or click blank then click option'

AA2:A20

BB2:B20

CC2:C20

DD2:D20

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping employee and manager ranges.

Using ranges that don't match in size.

The FILTER function returns employees from A2:A20 whose manager in B2:B20 matches the manager in A2.

5fill in blank

hard

Fill all three blanks to create a formula that counts employees under a manager with name in cell C1 and shows 'None' if zero.

Google Sheets

=IF(COUNTIF([1], [2]) [3] 0, COUNTIF([1], [2]), "None")

Drag options to blanks, or click blank then click option'

AB2:B30

BC1

C>

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operator.

Using wrong cell reference for manager name.

The formula counts how many employees have the manager name in C1 within B2:B30. If the count is greater than zero, it shows the count; otherwise, it shows 'None'.