The key to building advanced formulas for Excel is the use of functions. Functions are pre-generated formulas that can manipulate the data in cells. Combining traditional math operations with functions can create sophisticated formulas.
Functions for Advanced Excel Formulas
- PMT: The PMT function calculates the details of a loan, assuming a constant interest rate and payment amount. You'll need to supply the following arguments to the PMT function:
- Rate: the interest rate of the loan
- Nper: the total number of payments
- Pv: The present value of the loan, also known as the principal
- WORKDAY: This returns the date of a day that is the number of workdays before or after the starting date. The arguments required are the starting date, the number of days to project (positive for future, negative for past) and, optionally, any holidays that should be taken into account.
- IF: Evaluates a condition and returns a true or false result. For example: =IF(A1>10,"Over 10","10 or less") would return "Over 10" if the value of A1=13 and "10 or less" if the value of A1=7.
- SUMIF: Returns the conditional sum of a range of cells. For example: The function =SUMIF(A1:A3, ">5") with cell values of A1=5, A2=3, A3=6, will return a value of 11.
Powerful Ways To Modify Formulas
- 3-D formulas: Your formula can access cells in more than one worksheet. A reference (to a cell or range of cells) that refers to the same range on several worksheets is called a 3-D reference. A 3-D reference uses the names of the worksheet pages in combination with the cell references separated by an exclamation point. For example: =SUM(Sheet1:Sheet3!A2:A8) will calculate the sum of the cells in range A2 to A8 for all three pages (Sheet1, Sheet2 and Sheet3).
- Named ranges: To make your formulas easier to read, you can assign names to ranges of cells and then use the range name in the body of a formula. Naming a range of cells is as simple as selecting a range of cells with your mouse and entering the range name in the name box (located next the function bar below the main menu). Once named, the range can be referenced in any formula in the spreadsheet.
All of this can get a bit tricky, which is why Microsoft Excel Classes are available for those new to advanced formula functions. When you're looking for Microsoft Excel training, be sure to find out if the specific areas you need are covered; many classes simply cover the basics.