Operators and Operands

Types of Values   Introduction An expression is a combination of values and symbols used to produce a new value. To do this, there are different types of values involved in an expression. They are: A string is an empty space, a character, a word, or group of words considered as an entity. Any combination of letters or words you can think is primarily a string. A Boolean value is one that can be expressed in only one of two values. In Microsoft Access, a Boolean value is expressed as either TRUE or as FALSE. A FALSE value also has the value of 0. In this case, any other numeric value is considered TRUE. Boolean values are mostly used in comparisons. An integer is a natural number that displays without a decimal place. Examples of integers are 18, 16763, and 1450. If the number is very large, it can be considered as a Long integer. A byte is a small integer that ranges from 0 to 255. A double, also called double precision, value is a number that can display a decimal portion, using the character set as the decimal separator in Control Panel. For US English, that character would be the period. The expression “double-precision” means that this number provides a high level of precision. If you are dealing with a decimal number but precision is not important, then the number can be represented as a Single. Valid decimal numbers are 12.55, 3.14159 or 9.80336 A date is a numeric value that counts the number of days that have elapsed from a certain point of reference. How a date displays in a field is based on some conventions set by Microsoft Excel, Microsoft Windows, or you. There are also rules you must respect. Examples of dates are 28/06/1998, 10-8-82, January 10, 1865 A time is a numeric value that counts the number of seconds that have elapsed since midnight of a certain day. The time also displays following some conventions set in Control Panel, by Microsoft Excel, or the person who created the spreadsheet. Examples of time values are 10:42 AM and 18:06 The currency is a numeric value used to represent money. In some cases, you can use a double-precision value in place of the currency but because Microsoft Excel is equipped for accounting, you can safely take advantage of the currency type. When using currency rightly, its number presents the character specified in Control Panel. For US English, such a character is $. Constants A constant is a value that does not change. The constants you will be using in your expressions have already been created and are built-in Microsoft Excel. Normally, Visual Basic for Applications (VBA), the version of Microsoft Visual Basic that ships with Microsoft Excel also provides many constants.  Just in case you are aware of them, you will not be able to use those constants, as Microsoft Excel does not inherently “understand” them. For this reason, we will mention here only the constants you can use when building regular expressions.    The algebraic numbers you have been using all the time are constants because they never change. Examples of constant numbers are 12, 0, 1505, or 88146. Therefore, any number you can think of is a constant. Every letter of the alphabet is a constant and is always the same. Examples of constant letters are d, n, c. Some characters on your keyboard represent symbols that are neither letters nor digits. These are constants too. Examples are &, |, @, or !    In Boolean algebra, something is considered True when it holds a value. The value is also considered as 1 or Yes. By contrast, any other value is considered False, 0, or No.    When a field holds a value, the value would be considered using the comparison operators we will learn.

 

Operands and Operators

The values we have used so far were provided in cells of a spreadsheet. In some cases, you will need to display a value that is a combination of other values. For example, you may need to combine a first name to a last name to create a full name. In another case, to calculate an employee’s weekly salary, you may need to use the value of a salary and multiply it with a number of hours worked in a week. Most, if not all, of these expressions use what we call operators and operand. An operation is a technique of using a value or the contents of a cell, or to combine two or more values or contents of cells to either modify an existing value or to produce a new value. Based on this, to perform an operation, you need at least one value or the contents of one cell and one symbol. A value involved in an operation is called an operand. A symbol involved in an operation is called an operator. .

Regular Operators

 

The Assignment Operator =

In order to display a value in a cell, it must be preceded with the assignment operator, which is “=”. The syntax you would use is: =ValueOrExpression The operand on the right side of the assignment operator is referred to as the right value or Rvalue. It can be a known value or a reference to another cell. There are two main ways we will use the assignment operator: Imagine you already have a cell such as B12 and you want to display its value in another cell such as A4. In A4, you can type =B12 and press Enter. The contents of B12 would be provided to, and displayed in, A4 In some other operations and functions we will learn, you can use the = operator inside of the expression or function to perform another, intermediary operation, such as a comparison

Unary Operators

A unary operator is one that uses only one operand. An operator is referred to as binary if it operates on two operands. The Positive Unary Operator + Algebra uses a type of ruler to classify numbers. This fictitious ruler has a middle position of zero. The numbers on the left side of the 0 are considered negative while the numbers on the right side of the 0 constant are considered positive:

-∞   -6 -5 -4 -3 -2 -1   1 2 3 4 5 6   +∞    0 -∞   -6 -5 -4 -3 -2 -1   1 2 3 4 5 6   +∞

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign to its left. Examples are +4, +228, and +90335. In this case the + symbol is called a unary operator because it acts on only one operand. The positive unary operator, when used, must be positioned to the left side of its operand. As a mathematical convention, when a value is positive, you don’t need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, or 90335. Because the value does not display a sign, it is referred as unsigned. The Negative Unary Operator - In order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, and -32706. A value accompanied by - is referred to as negative. The - sign must be typed on the left side of the number it is used to negate. Remember that if a number doesn’t have a sign, it is considered positive. Therefore, whenever a number is negative, it must have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left. In the same way, if you want to negate the value of a cell, an expression, or a function, you can type the – operator on its left.

The Double Quotes: ""

Double-quotes are used to enclose a string. As we reviewed earlier, a string can be an empty space, one character, or a group of characters. Such a string must be considered “as is”. Therefore, to include a string in an expression, put it in double-quotes. Examples are “ “, "@", "Hermano", or "Rancho Cordova ". To display a string in a cell, precede it with double-quotes. For example, to display Rancho Cordova in cell B4, you would click B4, type ="Rancho Cordova" and press Enter.

The String Concatenator: &

The & operator is used to append two strings, the contents of two cells, or expressions. This is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general syntax of the concatenation operator is expressed as: Value1 & Value2 To display a concatenated expression, use the assignment operator on the left of the string. For example, imagine you want to concatenate Juan to Marcus and display the resulting string in cell C5, in C5, you would type =”Juan” & “Markus” and press Enter. The result would be JuanMarkus. To concatenate more than two expressions, you can use as many & operators between any two expressions as necessary. For example, to add an empty space in the above string, in cell C5, you would type =”Juan” & “ “ & “Markus” In the same way, you can concatenate the contents of various cells.

The Addition: +

The addition is used to add one value or expression to another. It is performed using the + symbol and its syntax is: Value1 + Value2 The addition allows you to add two numbers such as 12 + 548 or 5004.25 + 7.63 After performing the addition, you get a result. You can display such a result in a cell or use it as an intermediary variable in an expression. For example, to add 242.48 to 95.05 and display the result in cell C6, in C6, you would type =242.48 + 95.05 and press Enter.

Practical Learning: Using the Addition

Open the Red Oak High School workbook you started in Lesson7 and click Sheet2 In cell E11, type =E3+E4+E5+E6+E7+E8+E9+E19 press Enter Click cell F11 and type = Click cell F3 and type + Click cell F4 and type + Click cell F5 and type + Click cell F6 and type + Click cell F7 and type + Click cell F8 and type + Click cell F9 and type + Click cell F10 and press Enter Save the workbook

  

The Subtraction: -

The subtraction is performed by retrieving one value from another value. This is done using the - symbol. The syntax used is: Value1 - Value2 The value of Value1 is subtracted from the value of Value2. After performing the operation, a new value results. This result can be used in any way you want. For example, you can display it in a cell using the assignment operator as follows: = Value1 - Value2

The Multiplication: *

The multiplication allows adding one value to itself a certain number of times, set by the second value. The multiplication is performed with the * sign which is typed with Shift + 8. Here is an example: Value1 * Value2 During the operation, Value1 is repeatedly added to itself, Value2 times. The result can be assigned to another value or displayed in a control as follows: = Value1 * Value2

The Division: /

The division is used to get the fraction of one number in terms of another. For example, to divide a Value1 if Value2 pieces, you would use a syntax as: Value1 / Value2 After performing the operation, you get a new result you can use as you see fit. You can display in a cell or involve it in an expression.

The Exponentiation: ^

Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following mathematical formula: yx The operation is performed as y^x and means the same thing. Either or both y and x can be values or expressions, but they must carry valid values that can be evaluated. When the operation is performed, the value of y is raised to the power of x. You can display the result of such an operation in a cell using the assignment operator as follows: =y^x You can also use the operation in an expression.

The Parentheses Operators: ()

Parentheses are used to create sections in an expression. This regularly occurs when more than one operator is used in an operation. Consider the following expression typed in cell F2 as =8 + 3 * 5. The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, in the cell, you would write =(8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23. As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one; but parentheses allow you to control the sequence of these operations.

Worksheets and Expressions

To create a pseudo-database, you can connect worksheets that would exchange information. This is a valuable use of lists as one worksheet can be used to store particular information and make it available to other worksheets. In Lesson3, we already saw briefly how worksheets can be linked. We didn't expand on it because we had not been introduced to expressions or functions yet. Fortunately, almost any expression or function you use in the cells inside one worksheet can also be used in linked worksheets.