Visual Basic Skills AssessmentTry our free on-line Assessment

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.

Answer downloading must be done from the quiz / competition spreadsheet - it cannot be done directly from this page.

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.

TopicLoadable answerYouTube answerVote
Overview of this assessment

    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.

       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
       Basics of using data tables

       Three scenarios - simple application of a data table

       Scenarios - Easy example

       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.

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

       Dates - generate first business day in successive months - harder

       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.


Modeling competition

Quiz spreadsheet    [XLS file]

Modeling competition

Quiz answers [PDF file]

   Live leader board

Other quizes


Financial modeling