Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 3

Formulas, Functions, and Relative and Absolute Addressing

This chapter explains formulas, functions, and the addressing features or issues in Excel. I will show simple and advanced ways to create formulas and functions more efficiently. The focus is on speed, accuracy, and the ability to duplicate and repeat the functions and the formulas—saving valuable time and effort.

In Figure 3.1, you can see a simple payroll example that you can find in the Excel workbook for Chapter 3. The sheet is named Simple Payroll Before. To calculate the salary, Hours worked × Rate in cell E3: Type the = (equal) sign first; then click on cell C3, type ∗, and click on cell D3. When you press the Enter key, the formula will calculate the result. Note how the cursor will skip down to cell E4. Once you are in cell E4 and you want to duplicate the result for all other employees, you have to select E3 again. This is an extra step. In order to save this additional step you can do one of two things: after entering the formula, rather than pressing the Enter key, either you click on the green check sign √ (to the left of the formula bar) or use CTRL+Enter. The cursor will remain in the selected cell. See Figure 3.1.

FIGURE 3.1 Use CTRL+Enter to Save a Step

When the operation is completed in cell E2, ...