See what our courses are likeTake a tour

# Spreadsheet skills quiz / competition

The table below shows topics in our spreadsheet skills quiz / competition. The first column of ticks and crosses indicates whether a solution to the topic can be downloaded into the quiz spreadsheet. The second column shows whether there is a YouTube explanation of how to solve the problem.

In the third column you can "vote" on topics you would like added to the set of YouTube explanations. You can choose up to five topics and you can change your choice at any time. Your choices will be remembered until the next time you change them.

Overview of this assessment
Overview
Introduction

Aggregation
Conditional counting and summing
Conditionally summing - A simple problem illustrating the SUMIF function

Elaboration of preceding question - need to avoid 'hardcoding' the answer

Summing top 'N' - A short question - but very difficult

Counting conditionally - not easy - but not too difficult either.

Grouping and further applications
Grouping by date - use SUMPRODUCT - a good illustration of SUMPRODUCT's power

Conditionally summing between dates - use COUNTIF or SUMPRODUCT - not trivial

Conditionally summing - use an array & SUM & SUMIF - a fairly hard question.

Referencing
Lookups
One-dimensional lookup - Easy - use VLOOKUP

Two-dimensional lookup - bit more difficult - can use VLOOKUP & MATCH

Three-dimensional lookup - Harder - can use VLOOKUP, MATCH & INDIRECT

Applications of referencing
Interpolating - difficult - VLOOKUP isn't up to it - can use MATCH & OFFSET & others.

Depreciation / variable length 'window' - A good example of use of OFFSET function

Arithmetic and logic
Sample applications
Comparing data sets - Easy to get it almost right - harder to get it completely right.

Detecting duplicates - Try to do it in a single cell - but several is ok too

Mean / average - Can use SUM, SUMPRODUCT, PRODUCT to solve this.

IF statements and nesting
Dividend policy - easy - using IF statements.

Thresholds - Need to use 'nested' IF statements - bit more complicated

Further IF statements and logic
A fairly wordy problem. Need to 'translate' from English to Excel statements - IF, etc.

Sensitivities and scenarios
Sensitivities
Basics of using data tables

Three scenarios - simple application of a data table

Scenarios
Scenarios - Easy example

Financial
Financial
Paying down a loan - has a twist - avoid circular references - they're not needed here

A good test of your grasp of the concepts underlying the NPV & PMT family of functions.

A more complex version of the above - avoid the solver - we want a solution that is automatic.

NPV - When does it assume the first cash flow occurs? That leads to the answer.

XNPV - Which date does it value for? That leads to the answer.

Miscellaneous
Dates
Dates - generate date of first day in month for successive months - easy

Dates - generate first business day in successive months - harder

Sorting
Sorting - dynamically - fairly easy

Sorting - dynamically - a small change to the preceding problem makes it much harder

Error Handling
Detecting errors in a block of cells - harder than it looks

Lookup - Would be simple - but must generate a default value if the lookup fails.