Understanding the Components of Formulas

When you need to perform a custom calculation in a cell, use a formula rather than a function. All you need to do is type in a simple formula using the appropriate calculation operators, such as + signs for addition and – signs for subtraction. In this section, you'll meet the calculation operators, try using them in a worksheet, and learn the order in which Excel applies them—and how to change that order.

Meeting Excel's Calculation Operators

To perform calculations in Excel, you need to know the operators for the different operations—addition, division, comparison, and so on. Table 5–1 explains the full set of calculation operators you can use in your formulas in Excel.

Table 5–1 Calculation Operators You Can Use in Excel

Calculation Operator Operation Explanation or Example
Arithmetic Operators
+ Addition =1+2 adds 2 to 1.
− Subtraction =1−2 subtracts 2 from 1.
* Multiplication =2*2 multiplies 2 by 2.
/ Division =A1/4 divides the value in cell A1 by 4.
% Percentage =B1% returns the value in cell B2 expressed as a percentage. Excel displays the value as a decimal unless you format the cell with the Percentage style.
^ Exponentiation =B1^2 raises the value in cell B1 to the power 2.
Comparison Operators
= Equal to =B2=15000 returns TRUE if cell B2 contains the value 15000. Otherwise, it returns FALSE.
<> Not equal to =B2<>15000 returns TRUE if cell B2 does not contain the value 15000. Otherwise, it returns FALSE.
> Greater than =B2>15000 returns TRUE if cell B2 contains a value greater than 15000. Otherwise, it returns FALSE.
>= Greater than or equal to =B2>=15000 returns TRUE if cell B2 contains a value greater than or equal to 15000. Otherwise, it returns FALSE.
< Less than =B2<15000 returns TRUE if cell B2 contains a value less than 15000. Otherwise, it returns FALSE.
<= Less than or equal to =B2<=15000 returns TRUE if cell B2 contains a value less than or equal to 15000. Otherwise, it returns FALSE.
Reference Operators
[cell reference]:[cell reference] The range of cells between the two cell references A1:G5 returns the range of cells whose upper-left cell is cell A1 and whose lower-right cell is cell G5.
[cell reference],[cell reference] The range of cells listed A1,C3,E5 returns three cells: A1, C3, and E5.
[cell or range reference][space][cell or range reference] The range (or cell) that appears in both cells or ranges given =A7:G10 B10:B12 returns the cell B10, because this is the only cell that appears in both the ranges given. If more than one cell appears in the range, this returns a #VALUE! error.
Text Operator
& Concatenation (joining values as text) =A1&B1 returns the values from cells A1 and B1 joined together as a text string. For example, if A1 contains “New York ” (including a trailing space) and B1 contains “Sales”, this formula returns “New York Sales.” If A1 contains 100 and B1 contains 50, this formula returns 10050.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.35.255