Types of Values
|
An expression is a combination of values and symbols used to produce a new value.
There are different types of values involved in an expression. They are:
|
|
A string is 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, 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,
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
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
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.
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.
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:
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:
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:
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:
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.
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 & 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 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.
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 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 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.
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.
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.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
or press Enter to end the expression
 Practical Learning:
Using the Parentheses |
- The CPAR Repair Orders.xlsx workbook should still be opened and the Repair Order sheet
active.
Click Cell J34 and type =( - Click Cell J31 and type +
- Click Cell J32 and type )*
- Click Cell J33
- On the Formula Bar, click the Enter button
- Click Cell J35 and type =((
- Click Cell J31 and type +
- Click Cell J32 and type )*
- Click Cell J33 and type )+
- Click Cell J31 and type +
- Click Cell J32
- On the Formula Bar, click the Enter button
- Save the workbook
- Create an order as test
So far in our expressions, we were selecting cells to
combine their values to the operators and all the cells we used belonged
to the same worksheet. In a typical workbook, you use various worksheet.
Sometimes you will want want to a value stored in a cell of a separate
worksheet and want to involve the value of that cell in the expression. Of
course you can copy the value and paste it where needed. The problem is,
when/if that value changes, you would have to change it in your
expression. Instead of memorizing the value, you can create an expression
that would directly use the value in its cell.
In Microsoft Excel, yon can create an expression that
involves cells of two or more different worksheets. To proceed, in the
cell where the expression will appear, type the assignment operator
"=". If you know the name of the worksheet where the other cell
is located, type that name. If the name of the worksheet is in more than
one word, include it in single-quotes. If the name is in one word, you can
omit the single-quotes. After the name of the worksheet, type the !
operator. If you want to use one cell, type its name. If you want to use a
range of cells, type that range. If you want to use different non-adjacent
cells, for each cell you want to use, type the name of the worksheet
followed by ! and the name of the cell.
Instead of remembering the names of the worksheets and
their cells, and as we have done so far with cells, you can start an
expression with the = operator. Then, when you need a cell located in
another worksheet, click the tab of that worksheet and select the cell.
Microsoft Excel would take care of the names behind the scenes. When you
end the expression, you will be taken back to the worksheet where you were
working.
You may have one (or more) value(s) stored in the
cell(s) of a (different) worksheet(s) and the worksheet(s) belong(s) to a
different (or various) workbook(s). To use such a values, you could copy
it and paste it in the cell that holds the expression you are creating. As
mentioned for the worksheet, if that value changes, you would have to
manually update the expression. Fortunately, you can create an expression
that involves various workbooks and link them so that, when the value of
an involved cell changes, the result of the expression would be
automatically updated.
To involve the cell of a
different workbook in an expression, start with the = operator. Type the name of
the workbook as a file and, because it is a file, include its extension. Because
the name of the workbook includes a period, you must start that name with the
left square bracket "[" and you must end with the right square bracket
"]". Just after the right square bracket, type the name of the
worksheet where the cell is located. This name, whether the name of the
worksheet is in one or more words, don't include it in single-quotes. Instead,
the combination of the name of the workbook and the name of the worksheet must
be included in single-quotes. After the
the combination of the name of the workbook and the name of the worksheet, type
the ! operator. As reviewed for the worksheet, the ! is followed by the
name of the cell.
As done for the cells of one worksheet or a
cell in a different worksheet, after starting the expression, you can access the
other workbook and click the desired cell. If you use this technique (as opposed
to typing the names), Microsoft Excel would start the name of the cell with $,
followed by the letter-name of the cell, followed by $, and followed by the
number of the row.
|
|
Practical Learning: Using Various
Workbooks in an Expression |
- On the Ribbon, click View
- In the Window section, click View Side by Side.
In the Window section of the Ribbon, click Switch Windows. If CPAR Accounting Records.xlsx does not have a check mark, then click it - In the CPAR Accounting Records.xlsx workbook, click the Transaction Analysis tab
- On October 13, 2008, the company fixed a car for a
customer. We don't remember how much was paid. We will get the value from
the Repair Orders worksheet and put it in the Transaction Analysis
worksheet. We will also update the income satement.
In the Transaction Analysis worksheet, click Cell C20 and type = - In the Window section of the Ribbon, click Switch Windows and click CPAR Repair Orders.xlsx
- In the worksheet, scroll down and click Cell J35
- Press Enter
- In the Transaction Analysis worksheet, click Cell M20 and type =
- In the Window section of the Ribbon, click Switch Windows and click CPAR Repair Orders.xlsx
- In the worksheet, scroll down and click Cell J35 and press Enter
- Access the CPAR Accounting Records.xlsx workbook and click the Income Statement worksheet
- Click Cell J7
- In the Formula Bar, click the expression and press End
- Type +
- In the CPAR Accounting Records.xlsx workbook, click the Transaction Analysis tab
- Click Cell M20 and press Enter
- In the Window section of the Ribbon, click Switch Windows and click CPAR Repair Orders.xlsx
- In the worksheet, click Cell H17, type 45.35 and press Enter
- Notice that the total of the invoice has changed.
Access the CPAR Accounting Records.xlsx workbook and check that the values have been updated
- Save both workbooks
No comments:
Post a Comment