Excel Quiz

This will be the shortest Excel quiz you ever see; the shortest Excel quiz you’ll ever take.

Who is “My Dear Aunt Sally”?

If you’ve already know “My Dear Aunt Sally”, you probably don’t need this quiz.  But my Aunt Sally is pretty important to Excel users.

A Short Excel Quiz

Look at the short spreadsheet shown below.  Using Excel on your computer, solve the problem.  A decent Excel user will solve the problem in less than a minute.

Short Excel Quiz

Was this Excel Quiz Difficult?

In my Excel training ten to fifteen years ago, only 10% of my Introduction to Excel students (adults actually) would solve this problem quickly.

In recent training classes, the number of Intro Excel students solving this problem has climbed from 10% to more like 20-30%.  People are becoming more knowledgeable about Excel.  Also, college students have learned some basic Excel formula logic, and they can solve this Excel quiz easily.

But still, many adult computer users don’t have the correct answer to this Excel problem.

Let’s Work on this Excel Problem Together

You may want an answer to this problem.

Using your brain, what is the answer?

  • 50 + 150 = 200
  • 200 / 2 = 100

Did you get the answer 100 in cell A4?

Did you solve the problem at all?

Or did your formula produce the answer 125?

Excel does what its been programmed to do, not what you think it should to do.

Yes, that’s the harsh and beautiful truth about Excel.

Excel does what its been programmed to do, now what you think it should do.

If your formula generated the answer 125, you need my help.  If you were unable to create a formula for cell A4, you also need my assistance.

Here’s the reason so many people still have trouble with this short Excel quiz:

  1. Order of Precedence.
  2. “My Dear Aunt Sally”.

Order of Precedence

This is the technical explanation of how Excel does formulas.  It sounds pretty scary doesn’t it:  order of precedence.

This is Excel’s explanation of operator precedence taken from their website .

If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table. If a formula contains operators with the same precedence — for example, if a formula contains both a multiplication and division operator — Excel evaluates the operators from left to right.

Did the Excel explanation from their website make sense to you?  In all fairness, I gave you only one paragraph of the explanation.  But frankly, if I listed the entire discussion at their website , you wouldn’t have enjoyed it.

It’s time to meet My Dear Aunt Sally.

My Dear Aunt Sally

She’s a lovely gal, you should meet her.  It’s a learning acronym for the concept:

Computers multiply and divide before they add and subtract.

That’s why many of you failed with your formula at the start of this blog.

My Dear Aunt Sally Explains the “125” Formula Mistake

Who needs to understand MDAS?

  • Beginners – Your formula was wrong, obviously you need to understand My Dear Aunt Sally (order of precedence).
  • Intermediates – Even if you had a correct formula, one day you’ll meet or manage someone who needs to understand the logic of “My Dear Aunt Sally”.

Here’s the common MDAS mistake in the short excel quiz.

MDAS Explains Mistake

 MDAS Explains Excel Mistakes

MDAS rules the logic of Excel formulas.  Computers (and Excel), multiply and divide before they add and subtract.

So in the above formula for C4:

  • C2/2 = 150/2 = 75
  • C1 + 75 = 50 + 75 + 125

That’s why some of you mistakenly had the answer 125.  It’s MDAS ruling Excel formulas.

Parentheses are the Answer

When I tell classes that they need a parenthesis, they normally exclaim, “Oh right, I should have used a parenthesis.”

Maybe they knew this, maybe they forgot, but the key concept is that they didn’t use a parenthesis.

Under time pressure, even people who know about using parentheses in formulas forget to use them.  And forgetting to use parentheses in Excel formulas can have very bad, inaccurate results.

MDAS with a Parenthesis is the Answer

See below.

MDAS Solution

Here’s the explanation of logic.

  • C1 + C2 = 50 + 150 = 200
  • 200/2 = 100

Did you pass this short Excel quiz?

Maybe.

If you thought the question was too simple for you, then you’re a bit more advanced than other Excel users.  Believe me, there are many managers who manage people who have no understanding of order of precedence or My Dear Aunt Sally.

If you didn’t pass this short Excel quiz, or had trouble doing it quickly, then hopefully Your Excel Coach has taught you something important today.

 

 

 

 


Comments

Excel Quiz — 4 Comments

    • Carolyn,
      You raise an interesting point: why skip a row before a formula?
      All I can tell you is that in using spreadsheets since the Lotus 123 days, nobody has ever asked that question.
      I think its useful visually to skip a row before a formula, and my classes have agreed.
      So for now, we will agree to disagree. Thanks for your comment.
      Richard

  1. This is almost basic Maths 101 from junior school. It is similar to the acronym we were taught at school – BODMAS = brackets of division, mulitplication, addition and subtraction OR BOMDAS (swapping the multiplication and division around!).
    Cheers

    • Wayne,
      Thanks for your reply.
      Yes, it is basic algebra.
      But too many Excel users have forgotten their basic algebra from years ago.
      Richard

Leave a Reply to WayneCancel reply