Statistics Software

Data Analysis using Statistics Software Such as SPSS, Minitab, MS-Excel, STATA, Eviews, etc. Stat-Soft

Writing Excel Formulas (2016)

by

Writing Excel formulas is a little different than the way it is done in mathematics class. All Excel formulas start with an equal sign (=), that is, the equal sign always goes in that cell where you want the answer to appear from the formula. Therefore, the equal sign informs Excel that this is a formula not just a name or number. Let us start with writing Excel Formulas.

The Excel formula looks like

= 3 + 2

rather than

3+2 =

Writing Excel Formulas and Cell References in MS Excel

The example of a formula has one drawback. If you want to change the number being calculated (3, and 2), you need to edit it or re-write the formula. A better way is to write formulas in such a way that you can change the numbers without changing or re-writing the formulas themselves. To do this, cell references are used, which tells Excel that data/ numbers are located in a cell. Therefore a cell’s location/ reference in the spreadsheet is referred to as its cell reference.

To find a cell reference, simply click the cell of which you need cell reference, and from NAME BOX (shown in the figure below), see the text, such as F2.

Writing Excel formulas 1

F2 represents the cell in the $F$ column (horizontal position) and row 2 (vertical position). It means cell reference can also be found by reading the column heading (at the topmost position) of the cells and row number (at the leftmost position). Therefore, cell reference is a combination of the column letter and row number such as A1, B2, Z5, and A106, etc. For the previous formula example, instead of writing = 3 + 2 in cell suppose (C1), follow this way of cell reference and formula writing:

In cell A1 write 3, and in cell B2 write 2. In the C1 cell write the formula such as,

= A1 + A2
Excel Formula 2

Note that there is no gap between A & 1 and A & 2, they are simply A1 and A2. See the diagram for a clear understanding.

Updating/ Writing Excel Formulas

Upon wrong cell references in the Excel formula, the results from the formula will be automatically updated, whenever the data value in relevant cells is changed. For example, if you want to change data in cell A1 to 8 instead of 3, you only need to change the content of A1. The result of the formula in cell C1 will automatically be updated after the updation of the data value in A1 or B1.

Note that the formula will not change because the cell references are being used instead of data values or numbers.

Data Analysis in R Language

Read more about Creating Formulas in MS Excel and Operator Order of Precedence

Creating Matrices in Mathematica (2015)

by

In this article, we will discuss creating matrices in Mathematica.

Matrices in Mathematica

A matrix is an array of numbers arranged in rows and columns. In Mathematica, matrices are expressed as a list of rows, each of which is a list itself. It means a matrix is a list of lists. If a matrix has $n$ rows and $m$ columns then we call it an $n$ by $m$ matrix. The value(s) in the ith row and jth column is called the $i,j$ entry.

In Mathematica, matrices can be entered with the { } notation, constructed from a formula, or imported from a data file. There are also commands for creating diagonal matrices, constant matrices, and other special matrix types.

Creating Matrices in Mathematica

  • Create a matrix using { } notation
    mat={{1, 2, 3}, {4, 5, 6}, {7, 8, 9}}
    but the output will not be in matrix form, to get in matrix form use commands like
    mat//MatrixForm
  • Creating matrix using Table command
    mat1=Table[b{row, column},
    {row, 1, 4, 1}, {column, 1, 2, 1}]
    ];
    MatrixForm[mat1]
  • Creating symbolic matrices such as
    mat2=Table[xi+xj , {i, 1, 4}, {j, 1, 3}]
    mat2//MatrixForm
  • Creating a diagonal matrix with nonzero entries at its diagonal
    DiagonalMatrix[{1, 2, 3, r}]//MatrixForm
  • Creating a matrix with the same entries i.e. a constant matrix
    ConstantArray[3, {2, 4}]//MatrixForm
  • Creating an identity matrix of order $n\times n$
    IdentityMatrix[4]
Matrices and Mathematica

Matrix Operations in Mathematica

In Mathematica, matrix operations can be performed on both numeric and symbolic matrices.

  • To find the determinant of a matrix
    Det[mat]
  • To find the transpose of a matrix
    Transpose[mat]
  • To find the inverse of a matrix for a linear system
    Inverse[mat]
  • To find the Trace of a matrix i.e. sum of diagonal elements in a matrix
    Tr[mat]
  • To find the Eigenvalues of a matrix
    Eigenvalues[mat]
  • To find the Eigenvector of a matrix
    Eigenvector[mat]
  • To find both Eigenvalues and Eigenvectors together
    Eigensystem[mat]

Note that +, *, and ^ operators all automatically work element-wise.

Displaying Matrix and its Elements

  • mat[[1]]         displays the first row of a matrix where mat is a matrix created above
  • mat[[1, 2]]     displays the element from the first row and second column, i.e. m12 element of the matrix
  • mat[[All, 2]]  displays the 2nd column of matrix

Interactive Input (Menu)

  1. Go to Insert > Table/Matrix > New…
  2. Select Matrix (List of lists).
  3. Define the number of rows and columns.
  4. Click OK.
  5. Use the provided interface to enter values in each cell.

Predefined Matrices

Mathematica provides functions to generate specific types of matrices:

  • IdentityMatrix: Creates an identity matrix.
  • DiagonalMatrix: Creates a diagonal matrix from a specified list.
  • HilbertMatrix: Generates a Hilbert matrix.
  • VandermondeMatrix: Creates a Vandermonde matrix.

Importing from Files

  • Use the Import function to read data from various file formats like CSV, TSV, or Excel spreadsheets and convert them into matrices.
Matrices in Mathematica

References

R Frequently Asked Questions

Matrix in Matlab: Create and manipulate Matrices

by

Matrix in Matlab can be created and manipulated

Matrix (a two-dimensional, rectangular shape used to store multiple elements of data in an easily accessible format) is the most basic data structure in Matlab. The elements of a matrix can be numbers, characters, logical states of yes or no (true or false), or other Matlab structure types. Matlab also supports more than two-dimensional data structures, referred to as arrays in Matlab. Matlab is a matrix-based computing environment in which all of the data entered into Matlab is stored as a matrix.

The MATLAB environment uses the term matrix for a variable that contains real or complex numbers. These numbers are arranged in a two-dimensional grid. An array is, more generally, a vector, matrix, or higher dimensional grid of numbers. All variables in Matlab are multidimensional arrays, no matter what type of data they store. A matrix is a two-dimensional array often used for linear algebra.

It is assumed in this Matlab tutorial that you know some of the basics of how to define and manipulate vectors in Matlab software. we will discuss the following:

  1. Defining Matrix in Matlab
  2. Matrix Operations in Matlab
  3. Matrix Functions in Matlab

1)  Define or Create a Matrix in Matlab

Defining a matrix in Matlab is similar to defining a vector in Matlab. To define a matrix, treat it as a column of row vectors.

>> A=[1 2 3; 4 5 6; 7 8 9]

Note that spaces between numbers are used to define the elements of the matrix and semi-colon is used to separate the rows of matrix A. The square brackets are used to construct matrices. The individual matrix and vector entries can be referenced within parentheses. For example, A(2,3) represents an element in the second row and third column of matrix A.

Matrix in Matlab
Matrix in Matlab

A matrix in Matlab is a type of variable that is used for mathematical/statistical computation—some examples of creating a matrix in Matlab and extracting elements.

>> A=rand(6, 6)
>> B=rand(6, 4)
>> A(1:4, 3) is a column vector consisting of the first four entries of the third column of A
>> A(:, 3) is the third column of A
>> A(1:4, : ) contains column  and column 4 of matrix A

Convenient matrix-building Functions

eye –> identity
zeros –> matrix of zeros
ones –> matrix of ones
diag –> create or extract diagonal elements of a matrix
triu –> upper triangular part of a matrix
tril –> lower triangular part of a matrix
rand –> randomly generated matrix
hilb –> Hilbert matrix
magic –> magic square

2)  Matrix Operations in Matlab

Many mathematical operations can be applied to matrices and vectors in Matlab such as addition, subtraction, multiplication, and division of matrices, etc.

Matrix or Vector Multiplication

If $x$ and $y$ are both column vectors, then $x’*y$ is their inner (or dot) product, and $x*y’$ is their outer (or cross) product.

Matrix division

Let $A$ be an invertible square matrix and $b$ be a compatible column vector then

x = A/b is solution of A * x = b
x = b/A is solution of x * A = b

These are also called the backslash (\) and slash operators (/) also referred to as the mldivide and mrdivide.

3)  Matrix Functions in Matlab

Matlab has many functions used to create different kinds of matrices. Some important matrix functions used in Matlab are

eig –> eigenvalues and eigenvectors
eigs –> like eig, for large sparse matrices
chol –> Cholesky factorization
svd –> singular value decomposition
svds –> like SVD, for large sparse matrices
inv –> inverse of matrix
lu –> LU factorization
qr –> QR factorization
hess –> Hessenberg form
schur –> Schur decomposition
rref –> reduced row echelon form
expm –> matrix exponential
sqrtm –> matrix square root
poly –> characteristic polynomial
det –> determinant of matrix
size –> size of an array
length –> length of a vector
rank –> rank of matrix

To learn more about the use of Matrices in Matlab, See the Matlab Help

Using Mathematica Built-in Functions (2014)

by

Introduction to Mathematica Built-in Functions

There are thousands of thousands of Mathematica Built-in Functions. Knowing a few dozen of the more important will help to do lots of neat calculations. Memorizing the names of most of the functions is not too hard as approximately all of the built-in functions in Mathematica follow naming convention (i.e. names of functions are related to the objective of their functionality), for example, the Abs function is for absolute value, Cos function is for Cosine and Sqrt is for the square root of a number.

The important thing than memorizing the function names is remembering the syntax needed to use built-in functions. Remembering many of the built-in Mathematica functions will not only make it easier to follow programs but also enhance your programming skills.

Important and Widely Used Mathematica Built-in Functions

The following is a short list related to Mathematica Built-in Functions.

  • Sqrt[ ]:   used to find the square root of a number
  • N[ ]:   used for numerical evaluation of any mathematical expression e.g. N[Sqrt[27]]
  • Log[  ]: used to find the log base 10 of a number
  • Sin[  ]: used to find trigonometric function Sin
  • Abs[  ]: used to find the absolute value of a number

Common Mathematica built-in functions include

  1. Trigonometric functions and their inverses
  2. Hyperbolic functions and their inverses
  3. logarithm and exponential functions

Every built-in function in Mathematica has two very important features

  • All Mathematica built-in functions begin with Capital letters, such as for square root we use Sqrt, for inverse cosine we use the ArCos built-in function.
  • Square brackets are always used to surround the input or argument of a function.

For computing the absolute value -12, write on command prompt Abs[-12]  instead of for example Abs(-12) or Abs{-12} etc i.e.   Abs[-12] is a valid command for computing the absolute value of -12.

Mathematica Built-in Functions

Note that:

In Mathematica single square brackets are used for input in a function, double square brackets [[ and ]] are used for lists, and parenthesis ( and ) are used to group terms in algebraic expression while curly brackets { and } are used to delimit lists. The three sets of delimiters [ ], ( ), { } are used for functions, algebraic expressions, and lists respectively.

Introduction to Mathematica

R Programming Language

MCQs General Knowledge