1. Introducción
2.3. Parásitos gastrointestinales
2.3.3 Protozoarios
2.3.3.2 Toxoplasma gondii
Learning Objectives
After completing this topic, you should be able to
create and insert formulas
use names in formulas
1. Creating and inserting formulas
Excel 2010 makes it easy to use formulas to perform calculations on data – for example, to add or subtract values in particular cells, or to find percentages or averages. Excel 2010 also lets you name particular cells or ranges of cells in a worksheet, and then to use these names directly in formulas. This can make formulas easier to create and interpret.
Graphic
An example of a formula is =C4+C5. You can assign a name to a cell by selecting the relevant cell and typing its name in the cell reference field. An example of a formula in which a cell has been assigned a name, is =Contract_T+F5. After you type a formula in the Formula bar, the selected cell displays the result of the formula.
In Excel, you can enter a formula in a worksheet cell by typing it directly into the cell and then pressing the Enter key. You always include an equals sign in front of a formula, to tell Excel that your entry must be interpreted as a formula.
Graphic
A worksheet displaying sales figures for various products displays. The formula entered in I20, at the end of a row of sales figures, is =E20+F20+G20+H20.
After you press Enter, the result of the formula displays in the cell, and the cell below it becomes active.
Graphic
The result is $27,012.
If you select the cell, the formula displays in the Formula Bar.
Graphic
180
In the Excel interface, the Formula Bar displays beneath the Quick Access Toolbar and the Ribbon, and just above the open worksheet.
A worksheet records the sales made by each of four sales agents in various categories. You want to enter a formula in cell I10 to calculate the total sales of cell phone contracts. To do this, the formula must add the sales values in cells E10, F10, G10, and H10.
Graphic
Cells E10, F10, G10, and H10 contain the dollar values of sales of cell phone contracts made by each of the four salespersons.
You select cell I10 and type an equals sign into the cell to indicate that you're creating a formula.
Next you reference the first cell in the range you want to add. To do this, you can either type the cell reference or select the cell. In this case, you type E10. Excel then draws a blue border around cell E10.
You need the formula you're creating to add the values in cells E10 to H10 so you type the plus (+) operator, which is the operator that represents addition, after the E10 cell reference. You then select cells F10, and then repeat for G10, and H10.
As you add additional cell references to the formula, Excel highlights each relevant cell using a border of a different color.
Note
Subsequent selections are colored green, purple, red, light green, orange, and pink. After that, the cycle starts with blue again.
Finally you press the Enter key. The result of the formula calculation shows in the cell you selected originally, and the cell below it becomes active.
Graphic
A dollar value displays in cell I10 and cell I11 is selected.
You can also insert a formula in a cell by selecting the cell and then typing the formula directly into the Formula Bar.
Graphic
181
For example, the formula I10+I11+I14+I15+I17+I18+I19+I20 has been typed in the Formula Bar.
Each cell reference displays in a different color, which correspond to the borders of the relevant cells in the worksheet.
After you press Enter, the cell you originally selected remains active.
Graphic
The selected cell displays the total sales figure for different sales people. And the colored borders no longer display.
Next you want to enter a formula in cell E25. The formula must calculate Bruce Oprisan's commission, which is a fixed percentage of his total sales. To do this, it must multiply the total sales in cell E22 by the fixed percentage, which is specified in cell E24.
Graphic
Cell E24 contains the value 5%.
You select cell E25 and type an equals sign in the Formula Bar. The Formula Bar becomes active and Enter and Cancel buttons display.
Next you type E22 after the = sign. Excel then draws a blue border around cell E22 in the worksheet.
The formula you're creating must multiply the value in cell E22 by the value in cell E24. The first cell reference is displayed in blue and its cell is outlined in a blue border with square corners while the second cell reference is displayed in green with its cell outlined in a green border with square corners.
Graphic
The completed entry in the Formula Bar is =E22*E24.
Note
Cells for named ranges are similarly outlined but without square corners.
Finally, you can either press the Enter key on your keyboard or click the Enter button on the Formula Bar.
Cell E25, which remains selected, displays the result of the calculation, while the Formula Bar displays the formula.
182 Graphic
Cell E25 displays a dollar value.
Try It
Now you try using the Formula Bar to insert a formula in cell H12 that calculates Tonya Chavis's total cell phone sales. Use uppercase when typing the cell references.
In the open worksheet, the sales figures for Tonya Chavis appear in cells H10 and H11. They are
$37,260 and $7,846 respectively.
To complete the task
1. Select cell H12 in the worksheet
2. Type =H10+H11 in the Formula Bar and click the Enter button
You've inserted a formula using the Formula Bar in Excel 2010.
Now say you want to use the same formula to calculate the total cell phone sales for other employees. You can use the AutoFill feature to copy the formula from cell H12 into cells G12, F12, and E12.
Graphic
With H12 selected, the Formula Bar displays =H10+H11, and H12 displays a dollar value. The cells G12, F12, and E12 are blank.
You hover your cursor over the bottom right corner of cell H12 until it changes to a Fill handle.
You then use your mouse to drag the Fill handle across from cell H12 to cell E12.
Excel 2010 automatically adjusts the cell references in formulas based on which cells you enter them in. For instance, if you select cell E12, the formula =E10+E11 displays in the Formula Bar, although =H10+H11 is the formula you copied from cell H12.
Excel then places the formula in the selected cells, which display the results.
The adjustments Excel makes are possible because Excel uses relative referencing by default.
When you enter a formula, you enter specific cell references. Behind the scenes, Excel translates those references into positions relative to the result cell.
183
Most of the time, this is what you would want. However, there may be times when you need to stop Excel from updating cell references when you copy formulas. Sometimes relative
referencing can result in a copied formula that generates zero results.
In these situations, you can use absolute cell references. To create an absolute cell reference, you include a dollar sign before each of a cell's coordinates.
Graphic
For example, H12, which displays Tonya Chavis's cell phone sales' total, contains the formula
=H11*$E$24.
Say you want to calculate the percentage of total sales made by each of the four sales agents.
The same total sales figure, which is in cell I22, must be used in each of the calculations. So you need to divide each agent's sales by the same total sales figure from cell I22.
To start, you enter the formula for calculating the proportion of total sales Bruce Oprisan made in cell E24. In the formula, you use an absolute reference to cell I22.
Graphic
The formula in cell E24 is =E22/$I$22.
Now when you use Autofill to copy the formula in cell E24 to cells F24, G24, and H24, the absolute reference stays the same.
Try It
Now you try using the Formula Bar to create a formula that uses an absolute reference. The formula must calculate the proportion of Chadrick Powell's total sales that can be attributed to contract sales.
So it must divide the contract sales figure in cell F10 by the total sales figure in cell F22. Use uppercase when typing the cell references.
The bottom part of the December worksheet displays and contains sales figures.
To complete the task
1. Select cell F24 in the worksheet
2. Type =F10/$F$22 in the Formula Bar and click the Enter button
You've inserted a formula containing an absolute reference in Excel 2010.
Cell F24 contains the formula =F10/$F$22. You now copy this formula into cell F25 to calculate the proportion of Chadrick Powell's total sales that can be attributed to his prepaid sales. When you do this, the absolute cell reference in the formula remains unchanged.
184 Graphic
The formula copied into cell F25 is =F11/$F$22.
In Excel 2010 formulas, you can include references to cells in other worksheets. This type of reference is called a 3-D reference. It consists of the name of a worksheet, an exclamation mark, and then the coordinates of the relevant cell.
Graphic
The example in the Formula Bar is =Income!H8-Expenses!D9.
Suppose you want to subtract the total expenses for a conference from the total income it generated and the relevant values are in separate worksheets. You want the result to display in cell D61 of the Expenses worksheet, so you start by selecting this cell and typing an equals sign.
You click the Income tab to open the Income worksheet and then select cell H31, which contains the total income value. The 3-D reference Income!H31 is then automatically added to the formula you started typing in cell D61 of the Expenses worksheet.
Graphic
The Formula Bar displays the entry =Income!H31.
Next you return to the Expenses worksheet. You add a minus sign to the formula and type Expenses! to begin creating a 3-D reference to a cell in the Expenses worksheet.
You select cell D60, which contains the total actual expenses figure, to complete the formula.
You press Enter and the result of the formula displays in cell D61.
You can also add a 3-D reference simply by typing it into a formula, in a cell or in the Formula Bar.
Graphic
For example, =Income!H displays in the Formula Bar.
Try It
Now you practice using the Formula Bar to insert a formula that uses 3-D references. The formula must add the value in cell K19 of the active June worksheet to the value in cell K19 in the December
185
worksheet. Both values show the total bonus values for all sales agents. Use uppercase when typing the cell references.
To complete the task
1. Type =K19+December!K19 in the Formula Bar and click the Enter button
You've inserted a formula containing a value in another worksheet.
Question
A worksheet details the sales each of the four sales agents for a telecommunications company have made. You want to insert a formula that will calculate the total sales using an absolute reference for cells E22 and F22.
Which formula would you use?
A worksheet containing sales data displays. Cell E22 contains the total sales made by Bruce Oprisan and cell F22 contains the total sales made by Chadrick Powell. Cell G22 contains the total sales made by Heather Denfield and cell H22 contains the total sales made by Tonya Chavis.
Options:
1. =E!22+F!22+G22+H22 2. $E$22+$F$22+G22+H22 3. =$E$22+$F$22+G22+H22
Answer
Option 1: Incorrect. The correct operator for an absolute cell reference is a dollar sign before each cell coordinate. The correct formula is =$E$22+$F$22+G22+H22.
Option 2: Incorrect. A formula must always begin with an equals sign. The correct formula is
=$E$22+$F$22+G22+H22.
Option 3: Correct. This formula calculates the total sales by adding the sales made by all four employees.
Correct answer(s):
3. =$E$22+$F$22+G22+H22