MARISSA JONES ONLY!!!!

Understand Formulas

Learning Outcomes

· Explain how a formula works

· Identify Excel arithmetic operators

Excel is a truly powerful program because users at every level of mathematical expertise can make calculations with accuracy. To do so, you use formulas. A  formula  is an equation in a worksheet. You use formulas to make calculations as simple as adding a column of numbers, or as complex as creating profitand- loss projections for a global corporation. To tap into the power of Excel, you should understand how formulas work.  Managers at R2G use the Project Leader Payroll Calculator workbook to keep track of employee hours prior to submitting them to the Payroll Department. You’ll be using this workbook regularly, so you need to understand the formulas it contains and how Excel calculates the results.

Steps

1. 1

Click cell E5

The active cell contains a formula, which appears on the formula bar. All Excel formulas begin with the equal sign ( = ). If you want a cell to show the result of adding 4 plus 2, the formula in the cell would look like this: =4+2. If you want a cell to show the result of multiplying two values in your worksheet, such as the values in cells B5 and D5, the formula would look like this: =B5*D5, as shown in  Figure 1-5 . While you’re entering a formula in a cell, the cell references and arithmetic operators appear on the formula bar. See  Table 1-2  for a list of commonly used arithmetic operators. When you’re finished entering the formula, you can either click the Enter button on the formula bar or press [Enter].

Figure 1-5Viewing a Formula

Table 1-2

Excel Arithmetic Operators

operator	purpose	example
+	Addition	=A5+A7
−	Subtraction or negation	=A5−10
*	Multiplication	=A5*A7
/	Division	=A5/A7
%	Percent	=35%
^(caret)	Exponent	=6^2 (same as 62)

2. 2

Click cell F5

This cell contains an example of a more complex formula, which calculates overtime pay. At R2G, overtime pay is calculated at twice the regular hourly rate times the number of overtime hours. The formula used to calculate overtime pay for the employee in row 5 is:

O/T Hrs times (2 times Hrly Rate)

In the worksheet cell, you would enter: =C5*(2*D5), as shown in  Figure 1-6 . The use of parentheses creates groups within the formula and indicates which calculations to complete first—an important consideration in complex formulas. In this formula, first the hourly rate is multiplied by 2, because that calculation is within the parentheses. Next, that value is multiplied by the number of overtime hours. Because overtime is calculated at twice the hourly rate, managers are aware that they need to closely watch this expense.

Figure 1-6Formula with Multiple Operators

Details

In creating calculations in Excel, it is important to:

· Know where the formulas should be

An Excel formula is created in the cell where the formula’s results should appear. This means that the formula calculating Gross Pay for the employee in row 5 will be entered in cell G5.

· Know exactly what cells and arithmetic operations are needed

Don’t guess; make sure you know exactly what cells are involved before creating a formula.

· Create formulas with care

Make sure you know exactly what you want a formula to accomplish before it is created. An inaccurate formula may have far-reaching effects if the formula or its results are referenced by other formulas, as shown in the payroll example in  Figure 1-6 .

· Use cell references rather than values

The beauty of Excel is that whenever you change a value in a cell, any formula containing a reference to that cell is automatically updated. For this reason, it’s important that you use cell references in formulas, rather than actual values, whenever possible.

· Determine what calculations will be needed

Sometimes it’s difficult to predict what data will be needed within a worksheet, but you should try to anticipate what statistical information may be required. For example, if there are columns of numbers, chances are good that both column and row totals should be present.