Excel Quickguide Part. V Create a Formula on Excel 2010 (1/2)

Excel is like a calculator that you can program the way you want. You can use Excel to find totals for a column or row of numbers, but can also calculate a mortgage payment and solve engineering problems. Excel does such calculations by using formulas in cells. A formula performs calculations or other actions on the data in your worksheet.

i. The parts of a formula

Create a Formula on Excel 2010

1. Functions: The PI() function returns the value of pi: 3.142
2. References : A2 returns the value in cell A2
3. Constants: Numbers or text values entered directly into a formula, such as 2.
4. Operators: The (^) operator raises a number to a power, and the * (asterisk) operator multiplies numbers.

ii. Using constants in formulas

A constant in a formula refers to numbers that are not calculated. Which means it will always stay the same. This includes for example the date (Feb 14th 2012), the number 207 and the text “Quarterly Earnings”.

iii. Using calculation operators in formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using parentheses.

Types of Operators

Arithmetic Operator

Create a Formula on Excel 2010

Comparison Operator

Create a Formula on Excel 2010

Text Concentration Operator

Create a Formula on Excel 2010

Reference Operator

Create a Formula on Excel 2010

iv. The order in which Excel performs operations in formulas

Just like math computation, the order in which a calculation is performed can affect the return value of the formula.

Calculation order

Formulas calculate values in specific order. A formula in Excel always begins with an equal sign. Excel interprets the characters that follow the equal sign as a formula. Following the equal sign are the elements to be calculated, such as constants or cell references. These are separated by calculation operators. Excel calculates the formula from the left to right, according to a specific order for each operator in the formula.

Operator precedence

If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table.

Create a Formula on Excel 2010

Use of Parentheses

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first. For example, the following formula produces 21 because Excel performs multiplication before addition.

=6+5*3

But if you use parentheses to change the syntax, Excel adds 6 and 5 together and then multiplies the result by 3 to produce 33

=(6+5)*3

, , , , ,

No comments yet.

Leave a Reply