THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
 in John Paul Cook (Entire Site) Search

# 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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

Figure 11. The superscript template is found under Script.

Select the superscript template.

Figure 12. Selecting the Superscript template.

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

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

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

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

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

Figure 15. Parentheses for the base.

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

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.

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.

Figure 18. Using the Bar template.

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

Figure 19. Numerator finally complete.

Set focus to the denominator to enter another summation template.

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

I recommend entering the parentheses before inserting the summation operator.

Figure 21. Ready to insert summation template.

Figure 22. Summation template inserted.

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

Figure 23. Focus set to add the f.

Add the minus and the 1 to complete the equation.

Figure 24. All items entered into the Equation Editor.

Figure 25. Impressive looking end result.

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

(required)
(required)
Submit