Excel Examples

Mary Griffin

Example #1

Average Grades

=AVERAGE(B2:F2)

Example #2

Summing Grades

=SUM(B2:F2)
Example #3

Weighted Grade Book

=SUM(B2:F2)/5*0.5+G2*0.25+H2*0.25
 

Enter a formula

For information about how formulas calculate values, click . 
  1. Click the cell in which you want to enter the formula.
  2. Type = (an equal sign). 
If you click Edit FormulaEdit Formula buttonor Paste Function, Microsoft Excel inserts an equal sign for you. 
  1. Enter the formula.
  2. Press ENTER. 
Tips
  • You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER.
  • You can also enter a formula into a range of cells by copying a formula from another cell. 

Move or copy a formula

When you move a formula, the cell references within the formula do not change. When you copy a formula, absolute cell references do not change; relative cell references will change. For more information about absolute and relative references, click . 
  1. Select the cell that contains the formula you want to move or copy.
  2. Point to the border of the selection.
  3. To move the cell, drag the selection to the upper-left cell of the paste area. Microsoft Excel replaces any existing data in the paste area. 
To copy the cell, hold down CTRL as you drag. 
Tip   You can also copy formulas into adjacent cells by using the fill handle. Select the cell that contains the formula, and then drag the fill handle over the range you want to fill. 

 

Mathematical and relational operators

In an = (Formula) field, you can use any combination of values and the following mathematical and relational operators.

 
Operator 
Description
+
Addition
–
Subtraction
*
Multiplication
/
Division
%
Percentage
^
Powers and roots
=
Equal to
<
Less than
< =
Less than or equal to
>
Greater than
> =
Greater than or equal to
< >
Not equal to

Functions used by the = (Formula) field

The = (Formula) field can use values returned by the following functions. Functions with empty parentheses can accept any number of arguments separated by commas (,) or semicolons (;). Arguments can be numbers, formulas, or bookmark names.
Note   The following functions can accept references to table cells as arguments: AVERAGE(), COUNT(), MAX(), MIN(), PRODUCT(), and SUM().

 
Function
Returns
ABS(x)
The positive value of a number or formula, regardless of its actual positive or negative value.
AND( x,y)
The value 1 if the logical expressions x and y are both true, or the value 0 (zero) if either expression is false.
AVERAGE( )
The average of a list of values.
COUNT( )
The number of items in a list.
DEFINED(x)
The value 1 (true) if the expression x is valid, or the value 0 (false) if the expression cannot be computed.
FALSE
0 (zero).
INT(x)
The numbers to the left of the decimal place in the value or formula x.
MIN( )
The smallest value in a list.
MAX( )
The largest value in a list.
MOD(x,y)
The remainder that results from dividing the value x by the value y a whole number of times.
NOT(x)
The value 0 (zero) (false) if the logical expression x is true, or the value 1 (true) if the expression is false.
OR(x,y)
The value 1 (true) if either or both logical expressions x and y are true, or the value 0 (zero) (false) if both expressions are false.
PRODUCT( )
The result of multiplying a list of values. For example, the function


{ = PRODUCT (1,3,7,9) } returns the value 189.
ROUND(x,y)
The value of x rounded to the specified number of decimal places y; x can be either a number or the result of a formula.
SIGN(x)
The value 1 if x is a positive value, or the value –1 if x is a negative value.
SUM( )
The sum of a list of values or formulas.
TRUE
1.

Referencing cells in a table

When you perform calculations in a table, you reference table cells as A1, A2, B1, B2, and so on, with the letter representing a column and the number representing a row. Cell references in Microsoft Word, unlike those in Microsoft Excel, are always absolute references and are not shown with dollar signs. For example, referring to a cell as A1 in Word is the same as referring to a cell as $A$1 in Excel.
Table illustrating cell references
Reference individual cells   To reference cells in formulas, use a comma to separate references to individual cells and a colon to separate the first and last cells in a designated range, as shown in the following examples.

 
To average these cells
Type
Table with cell range selected
=average(b:b) or =average(b1:b3)
Table with cell range selected
=average(a1:b2)
Table with cell range selected
=average(a1:c2) or =average(1:1,2:2)
Table with cell range selected
=average(a1,a3,c2)

Reference an entire row or column   You can reference an entire row or column in a calculation in the following ways: 

  • Use a range that includes only the letter or number that represents it — for example, 1:1 to reference the first row in the table. This designation allows the calculation to automatically include all the cells in the row if you decide to add other cells later.
  • Use a range that includes the specific cells — for example, a1:a3 to reference a column with three rows. This designation allows the calculation to include only those particular cells. If you add other cells later and you want the calculation to include them, you need to edit the calculation. 
Reference cells in another table   To reference cells in another table, or to reference a cell from outside a table, identify the table with a bookmark. For example, the field { =average(Table2 b:b) } averages column B in the table marked by the bookmark Table2.