THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Creating formulas in Excel

Most people haven’t created a formula in Excel using the Equation Editor. If you are writing specifications for what statistical analyses must be performed on a project, it is appropriate to begin with a professional looking formula. Excel’s Equation Editor isn’t difficult to use and you shouldn’t be afraid of it. It is just a collection of templates or frameworks that you nest one inside another. Once you have practiced a few times with the Equation Editor, you should be able to create a formula like the one shown in Figure 1 in less than 3 minutes. You can achieve proficiency with the Equation Editor after creating only a few equations.

image

Figure 1. Standard deviation of a sample’s frequency distribution.

To invoke the Equation Editor, first click INSERT in the menu bar and then click Equation, which is on the right in the Symbols menu.

image

Figure 2. Click Equation to start the Equation Editor.

After Excel switches to Equation Editor mode, I suggest right-clicking where it says Type equation here and selecting a larger font size. I chose a 24 point font size.

image

Figure 3. Increasing the equation’s font size.

The next several steps consist of choosing the appropriate formatting templates for your equation. The order in which you build an equation makes a difference on how easy it is to complete it. Build starting at the outside and go progressively inward.

image

Figure 4. The easy part of my equation.

The outermost part of my equation is a radical, so that is where I began. I selected the square root template as shown.

image

Figure 5. Choosing the square root template.

Notice that the cursor is set to the right of the equation. That’s not where it needs to be for the next step.

image

Figure 6. The cursor appears to the right of the highlighted formula.

Click the square box to set focus there and click on Fraction to see the list of fraction templates.

image

Figure 7. Click the square box and select a template to insert where the square box is.

We need the stacked fraction template for our formula.

image

Figure 8. Selecting a stacked fraction template.

Once again, the cursor appears to the right. You’ll need to change focus twice, once to the numerator and once to the denominator.

image

Figure 9. Square root template containing a stacked fraction template.

Set focus to the numerator and select the simple summation operator.

image

Figure 10. Ready to place a summation operator in the numerator.

After the summation operator template in place, click the square box to set focus to it.

image

Figure 11. The superscript template is found under Script.

Select the superscript template.

image

Figure 12. Selecting the Superscript template.

You’ll need to set focus to each of the Superscript’s square boxes, one at a time.

image

Figure 13. Initial appearance of a newly inserted Superscript template.

Set focus to the exponent square box and enter the exponent.

image

Figure 14. Focus set to the exponent before entering it.

After entering the exponent, set focus to the base and enter the parentheses.

image

Figure 15. Parentheses for the base.

We need x bar for the mean of the sample, which requires the use of the Accent template.

image

Figure 16. Putting a bar over a symbol is accomplished with an Accent template.

Make sure the cursor is in the correct location before selecting the Bar template.

image

Figure 17. Selecting the Bar template.

Once the Bar template is in place, set focus to the square box and enter the letter x to create x bar.

image

Figure 18. Using the Bar template.

Once the numerator is complete, it is time to complete the denominator.

image

Figure 19. Numerator finally complete.

Set focus to the denominator to enter another summation template.

image

Figure 20. Fraction template with focus set to the denominator.

I recommend entering the parentheses before inserting the summation operator.

image

Figure 21. Ready to insert summation template.

image

Figure 22. Summation template inserted.

Set focus to the square box to set focus and add the f.

image

Figure 23. Focus set to add the f.

Add the minus and the 1 to complete the equation.

image

Figure 24. All items entered into the Equation Editor.

image

Figure 25. Impressive looking end result.

Published Monday, December 24, 2012 10:30 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement