You can compare two values with the following operators. When two values are compared by using these operators, the result is a logical value, either TRUE or FALSE.
Text concatenation operator
Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of text.
Combine ranges of cells for calculations with the following operators.
Operator Precedence
If you combine several operators in a single formula, Excel performs the operations in the order shown in the following table.
If a formula contains operators with the same precedence—for example, if a formula contains both a multiplication and division operator—Excel evaluates the operators from left to right.
Use of parentheses
To change the order of calculation, enclose in parentheses the part of the formula to be calculated first.
For example, the following formula produces 12 because Excel calculates multiplication before addition.
The formula multiplies 2 by 4 and then adds 4 to the result, and not 24 as you might expect by calculating left to right as one might do.
=4+2*4 (2 times 4 is eight, plus 4 is 12)
In contrast, if you use parentheses to change the syntax, Excel adds 4 and 2 together and then multiplies the result by 4 to produce 24.
=(4+2)*4 (4 plus 2 is 6, times 4 is 24)
In the example that follows, the parentheses around the first part of the formula force Excel to calculate A5+15 first and then divide the result by the sum of the values in cells D5, E5, and F5.
=(A5+15)/SUM(C5:E5)
So if the sum of cell A5 is 85, and cells C5 through E5 are 10, the result would be 10 (85 plus 15 is 100; divided by 10 is 10.
All PARENTHESES are completed FIRST
A NEGATION (such as -9) precedes any of the following operations. The negative value is used in the remaining calculations.
PERCENT's are calculated next and then used in the remaining calculations.
EXPONENTIATION (cubed numbers) are next calculated, returning the value internally before remaining calculations.
MULTIPLICATION is performed AFTER PARENTHETICAL operations and BEFORE any other calculations that follow.
Next is DIVISION, which follows multiplication.
Next is ADDITION, performed AFTER divisions, and before the final operator:
SUBTRACTION, which follows additions.
Because Excel follows a specific order of operations, you can therefore use parentheses to segment your formula.
This is effective because, remember, Excel performs calculations that are in parentheses first.
The operations in multiple sets of parentheses are performed from inside to outside, then that calculation is used for the second set, following the order of operations.
After that, those results are used for the next level, which are performed from left to right.
Here's an example:
=(2+4)*5+5-3
This results in 32 (2 plus 4 is 6, times 5 is 30, plus five is 35, minus 3 which is 32).
Yet removing the parentheses so that you have =2+4*5+5-3 results in 24 (2 plus 4*5 is 22, plus 5 is 27, minus 3 is 24)!
This could result in dramatic errors, to say the least. Moving the location of the parentheses results in other changes:
=2+4*(5+3)-5 results in 29 (5 plus 3 is 8, times 4 is 32, plus 2 is 34, minus 5 is 29)
=2+4*(5+3-5) results in 14 (5 plus 3 minus 5 is 3, times 4 is 12, plus 2 is 14)
=(2+4)*(5+3)-5 results in 43 (2 plus 4 is 6, 5 plus 3 is 8; 6 times 8 is 48, minus 5 is 43
=(2+4)*(5+3-5) results in 18 (2 plus 4 is 6; 5, plus 3 minus 5, is minus 2 (-2); plus 5 which equals 3 (-2+5=3); 3 times 6, which is 18).
Nesting Parentheses
Just to take this one step further, you can also nest calculations by inserting isolated sets of parentheses within other parentheses. For example:
This calculation results in 72. (4 plus 2 minus 1 is 5; 14 divided by 2 is 7; 7 plus 5 is 12; 12 times the sum of 2 plus 4 (which is 6) is 6 times 12 or 72.
Excel, in other words, calculated the inner-most set of parentheses first (4+2-1) which had been placed inside another set of parentheses (+14/2), then held that calculation (the sum of 5 plus 7) while it next calculated the other set of parentheses (2+4), and then multiplied that sum (6) by the first sum (12) to return 72 (6*12=72).
Are your eyes rolling yet?
No comments:
Post a Comment