10 of 10 people found the following review helpful
The inside scoop on formulas and functions,
This review is from: Formulas and Functions: Microsoft Excel 2010 (MrExcel Library) (Paperback)
This softcover book covers four important areas of Excel usage:
Ranges and formulas
Harnessing the power of functions
Building business models
Building financial formulas
The first section covers all you ever wanted to know about ranges. The author devotes several pages to the Go To Special dialog box. Here, there are many different ways to select (highlight) cells in a range that have certain characteristics. For example, you can identify cells that are the same, those that are different, precedent and dependent cells and more. He also shows all the ways in which the fill handle can be used to create series.
This section also includes formulas, arrays and many valuable methods of troubleshooting.
The next section covers at some length using functions in various ways. Text, logical and information functions are covered, with some very interesting examples of how to use them to your advantage.
This section has extensive explanation of lookup, date, time, math and statistical functions. There are so many of each of these categories that most readers will only use a few of them.
The result of a given function can almost always be calculated directly with appropriate formulas, although it may be difficult or lengthy. It's worthwhile to learn how to use functions, because they can relieve you of al lot of work in setting up formulas to do the calculation directly.
The third section starts out with tables and pivot tables. The use of tables allows you to sort, find and summarize data more easily than using sorting and manually calculated subtotals. Pivot tables are easy to set up and allow summarization of tabular data in simple fashion. You can compress thousands of records of sales data, for example, into a few categories. The beauty of pivot tables is that you can easily rearrange the categories to display the summaries in a more meaningful way.
This section also includes a chapter on business modeling tools. These include What-if analysis, goal seeking and scenario management. Each of these can simplify or, in some cases make possible, answers to complex business problems. Another tool is this section is regression analysis, to allow intelligent forecasts for business problems.
Last in this section, but certainly not least, is Solver, which is an outgrowth of operations research and linear programming. It is something like Goal Seek, but with more than one variable input and the ability to impose constraints on them. It can identify a minimum result, such as cost, or a maximum, such as profit.
The final section deals with loans, interest rates and discount rates. It illustrates how to calculate discounted cash flows, internal rate of return and payback period. These are useful methods for any business.
This is a must-have book for any Excel user, especially those solving business problems.