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.
Topic  Loadable answer  YouTube answer  Vote 

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  
Onedimensional lookup  Easy  use VLOOKUP 



Twodimensional lookup  bit more difficult  can use VLOOKUP & MATCH 



Threedimensional 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. 



Quiz spreadsheet [XLS file]
Quiz answers [PDF file]
Live leader board  

Leader  Percent  At 
test  25.8%  
alex  0.5%  
  0.1% 