Technology & Engineering
Excel Operators
Excel operators are symbols used to perform operations on data in Excel spreadsheets. They include arithmetic operators such as addition (+), subtraction (-), multiplication (*), and division (/), as well as comparison operators like equal to (=), not equal to (<>), greater than (>), and less than (<). These operators are essential for performing calculations and comparisons within Excel.
Written by Perlego with AI-assistance
Related key terms
1 of 5
8 Key excerpts on "Excel Operators"
eBook - ePub- Lisa A. Bucki(Author)
- 2021(Publication Date)
- Wiley(Publisher)
The old saying in programming of “garbage in, garbage out” refers to the fact that if program code uses a wrong value or an improper sequence, the program will output inaccurate results, throw an error message, or not run at all. The same applies to Excel formulas. You have to be careful to reference the correct cells and use the correct constants, you have to use the right operators, and you also have to be careful about the sequence and structure of the calculation. Order of precedence is one key to calculation sequence and structure. This section explains the correct use of operators and order of precedence in Excel formulas.Reviewing operators in Excel
As you learned earlier or maybe already knew, operators are the symbols that tell Excel what type of calculation to perform. Excel enables you to use a specific subset of the available universe of mathematical operators and symbols. There are four types or categories of operators in Excel:- Arithmetic. These operators perform the types of basic math you learned in school.
- Comparison. These operators compare two values. The result is either TRUE or FALSE, which seems limited, but these operators are typically used as part of a more complex formula.
- Text (Concatenation). This operator, normally used with text, enables you to combine entries or contents to display a longer string of text in a cell. You would use a cell reference in the formula to refer to a cell holding text as needed. One trick to keep in mind is that you often have to create spaces manually by including " " (quote space quote) among the parts being concatenated in the formula.
- Reference. These operators enable you to build normal and special range references.
Table 4.1 introduces the arithmetic, comparison, and text (concatenation) operators that you can use in Excel, along with a brief example of each. The reference operators require wordier explanations, so I cover them right after the table.Entering the four most common operators gives the 10‐key keypad another chance to shine. It includes the / (division), * (multiplication), ‐ (subtraction), and + (addition) keys for easier formula entry. If you're a heavy Excel user and your laptop doesn't have a 10‐key numeric keypad, you can get a USB version for $20 or so.
No longer available |Learn more- Michael Alexander, Dick Kusleika(Authors)
- 2022(Publication Date)
- Wiley(Publisher)
Again, note that every formula begins with an equal sign ( = ). The initial equal sign allows Excel to distinguish a formula from plain text. Chapter 9: Introducing Formulas and Functions 213 9 Using operators in formulas Excel formulas support a variety of operators. Operators are symbols that indicate what math-ematical (or logical) operation you want the formula to perform. Table 9.1 lists the operators that Excel recognizes. In addition to these, Excel has many built-in functions that enable you to perform additional calculations. You can, of course, use as many operators as you need to perform the desired calculation. Here are some examples of formulas that use various operators: Formula What It Does = Part- & 23A Joins (concatenates) the two text strings to produce Part-23A . =A1&A2 Concatenates the contents of cell A1 with cell A2. Concatenation works with values as well as text. If cell A1 contains 123 and cell A2 contains 456 , this formula would return the text 123456 . Note that the result of concate-nation is always formatted as text. =6^3 Raises 6 to the third power ( 216 ). =216^(1/3) Raises 216 to the power of 1/3. This is mathematically equivalent to calculating the cube root of 216, which is 6 . TABLE 9.1 Operators Used in Formulas Operator Name + Addition − Subtraction * Multiplication / Division ^ Exponentiation & Concatenation = Logical comparison (equal to) > Logical comparison (greater than) < Logical comparison (less than) >= Logical comparison (greater than or equal to) <= Logical comparison (less than or equal to) <> Logical comparison (not equal to) Part II: Working with Formulas and Functions 214 Formula What It Does =A1
eBook - PDF- Paul McFedries(Author)
- 2022(Publication Date)
- Visual(Publisher)
Because Excel worksheets primarily deal with numeric data, arithmetic formulas are by far the most common formulas used in worksheet calculations. The following table lists the seven arithmetic operators that you can use to construct arithmetic formulas: Operator Name Example Result + Addition =10 + 5 15 – Subtraction =10 – 5 5 – Negation =–10 –10 * Multiplication =10 * 5 50 / Division =10 / 5 2 % Percentage =10% 0.1 ^ Exponentiation =10 ^ 5 100000 Comparison Formulas A comparison formula combines numeric operands — numeric constants, functions that return numeric results, and fields or items that contain numeric values — with special operators to compare one operand with another. A comparison formula always returns a logical result. This means that if the comparison is true, then the formula returns the value TRUE, which is equivalent to the numeric value 1; if the comparison is false, then the formula returns the value FALSE, which is equivalent to the numeric value 0. The following table lists the six operators that you can use to construct comparison formulas: Operator Name Example Result = Equal to =10 = 5 FALSE < Less than =10 < 5 FALSE <= Less than or equal to =10 <= 5 FALSE > Greater than =10 > 5 TRUE >= Greater than or equal to =10 >= 5 TRUE <> Not equal to =10 <> 5 TRUE Operator Precedence Most of your formulas include multiple operands and operators. In many cases, the order in which Excel performs the calculations is crucial. For example, consider the formula =3 + 5 ^ 2. If you calculate from left to right, the answer you get is 64 (3 + 5 equals 8, and 8 ^ 2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5 ^ 2 equals 25, and 3 + 25 equals 28). Therefore, a single formula can produce multiple answers, depending on the order in which you perform the calculations.
eBook - ePub- Michael Alexander, Richard Kusleika(Authors)
- 2014(Publication Date)
- Wiley(Publisher)
Double-click the formula cell: You can edit the formula directly in the cell it’s in by double-clicking the cell. Double-clicking the cell gets you into Edit mode, where you can edit the formula as needed.- Press F2: Select the cell that contains your formula and then press F2 on your keyboard to get into Edit mode. As stated previously, you can then edit the formula as needed.
Using Formula Operators
As mentioned earlier in this chapter, you can use symbols known as operators to define the operation your formula will accomplish. Some of these operators are mathematical operators that simply add, subtract, and multiply. Other operators allow you to perform more complex actions such as comparing values. For example, you can determine whether an employee has met his or her quota by using a comparison operator to see if actual sales are greater than or equal to a predetermined quota.Table 1-1 lists the operators you can use in your Excel formulas.Table 1-1: Operators for Excel FormulasOperatorWhat It Does+The plus symbol adds two or more numeric values. -The hyphen symbol subtracts two or more numeric values. /The forward slash symbol divides two or more numeric values. *The asterisk symbol divides two or more numeric values. %The percent symbol indicates a numeric percent. Entering a percent sign after a whole number divides the number by 100 and formats the cell as a percentage. 
eBook - PDFComputer Practice N5 SB
TVET FIRST
- S Sasti, D Sasti(Authors)
- 2021(Publication Date)
- Macmillan(Publisher)
Formulae are used to perform these calculations, which are used to generate new data or to help you to analyse current data. Table 3.3 shows symbols in MS Excel that perform simple mathematical operations. MS Excel enables users to perform any mathematical calculation, which is what makes the program so powerful and useful. Table 3.3: Symbols used for mathematical operations in a formula Symbol Mathematical operation Example Explanation of the formula + Addition = B1 + 20 We are adding 20 to the value stored in cell B1 – Subtraction = C3 – 5 We are subtracting 5 from the value stored in cell C3 / Division = D6 / 3 We are dividing the value stored in cell D6 by 3 * Multiplication = B1 * 4 We are multiplying the value stored in cell B1 by 4 ^ Exponents = E1 ^ 2 We are raising the value stored in cell E1 to the power of 2 % Percentages = F1 * 15% We are calculating 15 percent of the value stored in cell F1 Creating formulae You have to start your formula with the ‘=’ symbol followed by the mathematical operation for your formula. This is how MS Excel knows that you want to use a formula. You can refer to the cell contents or use constants in your calculations. • Click on the cell to display the result of the calculation. • Click in the Formula bar. • Type your formula, and press the Enter key. • MS Excel will calculate and display the result. • In Figure 3.62, the constant number 3 is added to the contents in the cell A2. 180 min Mathematical operation Contents from cell A2 to be used in the formula Constant value Start of a formula Figure 3.62: A formula explained 152 Topic 1: Computing concepts and application skills TVET FIRST Order of precedence in MS Excel formulae Excel uses the order of precedence to calculate a formula when it contains many mathematical operations: • First, evaluate items in parentheses or brackets. • Then exponents. • Then multiplication and division.
Available until 6 Oct |Learn more- Paul McFedries, Greg Harvey(Authors)
- 2021(Publication Date)
- For Dummies(Publisher)
302 BOOK 3 Formulas and Functions More about operators Most of the time, you’ll rely on the arithmetic operators when building formulas in your worksheets that don’t require functions because these operators perform computations between the numbers in the various cell references and produce new mathematical results. The comparison operators, on the other hand, produce only the logical value TRUE or the logical value FALSE, depending on the result of the comparison. For exam -ple, say that you enter the following formula in cell A10: =B10 <> C10 If B10 contains the number 15 and C10 contains the number 20, the formula in A10 returns the logical value TRUE. If, however, both cell B10 and C10 contain the value 12, the formula returns the logical value FALSE. The single text operator (the so-called ampersand) is used in formulas to join two or more text entries (an operation with the highfalutin’ name concatenation ). For example, suppose that you enter the following formula in cell C2: =A2 & B2 If cell A2 contains John and cell B2 contains Smith , the formula returns the new (squashed together) text entry, JohnSmith . To have the formula insert a space between the first and last names, include a space as part of the concatenation as follows: =A2 & & B2 You most often use the comparison operators with the IF function when building more complex formulas that perform one type of operation when the IF condition is TRUE and another when it is FALSE. You use the concatenating operator (&) when you need to join text entries that come to you entered in separate cells but that need to be entered in single cells (like the first and last names in separate col -umns). See Book 3, Chapter 2 for more on logical formulas, and Book 3, Chapter 6 for more on text formulas. Order of operator precedence When you build a formula that combines different computational operators, Excel follows the set order of operator precedence, as shown in Table 1-2.
eBook - PDF- Timothy Mayes(Author)
- 2020(Publication Date)
- Cengage Learning EMEA(Publisher)
Mathematical Operators Aside from division, which we have already seen, there are four additional primary mathematical operations: addition, subtraction, multiplication, and exponentiation. Table 1-3 summarizes the five basic operations and the result that you should get from entering the example formula into cell B8. Cut Table 1-3 Mathematical Operations Operation Key Formula Result in B8 Addition 1 5B5 1 B6 165,083 Subtraction 2 5B5 2 B6 86,603 Multiplication * 5B5*B7 39,240 Division / 5B6/B7 125,843 Exponentiation ^ 515^2 225 Parentheses and the Order of Operations Using the mathematical operators provided by Excel is straightforward in most instances. However, there are times when it gets a bit complicated. For example, let’s calculate the rates of growth of Microsoft’s sales and net income. To calculate the growth rates we usually want the compound annual growth rate (CAGR, which Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. CHAPTER 1 Introduction to Excel 20 is also known as the geometric mean growth rate) rather than the arithmetic average growth rate. The equation for the CAGR of sales is: CAGR 5 1 N 2 12 Å Sales N Sales 0 2 1 5 a Sales N Sales 0 b 1 (N 2 1) 2 1 (1-1) where N is the count of the numbers in the series, Sales 0 is the first number in the series (2014 sales in our example), and Sales N is the last number in the series (2019 sales). Translating this equation into Excel is not as simple as it may at first appear. Doing this correctly requires knowledge of operator precedence.
- Shawna Lockhart, Eric Tilleson(Authors)
- 2018(Publication Date)
- SDC Publications(Publisher)
Operator Meaning < Less than <= Less than or equal to > Greater than >= Greater than or equal to == Equals ~= Does not equal Note: MATLAB is the name of both the programming environment (the MATLAB program itself) and the programming language used in that environment. Both uses are fairly interchangeable in conversation, if you ever have those sorts of conversations. Tip: Operators also have function forms; for instance plus(3,4), which does the same thing as 3 + 4. Their reason for being is beyond our scope and we won’t be using them. See the MATLAB help section on operators if you’re curious to learn more. 16 Tutorial 2 PROGRAMMING BASICS: OPERATORS & VARIABLES Not All Equals are Equal A quick word about the equals sign operators. MATLAB has more than one operator that uses equals signs (=) and they act differently. If you have done some programming, you are probably used to the idea that “=” is not used for comparison, but rather to assign the value from the right side of the equals sign to the variable on the left side. For example: index = index + 1 is a perfectly legitimate assignment statement (for the new value, add 1 to the old value), but the two sides are certainly not mathematically equal. Type the following lines in the Command Window and observe the results: >> x = 3 >> x == 3 The results display in the Command Window similar to Figure 2.1. Figure 2.1 The command x = 3 assigns the value 3 to the variable x. The command x == 3 asks “Is x equal to 3?” and returns a value of 1, which means “true” since we just set it equal to 3. We’ll talk about this again, but the accidental use of = when you meant == is a common coding error. Logical Operators Here are the logical operators. These operators are used on logical values and result in a logical value. Operator Meaning && and || or ~ not Logical operators might be new to you. Logical operators test for true or false and report either 1 (true) or 0 (false).
Index pages curate the most relevant extracts from our library of academic textbooks. They’ve been created using an in-house natural language model (NLM), each adding context and meaning to key research topics.
