When you first start building Excel formulas, the formula construction process can seem way too complicated. But understanding a few simple rules makes the process easier than you might think says bestselling computer book author Stephen L. Nelson.
Formula Basics
Formulas must begin with the equal sign (=); that’s how Excel distinguishes them from values and labels.
You can construct formulas that subtract, multiply, divide, and exponentiate. The – symbol means subtraction, the * means multiplication, the / means division, and the ^ means exponential operation.
Entering Formulas in Excel
Excel calculates formulas automatically. You enter them in a worksheet cell in the same way as you do with labels and values. In the cell, however, Excel displays not the formula, but its result. For example, if you enter a formula that says to add 4 and 2, Excel retains the formula and displays it in the formula bar when the cell is selected, but Excel displays the result, 6, in the worksheet itself.
Some Examples of Simple Excel Formulas
The points covered in the preceding paragraphs may not seem very powerful. But by applying the information, you can actually build a variety of useful Excel formulas. For example, if you enter the formula =4+2 into a cell, Excel returns the formula result 6.
Similarly, enter the formula =4-2 and Excel returns 2.
Enter the formula =4*2 and Excel returns 8.
Enter the formula =4/2 and Excel returns 2.
Finally, enter the formula =4^2 and Excel returns 16.
Building More Complicated Excel Formulas
To build more complicated formulas, you need to recognize the standard rules of operator precedence: Excel first performs exponential operations, then multiplication and division operations, and finally, addition and subtraction.
For example, in the equation =1+2*3^4, Excel first raises 3 to the fourth power to get 81. It then multiplies this value by 2 to get 162. Finally, it adds 1 to this value to get 163.
To override these rules, you must use parentheses. You can use multiple sets of parentheses in a formula as need be. Excel first performs the function in the innermost set of parentheses.
For example, enter the formula =1+2*3^4 and Excel returns 163.
Enter the formula =(1+2)*3^4 and Excel returns 243.
And a final example: Enter the formula =((1+2)*3)^4 and Excel returns 6561.
Using Cell References in Formulas
Suppose that you were building a budgeting worksheet and wanted to calculate the total of a handful of values.
You could total the budgeted expenses by entering a formula such as =500+50+500+2000+250 into a particular cell--say cell C7.
There is, however, a practical problem with this approach: You would need to rewrite the formula each time any of the values changed.
Because this approach is unwieldy, Excel also allows you to use cell references in formulas. When a formula includes a cell reference, Excel uses the value that cell contains.
For example, suppose that cells C1, C2, C3, C4 and C5 contained the values 500, 50, 500, 2000 and 250 and that you want to total these values and place the total value into cell C7.
You could enter the formula =500+50+500+2000+250 into cell C7.
Alternatively, you can instead enter the formula =C1+C2+C3+C4+C5 into cell C7.
Note, then, that to reference a cell on the same worksheet as the formula, you need to supply only the column-letter-and-row-number cell reference. To reference cell C1 on the same worksheet, for example, you enter C1.
Advanced Cell Referencing Skills
Many people work only with one worksheet Excel files. But you may be interested to know that you can also reference cells on other worksheets. To reference a cell on another worksheet in the same workbook, however, you need to precede the cell reference with the name of the worksheet and an exclamation point symbol. To reference cell C1 on the worksheet named Sheet2, for example, you enter Sheet2!C1.
You can reference cells in other workbooks, too. To do this most easily, open the other workbooks, begin building your formula as described earlier in this chapter, and then click the other workbook cell you want to reference at the point you want to include the reference. Excel then writes the full cell reference for you, which includes the workbook name. An external reference to cell C1 on the worksheet named Sheet2 in the workbook named Budget might be written as =[Budget.xls]Sheet2!$C$1.
Mastering Accrued Interest Calculations with Microsoft Excel
Microsoft Excel is not just a spreadsheet program; it's a powerful tool for financial calculations, including the computation of accrued interest for various securities. Excel's built-in functions, ACCRINT and ACCRINTM, are specifically designed to simplify the process of calculating accrued interest for securities that pay periodic interest and those that pay at maturity, respectively. This article delves into the nuances of these functions, providing a detailed guide on how to use them effectively, along with common pitfalls to avoid.
Using the Bond Duration Add-in Functions
Making bond duration calculations? Microsoft Excel can help. Excel provides two functions that help you with bond duration calculations.
Using Excel's Xirr and Xnpv Add-in Functions
Making internal rate of return or net present value calculations with Microsoft Excel? Make sure you aren't unnecessarily limiting your options. In addition to the well-know IRR and NPV financial functions, Microsoft Excel also supplies two powerful add-in functions, XIRR and XNPV, that can expand your analytical possibilities.
