﻿ Microsoft Excel 2007 tutorial: formulas

# Microsoft Excel 2007 tutorial: formulas

## Formulas

### I. Formulas

Formulas are equations that start with equal sign (=) and perform calculation on values in your worksheet. For instance, the following formula subtract 12 by 5 and then multiplies 3 to the result.

=(12-5)*3

A formula can contain any or all of the following: functions, references, operators, and constants.

1. References: A2 and B2 return the values in cell A2 and B2.

2. Constants: Numbers or text values enter directly into a formula, such as 2.

3. Operators: The + (plus sign) operator adds values, and the * (asterisk) operator multiplies.

### II. Using calculation operators in formula

You use operators to perform calculation on the elements of a formula. There are four different types of calculation operators: arithmetic, comparison, text concatenation, and reference.

#### II.1. Arithmetic operators

The following arithmetic operators use to perform basic mathematic operation such as addition, subtraction, multiplication, or division; combine number; and produce numeric result.

Arithmetic operatorMeaning Example
- (minus sign)Subtraction
Negation
5-3
-2
* (asterisk)Multiplication 5*5
/ (Forward slash)Division 5/2
% (percent sign)Percent 30%
^ (caret)Exponentiation 5^2
##### II.1.1. Plus sign (+)

Plus sign (+) is used to add two or more numeric values. For example, add value1 and value2.

##### II.1.2. Minus sign (-)

Minus sign (-) is used to subtract two or more numeric values or represent negative values. For instance, subtract value1 by value2.

##### II.1.3. Asterisk (*)

Asterisk is used to multiply numeric values. For example, multiply value1 by value2 and then subtract value3 to the result.

##### II.1.4.  Forward slash(/)

You use forward slash to divide numeric values. For example, divide value1 by value2 and then multiply value3 to the result.

##### II.1.5. Percent sign (%)

You use percent sign to display the value of the cell as percentage. For example, 20%, 35%,etc. If you type 0.3 in the cell, when you click percent sign in the Number group, Excel displays the value as 30%. You can also use shortcut key to display the value as percentage by pressing ctrl+ shift+ %.

##### II.1.6. Caret (^)

You use caret operator to raise a number to a power. For example, 3^4. In Excel, to calculate exponentiation, you use Caret operator or POWER function. For instance, you want to calculate 4^5. You need to do the following:

1. Open Microsoft Office Excel.

2. Click on any cell.

3. Write the following formula:

=4^5

Alternatively, you can write the formula as below:

=POWER(4,5)

#### II.2.Comparison operators

You can use the following operators to compare two values. When two values are compared by using these operators, the result is a logical value either TRUE or FALSE.

Comparison operatorMeaning Example
=  (equal sign)Equal to A1=B1
> (greater than sign)Greater thanA1>B1
< (less than sign)Less than A1<B1
>= (greater than or equal to sign) Greater than or equal to A1>=B1
<= (less than or equal to sign) Less than or equal to A1<=B1
<> (not equal to sign)Not equal to A1<>B1

#### The following examples demonstrate each comparison operator.

Ex1. If you type m or M in the cell A1, the cell B1 displays "Male". Otherwise the cell B1 displays blank.

To settle the example, you need to write the following formula in the cell B1.

=If(A1="m","Male","")

Ex2: If values in the cell A1 is greater than 20, the cell B1 displays "Invalid value". Otherwise the cell B1 displays blank. To do this task, write the following formula in the cell B1.

=If(A1>20,"Invalid value"," ")

Ex3: If values in the cell A1 is less than 20, the cell B1 displays "Valid value"; on the contrary, the cell B1 displays "Invalid value". To do this task, write the following formula in the cell B1.

=If(A1<20,"Valid value","Invalid value")

Ex4: The cell A1 can contain only values that start from 10 upward. To solve this task, write the following formula in any cell on the worksheet except A1:

=A1>=10

Ex5: The cell A1 can contain only values that start from 10 downward. To solve this task, write the following formula in any cell on the worksheet except A1:

=A1<=10

Ex6: The cell A1 must have data. If this cell is blank, a message "The cell cannot be blank" displays in the cell B1. To do this task, write the following formula in the cell B1:

=If(A1<>"","","The cell cannot be blank")

#### II.3.Text concatenate operator

To join two or more text strings to make a single piece of text, you use ampersand (&).

Text operatorMeaning Example
& (ampersand)Joins, or connects, or concatenates, two values to produce a single text value. "Micro"&"soft"

For example, you have a list of name that contains first names and last names as the figure one; you want to concatenate those first names and last names as full names as the figure two.

To do this task, write the following formula in a cell under the full name column:

=A1&" "&B1

Note: In Excel, you can also use CONCATENATE function to join several text strings into one text string.

#### II.4.Reference operators

The following operators are used to combine ranges of cells for calculation.

Reference operatorsMeaning Example
: (colon)Makes one reference to all the cells between two references, including the two references call range operator. A1:A6
, (comma)Combines multiple references into one reference call union operator. Max(B1:B5,C1:C5)
(space)Produces on reference to cells common to the two references call intersection operator. A1:A6 D1:D5

For example, you have a list of students as the figure below:

1. Calculates average score in Ms. Excel.

2. Calculates total score of stu004.

To do this task, do the following:

For calculation average score in Excel, write the following formula in a cell that you want it to display an average score:

=Average(D3:D8)

For calculation total score of stu004, write the following formula in a cell you want it to display a total score:

=SUM(D6:F6)