101 Ready-to-Use Excel Formulas

Chapter 2: Common Mathematical Operations

Most Excel analysts working in the corporate world are asked to perform mathematical operations that provide insight into key operational metrics. Calculations such as percent of totals, variance to budget, and running totals are the cornerstone of any basic business analysis. In this chapter, you explore 12 mathematical operations commonly used in the world of business analytics.

You can download the files for all the formulas at www.wiley.com/go/101excelformula.

Formula 1: Calculating Percent of Goal

When someone asks you to calculate a percent of goal, she is simply saying to compare actual performance to a stated goal. The math involved in this calculation is simple: Divide the goal by the actual. This gives you a percentage value that represents how much of the goal has been achieved. For instance, if your goal is to sell 100 widgets, and you sell 80, your percent of goal is 80 percent (80/100).

Percent of Goal can also be referred to as percent of budget or percent of forecast; it all means the same thing.

In Figure 2-1, you see a list of regions with a column for goals and a column for actuals. Note that the formula in cell E5 simply divides the value in the Actual column by the value in the Goal column.

=D5/C5

Figure 2-1: Calculating ...