# Office Wide Excel Skills Test



## timreichert (Dec 7, 2005)

We are a small office (25 people) of an economic consulting firm, and are considering running a contest for "excel bragging rights."  We need to find a test of advanced excel skills.  Can you recommend one?

Thanks in advance.


----------



## Smitty (Dec 7, 2005)

Welcome to the Board!

There are all kinds of tests out there.  Here's a start: http://www.microsoft.com/learning/mcp/OfficeSpecialist/default.asp

A google search will also turn up plenty, like http://stafftesting.com/tests.htm.

The degree and type of test is really subjective though.  Like what level?  Beginner, Intermediate, Advanced?  And is it a General test, basic Formulas, advanced Formulas, VBA?

Just some thoughts,

Smitty


----------



## toddbretl (Dec 7, 2005)

I am also from the same office.  We're looking specifically for some exceptionally difficult questions, that would test someones knowledge of excel formulas, without involving VBA.


----------



## PaddyD (Dec 7, 2005)

Well there are plenty here!  

How about:

Devise a formula that will return the first visible cell from a filtered list.

Devise the shortest formula for returning, in alphabetical order, the just letters from an alphanumeric string (eg 'abc' from '1b23a9c2')


----------



## Zack Barresse (Dec 8, 2005)

How about...


*[1]*
A table of data (including headers), dates in column A, multiple conditions to meet in subsequent columns (we'll say B & C).  Return the most recent match from column D where criteria matches in column B & C with any date less than or equal to the starting date from column A.

Variables:_
Date
Column B criterion
Column C criterion
_
*[2]*
Sum a multi-conditional range with AutoFilter on.


*[3]*
Show a ranked list of unique ranks.


*[4]*
Use Conditional Formatting to highlight every odd row in a specified range.


*[5]*
Sum a column based on multiple conditions.


*[6]*
Count a column based on multiple conditions.


*[7]*
With Data Validation, prevent duplicates from being entered into a range.


*[8]*
Put the sheetname into a cell.


*[9]*
Use a VLOOKUP based from multiple conditions.


*[10]*
Extract only numerics from an alphanumeric cell value.


----------



## Greg Truby (Dec 8, 2005)

Are the contestants allowed to search MrExcel.com, OzGrid, et al for help developing the answers?  I'd think that'd be a key rule.


----------



## Smitty (Dec 8, 2005)

> Are the contestants allowed to search MrExcel.com, OzGrid, et al for help developing the answers?


Onlt the ones who know about it.    

Smitty


----------



## toddbretl (Dec 12, 2005)

Nope, I believe the plan is to turn off the office internet, or otherwise prevent people from using those websites.  Thanks paddyd and firefytr, those are the types of questions we're after.


----------



## Greg Truby (Dec 12, 2005)

How about a formula to allow a user to enter an item's SKU into the cell beside it and the formula creates a hyperlink to the matching SKU and product description on another worksheet?


----------



## Zack Barresse (Dec 12, 2005)

Note that I tend to think the questions I posted are geared towards an Intermediate-type formula level.  If you are not looking for something like that, how about just asking how to use or how to build some formulas?  I.e. _Rank this column of numbers; Use the DGET function on this (xxx) data range; Use the HLOOKUP function on this (xxx) data range;_ etc.  I guess it depends on how *advanced* your office-wide skills go.  That interpretation can vary from office to office.

Then there is always questions you can ask about native functions, such as Autofilter, AdvancedFilter, Text to Columns, Sheet/Workbook Protection, Find/Replace, Defined Names, Security Settings, Subtotals, Sorting, Pivot Tables, Charting, Hyperlinks, Comments, etc.


----------



## Greg Truby (Dec 12, 2005)

<sup>¿¿</sup>Intermediate<sub>??</sub>  Am I overlooking some very simple strategy on your #10.  I didn't work out a formula for it, but my first inclination is some unholy mix of Substitute in an array formula that uses Char(Indirect(Rows(65:91))) to do this.  *Not* what _I_ would call intermediate.  Is there some very easy strategy that I've not considered?  Or do you consider knowledge of the Indirect(Rows()) trick in array formulae to be "intermediate" and if so, *what* (dare I ask) would you consider "advanced"?


----------



## Zack Barresse (Dec 12, 2005)

LOL!  Okay, some may borderline Advanced.  Especially w/o VBA, as I would turn to first with RegExp most likely.  Would it be safer to say that list was _Intermediate-Advanced_??


----------



## Greg Truby (Dec 12, 2005)

Ah, but Mssr. Zack, in the third post, todd said "without involving VBA".  How you gonna use RegExp w/o VBA?  And even if this were to allow VBA/UDF's, I would _still_ call RegExp an advanced topic since it is not in the libraries one would normally use.  You'd have to know of its existance from other programming experience.  (Of course, you realize that if Nate reads this he's gonna have "no, no use a quick stack binary array!!!" ringing in his head    -- but that's still VBA)  So, yes, I guess I'm saying _Intermediate-Advanced._


----------



## Oaktree (Dec 12, 2005)

To be fair, Zack never said you had to use a formula... and, he only said from _an_alphanumeric cell value.

You could tell by looking at 12ABC456DEF that the numerics are 12456. 

I'd call that basic--even "remedial" Excel.


----------

