Which Excel Function Are You?

If you’ve been anywhere online in the past couple of years, you’ve probably seen those quizzes, such as Which Star Wars Character Are You? 

Now, it’s time to play a new game – Which Excel Function Are You?

We don’t need an online service to create our quiz – we can build it in Excel.

Which Excel Function Are You? blog.contextures.com/

Pick the Five Functions

Most of these games have 5 or 6 possible outcomes, so I’ll pick 5 Excel functions that have distinctive personalities.

  1. ROUND
  2. VLOOKUP
  3. MATCH
  4. EXACT
  5. COUNT

On a worksheet named Admin_Data, I created a table with a list of the functions, and a personality description for each one. To add a bit of color to the quiz results, I inserted clip art to match each description.

whichexcelfunctionareyou03

Create the Questions

Next, on a sheet named Quiz, I created 4 questions, with 5 possible answers for each question. Here is the first question.

whichexcelfunctionareyou04

In column F, which will be hidden later, I put a function ID, to match each function to one of the answers. In column G, an INDEX formula shows which function was assigned. That isn’t necessary, but it helped me assign the IDs.

whichexcelfunctionareyou05

Add Option Buttons

Next, each question gets an option button, and the buttons are in a group. You can click here to read more about setting those up.

Each group of buttons is linked to a cell in column H, in the same row as the question. In the screen shot below, I clicked the 4th option button, and 4 is entered in the linked cell – $H$4

whichexcelfunctionareyou06

Find the Selected Function

The final step on the Quiz sheet is to calculate which function was selected. An INDEX function is added in cell G4, and it returns the function ID in the selected position. The 4th answer was selected in this example, and the LOOKUP function, with ID of 2, is in that row.

whichexcelfunctionareyou07

Count the Results

On the Admin_Data sheet, I added COUNTIF formulas, to count the number of times that each function was selected. It looks for the IDs in column G on the Quiz sheet.

whichexcelfunctionareyou08

Next, a MAX formula gets the highest total, and a MATCH formula finds the first row with that total. So, if two or more functions are tied, the first function will be returned as the result.

Then, based on the MATCH result, the Function name and description are returned in INDEX formulas.

whichexcelfunctionareyou09

Get the Results

The remaining sheet is named Results, and it uses simple formulas to pull the selected formula name and description.

whichexcelfunctionareyou10

Show the Function Picture

To show the picture, I named the Picture heading cell in the Functions table – PicStart. I created another name – PicSel – with this formula;

=INDEX(Admin_Data!$G$3:$G$7,Admin_Data!$D$12)

I copied the cell with the first picture, and pasted it as a Linked Picture on the Results sheet. I made the copy bigger, so it would be easier to see.

When the picture is selected, you can see its link in the formula bar.

whichexcelfunctionareyou12

To make the correct picture show, change that formula to:  =PicSel

Press Enter, and the selected function’s picture will appear in the linked picture.

whichexcelfunctionareyou13

Finish the Workbook

To finish the workbook, clear out the numbers in column H on the Quiz sheet. Those cells are linked to the Option Buttons, so if no number is in the linked cell, no option will be selected.

Then, hide the working columns, F:H.

NOTE: If nothing is selected, the first function – ROUND – is shown as the result. You could build in some checks, so that no result is shown, unless the quiz is completed. If you are the EXACT function, you’ll probably get right on that!

And if you create new questions, or want to share your version of the quiz, please add a comment.

Download the Sample File

To see all the questions, and to take the quiz, please visit my Contextures website. On the Sample Excel files page, go to the Functions section, and look for FN0032 – Which Excel Function are You?

The file is in xlsx format, and does not contain macros.

You can add more questions, or select different functions, to create your own quiz. Or, use this structure to create a completely different type of quiz!

Which Excel Function Are You? blog.contextures.com/

_______________

9 comments to Which Excel Function Are You?

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>