New book: MOS 2010 Study Guide for Microsoft Word, Excel, PowerPoint, and Outlook

648753ver1.inddMOS 2010 Study Guide for Microsoft Word, Excel, PowerPoint, and Outlook (ISBN 9780735648753; 736 pages) is now available in stores and online. Demonstrate your expertise with Microsoft Office 2010! This all-in-one guide is designed to help you practice and prepare for the four core Microsoft Office Specialist (MOS) exams covering Word, Excel, PowerPoint, and Outlook.

 

Here is an excerpt from the Excel section of the study guide:

5 Applying Formulas and Functions

The skills tested in this section of the Microsoft Office Specialist exam for Microsoft Excel 2010 relate to calculating data by using formulas. Specifically, the following objectives are associated with this set of skills:
5.1 Create Formulas
5.2 Enforce Precedence
5.3 Apply Cell References in Formulas
5.4 Apply Conditional Logic in Formulas
5.5 Apply Named Ranges in Formulas
5.6 Apply Cell Ranges in Formulas

Excel has been referred to as the world’s most popular database program because you can store vast amounts of data within a single workbook or worksheet. This was not, however, the purpose for which Excel was created. By using the many functions built in to Excel, you can build formulas that perform complex data calculations. Excel 2010 includes many features that simplify the process of creating complex formulas.

This chapter guides you in studying ways of creating basic formulas, controlling the order in which Excel performs calculations within formulas, calculating data based on specific requirements, and referencing data within a formula.

image

 

5.1 Create Formulas

 

Formulas in Excel can be made up of values that you enter, cell references, names, mathematical operators, and functions. A function can be thought of as a service provided by Excel to do a specific task. That task might be to perform a math operation, to make a
decision based on information you give it, or to perform an action on some text.

A function is always indicated by the function name followed by a set of parentheses. For most functions, arguments inside the parentheses either tell the function what to do or indicate the values that the function is to work with. An argument can be a value that you enter, a cell reference, a range reference, a name, or even another function. The number and type of arguments vary depending on which function you’re using. It is important to understand the syntax of common functions and be able to correctly enter the function
arguments. Fortunately, you don’t have to memorize anything; Excel 2010 does an excellent job of walking you through the process of using a function within a formula. You can type a function’s syntax yourself if you want, but it’s almost always easier to let Excel guide
you through the process.

Probably the most common formula used in Excel is one that totals the values in a set of cells. Rather than individually adding the values of all the cells you want to total, you can use the SUM function to perform this task. The following table describes other functions that allow you to summarize information from sets of cells.

image

In the previous table, any argument specified as a number can be a number that is entered directly, a text representation of a number (a number inside of quotation marks), a cell reference, a range reference, or a named reference. Any cells that have text which can’t be translated to a number, that are empty, or that have an error are simply ignored by the function.

Also in the table, any argument specified as a value can be any type of value. In the case of the COUNT() function, it will simply ignore any value that it can’t interpret as a number. In the case of COUNTA(), it will count every cell that isn’t empty.

image

 

To sum values in a cell range
1.
Select the cell immediately below or to the right of the values you want to total.
2. On the Home tab, in the Editing group, click the AutoSum button.

Or
On the Formulas tab, in the Function Library group, click the AutoSum button.
3. Verify that the cell range displayed in the formula is correct, and then press Enter.
 

Or
1. Select the cell in which you want to place the total.
2. On the Formulas tab, in the Function Library group, click the Math & Trig button,
and then in the list, click SUM.
3. In the Function Arguments box, enter the cell range you want to total, and then
click OK.

➤ To count cells containing numeric values
1.
Select the cell immediately below or to the right of the range within which you want
to count the cells containing numeric values.
2. On the Formulas tab, in the Function Library group, click the AutoSum arrow, and
then in the list, click Count Numbers.
3. Verify that the cell range displayed in the formula is correct, and then press Enter.

Or
1. Select the cell in which you want to place the count.
2. On the Formulas tab, in the Function Library group, click the More Functions
button, point to Statistical, and then in the list, click COUNT.
3. In the Function Arguments box, enter the cell range within which you want to
count non-empty cells, and then click OK.

➤ To count cells containing any type of value
1.
Select the cell in which you want to place the count.
2. On the Formulas tab, in the Function Library group, click the More Functions
button, point to Statistical, and then in the list, click COUNTA.
3. In the Function Arguments box, enter the cell range within which you want to
count the cells that contain any type of value, and then click OK.
➤ To average values in a data range
1. Select the cell immediately below or to the right of the values you want to average.
2. On the Formulas tab, in the Function Library group, click the AutoSum arrow, and
then in the list, click Average.
3. Verify that the cell range displayed in the formula is correct, and then press Enter.

Or
1. Select the cell in which you want to place the average.
2. On the Formulas tab, in the Function Library group, click the More Functions
button, point to Statistical, and then in the list, click AVERAGE.
3. In the Function Arguments box, enter the cell range that you want to average,
and then click OK.

➤ To find the lowest value in a data range
1.
Select the cell immediately below or to the right of the values you want to evaluate.
2. On the Formulas tab, in the Function Library group, click the AutoSum arrow, and
then in the list, click Min.
3. Verify that the cell range displayed in the formula is correct, and then press Enter.
Or
1. Select the cell in which you want to place the minimum value.
2. On the Formulas tab, in the Function Library group, click the More Functions
button, point to Statistical, and then in the list, click MIN.
3. In the Function Arguments box, enter the cell range you want to evaluate, and
then click OK.

➤ To find the highest value in a data range
1.
Select the cell immediately below or to the right of the values you want to evaluate.
2. On the Formulas tab, in the Function Library group, click the AutoSum arrow, and
then in the list, click Max.
3. Verify that the cell range displayed in the formula is correct, and then press Enter.

Or
1. Select the cell in which you want to place the maximum value.
2. On the Formulas tab, in the Function Library group, click the More Functions
button, point to Statistical, and then in the list, click MAX.
3. In the Function Arguments box, enter the cell range you want to evaluate, and
then click OK.

image