|
Entering Formulas in Worksheets
The power of a spreadsheet is its ability to use formulas to
represent data in different cells.
Writing Formulas: To write a formula, click the cell
where you want the result of the formula to appear, and type the formula in
the formula bar. You need to begin the formula with a symbol to
signify that you're typing a formula. In Excel, that symbol is an
equal sign (=).
Simple Formulas: Many formulas use the basic
arithmetic operations of addition (+), subtraction (-), multiplication (*),
and division (/). Another useful operation is exponentiation (^), in
which the raised number tells how many times the normal sized number is used
as a factor in multiplication. For instance 22 is 2*2, 23
is 2*2*2. Writing simples formulas is like writing a math problem.
To add 5 and 2, you simply write =5+2 or +5+2 as the formula, depending on
which program you are using. To divide 5 by 2, write the formula =5/2.
When you are done writing the formula, press Enter. That completes the
formula and places the result in the selected cell.
Complex Formulas: You can write more complex formulas
as well. Formulas can include many numbers, such as =1+2+3+4+5.
They can also include more than one mathematical operation. For
instance, suppose you owned a store that sold 50 copies of a game on e month
and 56 copies the next. you want to know by what percentage you r
sales of that game increased. you could find out by writing this
formula: +(56-50)/50. In this formula, you subtract 50 from 56
to find the number of additional games you sold. Then you divide the
result by 50, the number of games sold the first month, to find the
percentage increase. The answer is .12, or 12 percent.
Working With Complex Formulas
Many formulas, like =(56-50)/50, have two or more
operations. How does the program know which one to do first? It
uses the order of evaluation. This rule tells the program to do the most
important operation first. Then it does the others in order, from most
to least important
Ranking Operations: Operations within parentheses are
the most important. Exponentiation comes next, followed by
multiplication, or division, then addition or subtraction. Use the
sentence, "Please excuse my dear Aunt Sally" to remember the order.
The first letter of each word (P-E-M-D-A-S) matches the first letter of each
operation in the right order.
Using Order of Evaluation: Suppose you want to write
an Excel formula to average the numbers 29, 34, and 27. The formula
=(29+34+27)/3 is correct. The parentheses tell the program to add the
three numbers first. The sum, 90, is then divided by 3 to find the
average which is 30. The formula =29+34+27/3 is not correct. In
this case, the program would first divide 27 by 3 because division is the
more important operation. It would then add the result, 9, to 29 and
34 for an answer of 72. Some formulas have more than one operation
with the same importance, such as addition and subtraction. In this
case, those operations are done in the order in which they appear from left
to right.
Rules for Writing Formulas
-
Type the = to begin the formula.
-
Include all numbers and operations.
-
Remember the order of evaluation. (P-E-M-D-A-S)
-
Use parentheses to change the importance of operations
if necessary.
Using References, Not Values
The formulas discussed so far have used values. But formulas can also
use cell references, or cell addresses. For example, suppose you
wanted to multiply cell A1, with a value of 5, and cell B2, with a value of
3. Instead of =5*3, you can write =A1*B2. In fact, it is better
to use cell references for the two reasons listed below.
Avoiding Errors: You might accidentally type the wrong
value and not realize it, as the formula does not always show in the cell.
If you insert a cell reference, however, the formula will always use the
correct value.
Reflecting Changes: A value in a formula never
changes. The formula =5*3 will always produce 15. But what if
the value in cell A1 changes? The formula =5*3 will no longer
correctly multiply A1 and B2. If you use a cell reference, the formula
uses whatever value the cell has. If the cell value changes, so will
the result calculated by the formula. by using cell references, you
make sure that your worksheet remains up-to-date even if data changes.
Using a Function Adding a column
of numbers is a common task, so spreadsheet programs include a function
named SUM, which performs addition. This function is typically shown
on the toolbar by the symbol Σ. Simply
select the cells you want to add and click that symbol. That total
appears in the following row. |
