Gamification 3: Challenges

This is the third post in the Gamification + Excel Training series, and in this post we talk about challenges. One component of our gamification strategy is to encourage competition by hosting challenges. In general, competition is motivating and can help people push themselves to accomplish more. So, it is an element used in our Campus Pass, where our goal is to provide a fun and motivating place to learn Excel.

As you can image, there is almost no limit to the types of Excel competitions we can design. But, in this post, we’ll provide an example of a formula challenge.

The idea is straightforward. You have to write a formula that provides the correct result. Given the vast number of functions available in Excel, there are many ways to write a formula that will provide the correct result. The beauty of formula challenges is that students can see other approaches to the same problem that they may not have considered. The student can then make a mental note of it in case they encounter a real-life workbook where it would be helpful.

For example, let’s say you are preparing an invoice in Excel, which looks a bit like this:

The Prices come from a pricing matrix that looks like this:

Challenge: Write a formula that retrieves the correct Price for each invoice line from the price matrix. You must be able to fill the formula down and it should work for all invoice rows.

If you’d like to give it a try, download the file below:

If you’d like to see one possible solution formula, check out the video below:


If you decide to participate, please feel free to copy your formula and paste it as a comment below…thanks!

 

 

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

9 Comments

  1. shakoor on January 21, 2020 at 10:38 am

    Dear Jeff.
    I have two methods for this problem. The first one is the match-index which you can see formula below:
    =INDEX($C$20:$E$23,MATCH($B11,$B$20:$B$23,0),MATCH($C11,$C$19:$E$19,0))
    The second one is sumproduct function. The formula is described below:
    =SUMPRODUCT((($B$20:$B$23)=$B11)*(($C$19:$E$19)=$C11)*($C$20:$E$23))

    I would like to know what’s your opinions about these methods.
    i look forward to hearing from you.

    • Jeff Lenning on January 21, 2020 at 10:39 am

      Well done Shakoor! I used the INDEX/MATCH/MATCH option as well in the solution video 🙂

  2. ROBERT BACON on January 22, 2020 at 9:50 am

    Hi Jeff,
    Per your first requirement of writing “the shortest formula you can . . . “, I offer the following 2-way lookup formula located in cell D11:
    =VLOOKUP(B11,$B$20:$E$23,MATCH(C11,$B$19:$E$19,0),0)

    Looking forward to your next challenge.
    Bob

    • Jeff Lenning on January 22, 2020 at 9:51 am

      Outstanding approach … I love it!

  3. Eric on January 22, 2020 at 10:48 am

    Completed! I started by making the price chart into a table and then did the index/match/match formula. Then I played around with adding a column to the right for X Large and a row at the bottom for Purple and it still worked!

    Thanks!

    • Jeff Lenning on January 22, 2020 at 10:57 am

      The table idea is great, and nice to hear you tested it with additional rows/columns … awesome!!

  4. Peter Puggaard on January 24, 2020 at 5:02 am

    I did a query from the price table/area, Unpivot data, Add Coloumn: Shirt Options & Size. Then the formula looks like:

    =VLOOKUP(TEXTJOIN(” “;1;B11:C11);$J$2:$K$14;2;0)

    My query data table is in J2:K14

  5. Scott Huval on January 28, 2020 at 11:22 am

    =VLOOKUP(B11,$B$20:$E$23,MATCH(C11,$C$19:$E$19,0)+1,0)

    i used the vlookup but added an additional column.

    • Jeff Lenning on January 28, 2020 at 11:24 am

      Scott … nice approach!!

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.