Working with Formulas and Functions in Excel 2010
- Introduction
- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names
Introduction
Once you enter data in a worksheet, you’ll want to add formulas to perform calculations. Microsoft Excel can help you get the results you need. Formulas can be very basic entries to more complex ones. The difficulty of the formula depends on the complexity of the result you want from your data. For instance, if you are simply looking to total this months sales, then the formula would add your sales number and provide the result. However, if you were looking to show this months sales, greater than $100.00 with repeat customers, you would take a bit more time to design the formula.
Because Microsoft Excel automatically recalculates formulas, your worksheets remain accurate and up-to-date no matter how often you change the data. Using absolute cell references anchors formulas to a specific cell. Excel provides numerous built-in functions to add to your worksheet calculations. Functions, such as AVERAGE or SUM, allow you to perform a quick formula calculation.
Another way to make your formulas easier to understand is by using name ranges in them. Name ranges—a group of selected cells named as a range—can help you understand your more complicated formulas. It is a lot easier to read a formula that uses name ranges, then to look at the formula and try to decipher it. Excel offers a tool to audit your worksheet. Looking at the “flow” of your formula greatly reduces errors in the calculation. You can see how your formula is built, one level at a time through a series of arrows that point out where the formula is pulling data from. As you develop your formula, you can make corrections to it.