Creating and Using an Expression

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

Creating and Using an Expression

Introduction

When using a worksheet, an expression is entered in a cell and we will see various types of expressions. As done with the other values we have used so far, to create an expression, you first click a cell. If you know the expression you want to use, you can type it either in the cell or in the Formula Bar. In most cases, after typing the expression, you can press Enter (or Tab). In some other cases, as we will see, you can click another cell whose value will complete the expression.

To create an expression, you will use some constants values such as numbers or strings, the operators we are going to study, the names of cells, the names of groups of cells, and sometimes the functions:

  • To use a constant value, type it in the right place
  • To use a cell, you can type its name. Here is an example:
     
  • To use a cell, after starting the expression, click the cell you want to involve. When a cell has been clicked, its blue borders blink. Here is an example:
     
     

    After clicking the cell, you can continue creating the expression. At any time when needed, you can type another name of a cell or you can click any other cell you need. Here is an example:
     


    After typing and/selecting the cells, click the Enter button Enter or press Enter to end the expression
  • To use a function, we will learn how to, when we get to the functions

After creating the expression, the value of the cell would represented the expression. When you click it, the Formula Bar would display the expression. Here is an example:

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 ".

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: Adding Cells Values

  1. Start Microsoft Excel
  2. Open the CPAR Repair Orders1.xlsx workbook and, if necessary, click the Repair Order tab
  3. Click Cell J31 and type =
  4. Click Cell J16 and type +
  5. Click Cell J17 and type +
  6. Click Cell J18 and type +
  7. Click Cell J19 and type +
  8. Click Cell J20 and type +
  9. Click Cell J21
     
    Addition
  10. On the Formula Bar, click the Enter button 
  11. Click Cell J32 and type =
  12. Click Cell J24 and type +
  13. Click Cell J25 and type +
  14. Click Cell J26 and type +
  15. Click Cell J27 and type +
  16. Click Cell J28 and type +
  17. Click Cell J29 and, on the Formula Bar, click the Enter button 
  18. 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

  Practical Learning: Multiplying Cells Values

  1. The CPAR Repair Orders.xlsx workbook should still be opened and the Repair Order sheet active.
    Click Cell J16
  2. Type =H16*I16 and notice that the cells involved are selected
     
    Addition
  3. On the Formula Bar, click the Enter button 
  4. Position the mouse on the button in the lower-right corner of the selected cell until the mouse cursor becomes a +
     
    Addition
  5. To use AutoFill, click and drag down to Cell J21
     
    AutoFill
  6. Release the mouse
  7. Select Cells J16:J21
  8. Right-click the selection and click Format Cells
  9. Click the Border property page and change it as follows:
     
    Format Cells
  10. Click OK
  11. Save the workbook

  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: ^

RTHS - Calculate the mean grade of each course (See Lesson 11)

 

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.

  Practical Learning: Using the Parentheses

  1. The CPAR Repair Orders.xlsx workbook should still be opened and the Repair Order sheet active.
    Click Cell J34 and type =(
  2. Click Cell J31 and type +
  3. Click Cell J32 and type )*
  4. Click Cell J33
     
    Using Parentheses
  5. On the Formula Bar, click the Enter button 
  6. Click Cell J35 and type =((
  7. Click Cell J31 and type +
  8. Click Cell J32 and type )*
  9. Click Cell J33 and type )+
  10. Click Cell J31 and type +
  11. Click Cell J32
     
    Using Parentheses
  12. On the Formula Bar, click the Enter button 
  13. Save the workbook
  14. Create an order as test

College Park Auto Repair