Entering formulas
Entering formulas:
Note that Excel refers to formulas as functions. There are two ways to enter formulas in Excel, either use one of the functions already programmed in Excel, or enter your own from scratch.
Entering your own formula. To enter your own formula start by typing an equal sign (this tells Excel you are entering a formula)
and then enter the formula using operands and operators. Standard arithmetic operators are listed in Table 1, but many others are available. Operands can either be numbers you enter, or can be cell references. To enter a cell reference in a formula either type it, or click the cell.
Table 1. Arithmetic operators.
Arithmetic
operator Meaning (example)
+ (plus sign) Addition (3+3)
- (minus sign) Subtraction (3-1)
*(asterisk) Multiplication (3*3)
/ (forward slash) Division (3/3)
% (percent sign) Percent (20%)
^ (caret) Exponentiation (3^2)
When evaluating operators in a formula Excel follows the order of operation summarized in Table 2. If a formula contains operators with the same precedence Excel evaluates the operators from left to right. To override operator precedence,use parenthesis. For more information on entering your own formulas check in;Excel Help>Contents> creating and correcting formulas.
Table 2. Operator precedence in Excel.
Precedence Operator Description
1 : (colon) (single space), (comma) Reference operators
2 – Negation (as in –1)
3 % Percent
4 ^ Exponentiation
5 * and / Multiplication and division
6 + and – Addition and subtraction
7 & Connects two strings of text
(concatenation)
8 = < > <= >= <> Comparison
Using Excel’s functions
The easiest way to understand the implementation of Excel functions is by following
a step by step example.
There are three ways to access Excel’s functions, click the function button next
to the function window (a popup window appears), find the function on the Formulas
Ribbon, or type = followed by the function name and a bracket {e.g. =sum( }.
In this first example we will calculate the sum of a series of numbers.
In this second example, we will calculate the standard deviation for the same
numbers used in the sum example
Using auto fill to copy a formula:
Often, you will want to apply the same formula to a series of cells in a column or row. Enter the data as shown in Figure 9 (don’t enter the mean weight). Use Excel’s average function to calculate the average for sample 1 (remember to press enter once the formula is typed). Your spread sheet should look like Figure 9.
Now, grab the cell handle for the cell containing the formula you just entered and drag down four cells. Go back and click in the cell for the mean weight of sample 2. Notice in the formula bar, the function is still AVERAGE, but the cell reference has moved down one row (in my example it would now read B5:E5). This is referred to as relative addressing and it is the default method Excel applies to copying formulas.
Occasionally you will need to make an absolute reference to a cell. To do this, add dollar signs to the cell reference. In my example I need to correct the mean weights of all the samples by multiplying them by the Z factor of 1.0035, which is entered in cell H1. To do this, I enter a formula, as shown in the formula bar of Figure 10, using
H$1 to reference the Z factor. Now, when I auto fill the row number remains #1 and all mean values are multiplied by the value in cell H1. Give it a try; also check what happens when you don’t use the absolute reference.
If you need the column number and the row number to not change, you would enter
$H$1 for the absolute reference. If you needed only the column number to be absolute you would enter $H1.
Figure 10. Using an absolute reference in Excel.Box 2.
Entering formulas.
• All formulas start with an = sign.
Case is not important when entering the formula.
• Cells containing non numerical entrees will be ignored in calculations.
• Excel functions are listed in; Excel Help>Contents>Function Reference.
• The default for auto filling formulas is to use relative addressing