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.
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:
- Order of Precedence.
- “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 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
Here’s the explanation of logic.
- C1 + C2 = 50 + 150 = 200
- 200/2 = 100
Did you pass this short Excel quiz?
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.