Lookup Tables and Boolean Express
As we learned, MathCAD stores data in the form of a matrix. To generate this data within MathCAD, or
data in a matrix, MathCAD requires the use of range variables.
Excel also stores data in a matrix, but it visually appears as a table. For this reason, most people do not
when working with Excel. This is despite the fact that one of the most common methods of solving simu
equations in Excel is the application of matrix methods and that Excel does include functions to perform
manipulations.
The ability to generate a table in Excel simply and quickly is perceived as an inherent advantage of Excel
This is often true if the end product is simply a data table to display and print. However, what if one nee
piece of data within the table for subsequent calculations. In MathCAD, this is inherent in the original fo
equation or function. Not so in Excel. To do so requires the application of ‘Lookup and reference functio
lecture, we will learn to use the HLOOKUP( ) , VLOOKUP( ), and LOOKUP( ) functions. Where HLOOKUP(
operate most efficiently on tables of data (i.e.: arrays), the LOOKUP( ) function is best applied to rows o
(i.e.: Vectors). As we will see through example, these functions have similarity to the use of range variab
In fact, they could be considered as matrix [access] functions.
The introduction of Boolean expressions and conditional functions appears to be out of place with Excel
functions. However, as we will soon see, the use of such functions will be quite useful when dealing wit
Let’s discuss those first.
Expressions
ta within MathCAD, or to access the
n, most people do not think of matrices
methods of solving simulataneous
e functions to perform matrix
ent advantage of Excel over MathCAD.
owever, what if one needs to access a
herent in the original formulation of the
p and reference functions’. In this
ons. Where HLOOKUP( ) and VLOOKUP( )
best applied to rows or columns of data
the use of range variables in MathCAD.
out of place with Excel lookup
seful when dealing with Excel lookup.
Boolean and Conditional Expr
Boolean expressions were introduced in MathCAD. They are not used any differently in Excel although t
searching on the expression ‘Logical Functions’. Excel includes the following logical functions: AND
Unbelievably, Excel does not have an XOR function, although it has been added to Excel 2013. Excel also
functions. These functions are of limited use in Excel and are provided for backward compatibility and c
programs.
Although technically a reference function, Excel’s LOOKUP( ) function will act as a Case
are limited to 64 nested IF( ) statements.
Conditional functions in Excel can be found by searching on ‘Information Functions’. These include the c
functions are used with the IF( ) function to generate a conditional response or calculation but are also u
Let’s take a quick look at some of these functions. Since the application was covered under MathCAD. T
Excel is function syntax. Since MathCAD emulates written expressions, logical operators are placed betw
input as a comma separated list.
AND( )
OR( )
XOR( )
Values
0
1
2
TRUE
=AND(D27=0,D28>0,D290)
FALSE
=OR(D330,D340,D290))
FALSE
TRUE
=NOT(OR(D330,D34
Purchase answer to see full
attachment