Statistics Software

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

Operator order of precedence and Creating Formula in Excel

by

Creating Formula in Excel

Creating customized (user-defined) formulas in Microsoft Excel is not too difficult. For creating formulas just combine the references of your data with the correct mathematical operator (such as -, +, /, *, and ^).

Operator Order of Precedence

The order of mathematical operations determines in which order the mathematical operations are carried out. If more than mathematical operators are used in the formula, there is a specific order (sequence) that Microsoft Excel will follow to perform (compute) these mathematical operations. However, to change the order of operations, brackets (parenthesis) are used in the Excel formula. The easy way to remember the order of operations (precedence) is to remember the acronym: BEDMAS (PEDMAS), that i.e.,

The Operator order of precedence (operations) is:

Bracket or Parenthesis
Exponents (^)
Division (/)
Multiplication (*)
Addition (+)
Subtraction (-)

Suppose, the following is the screenshot of an Excel sheet. The formula is also shown in the formula bar. As an example, addition (+), division (/), and multiplication (*) operators are used.

Operator order of precedence

The formula in the screenshot performs the computation in the following order,

  • $E1/F1$ will be computed (the answer is 1.5),
  • the answer of $E1/F1$ will be multiplied by value of $G1$ (answer is $1.5*2 = 3$)
  • the answer of $E1/F1 * G1$ will be added to $D1$ (answer is 7)

Any operation(s) enclosed in brackets (parenthesis) will be carried out first followed by any exponents. After that, Excel will consider division or multiplication operations to be of equal importance. The operations are performed in the order they occur left to right in the formula. A similar sequence is also performed for addition and subtraction. Both (addition and subtraction) are considered equal in the order of operations. The operator that appears first will be computed first.

order of precedence bracket

For example, see the screenshot The sequence of operations is

  • The first bracket will be computed, that is, multiplication will be performed $(2 *2 = 4)$
  • E1 will be divided by the answer from the multiplication of $F1$ and $G1 (3/4 = 0.75)$
  • Lastly, $D1$ will be added to the answer $0.75 (4 + 0.75 = 4.75)$

Now check the sequence in the following screenshot

order of precedence bracket

For Creating formula in Excel, see the link Creating Excel Formula

Learn R Programming

Online MCQs Test Website

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