CellReps

Practice

Pick a topic. Each drill is one formula in one cell.

Lookups

VLOOKUP, XLOOKUP, INDEX and MATCH · 0 of 4 solved

  1. Look up a product price with VLOOKUPLevel 1
  2. Show a friendly message when XLOOKUP finds nothingLevel 2
  3. Return a salary from a column left of the employee codeLevel 3
  4. Find a sales commission from a slab table with approximate matchLevel 3

Conditional totals

SUMIFS, COUNTIFS, AVERAGEIFS · 0 of 4 solved

  1. Total sales for one region and one category with SUMIFSLevel 2
  2. Count students at or above a pass mark held in a cellLevel 2
  3. Average order value for one channel and country, roundedLevel 3
  4. Sum sales between a start date and an end date held in cellsLevel 4

Logic

IF, IFS, SWITCH and error handling · 0 of 3 solved

  1. Turn a test average into a letter grade with IFSLevel 2
  2. Loan pre-check with nested IF and AND, despite a text credit scoreLevel 4
  3. Map country codes to shipping zonesLevel 3

Text

Clean, split and extract · 0 of 4 solved

  1. Pull the warehouse code out of a SKULevel 1
  2. Split a full name into first and last nameLevel 2 · paid
  3. Clean up messy names with PROPER, TRIM and SUBSTITUTELevel 3 · paid
  4. List invoice numbers over 50,000 in one cellLevel 4 · paid

Dates

Due dates, tenure, working days · 0 of 3 solved

  1. Work out an invoice due date with EDATELevel 2 · paid
  2. Count complete months of service with DATEDIFLevel 3 · paid
  3. Count working days with NETWORKDAYS and a holiday listLevel 4 · paid

Arrays

ARRAYFORMULA, FILTER, SORT · 0 of 2 solved

  1. Fill a column of line totals with one ARRAYFORMULALevel 3 · paid
  2. Filter pending expense claims and sort them smallest firstLevel 4 · paid