Statistics Software

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

Creating Formulas in MS Excel and Changing Data

by

Changing the data

Before Creating formulas in MS Excel, you need numeric data in different columns or rows of the Excel sheet. Suppose you want to enter a few numbers in a column. Before entering these numbers, you should confirm the cell reference where you need to enter the data. Let’s start by entering numbers in Microsoft Excel‘s cells $A1$ and $A2$. For this purpose follow the steps given below

  1. Click on the cell $A1$
  2. Type 3 from the keyboard
  3. Press the ENTER or DOWN ARROW key on the keyboard. You will be in Cell A2
  4. Now type say 2 from the keyboard and press the ENTER key

Suppose you want to add these numbers in Cell $C1$. You need to write a formula in cell $C1$. After writing the correct formula the content of Cell $C1$ will immediately change to adding two numbers typed in $A1$ and $A2$ and used in $C1$ as formula content.

excel-data-and-formula

Creating Formulas in MS Excel

In this section, we will learn about Creating Formulas in MS Excel. In Excel, each formula begins with an equal sign ($=$), see the picture below

Creating Formulas in MS Excel

Therefore, when creating formulas in Excel, ALWAYS start by typing the equal sign. The equal sign is typed in the Cell where you want the answer to appear. Like the image above, follow these steps

  1. Click on cell $C1$ with ARROW keys from the keyboard or with the mouse pointer.
  2. Type the equal sign in cell $C1$.
excel-data-and-formula

After typing the equal sign in step 2, you have two choices for adding cell references to the spreadsheet formula. Note that cell reference is the name of the cell you want to use in the formula. $A1$ and $A2$ are cell references of numbers 3 and 2, respectively.

  1. You can type these references in or,
  2. You can use an Excel feature called Pointing
excel-data-and-formula

Pointing allows you to click with your mouse on the cell that contains the data or approach a cell reference using the keyboard ARROW keys containing your data to add. This will add cell reference to the formula.

After typing an equal sign in cell C3 in step 2:

  1. Click on cell $A1$ with the mouse pointer to enter the cell reference into the formula
  2. Type a plus (+) sign. You can also use other operators such as for multiplication use you have to use the * symbol, for division/symbol, and for subtraction use $–$, etc.
  3. Click on cell $A2$ with the mouse pointer to enter the cell reference into the formula
  4. Press the ENTER key on the keyboard

The answer 5 should appear in cell $C1$.

Note if you have more than one row or column of data then you need to perform calculations on each row or column cell. It is often possible to copy the first formula to other cells. The easiest way is to copy formulas with the file handle.

See also Creating Formulas in Microsoft Excel

Learn about Primary and Secondary Data

R Programming and Data Structure in R

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