Category: Microsoft Excel

Mar 29, 2017

Creating Formula in Excel: Operators order of precedence

Creating Formula in Excel: Operators Order of Precedence

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 ^).

Microsoft 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 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 order of operations (precedence) is:

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

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

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

E1/F1 will be computed (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. Similar sequence is also performed for addition and subtraction. Both (addition and subtraction) are considered equal in the order of operations. The operator which appears first will be computed first.

For example, see the screenshot The sequence of operation is

First bracket will be computed, that is, multiplication will be performed (2 *2 = 4)
E1 will be divided by the answer from 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

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

Microsoft Excel

Oct 30, 2016

by Muhammad Imdad Ullah

Changing the data and creating Formula in MS-Excel

Changing the data

Before writing your required formula, you need numeric data in different columns or rows of Excels’ sheet. Suppose you want to enter few numbers in a column. Before entry these number you should first confirm the cell reference where you need to enter the data. Let start by entry number in Excels’ cell A1 and A2. For this purpose follow steps given below

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

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

Creating Formula in MS-Excel

In Excel, each formula begins with a equal sign (=), see the picture below

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

Click on cell C1 with ARROW keys from keyboard or with mouse pointer.
Type the equal sign in cell C1.

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 cell you want to use in formula. A1 and A

2 are cell references of numbers 3 and 2, respectively.

You can type these references in or,
You can use an Excel feature called Pointing

Pointing allows you to click with your mouse on the cell contain the data or approaching to a cell reference using 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:

Click on cell A1 with the mouse pointer to enter the cell reference into the formula
Type a plus (+) sign. You can also use other operators such as for multiplication use you have to use * symbol, for division / symbol and for subtraction use – etc.
Click on cell A2 with the mouse pointer to enter the cell reference into the formula
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 to do this is to copy formulas with the file handle.

Writing Excel Formulas

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

= 3 + 2

rather than

3+2 =

Cell References in Formula

The example of 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 formula 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 figure below), see the text, such as F2.

F2 represents the cell in F column (horizontal position) and row 2 (vertical position). It means cells reference can also be found by reading column heading (at the top most position) of the cells and row number (at the left most position). Therefore, cell reference is a combination of the column letter and row number such as A1, B2, Z5, and A106 etc. For 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 C1 cell write the formula such as,

= A1 + A2

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

Updating Excel Formula

Upon wrong cell references in Excel formula, the results from 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 only. The result of formula in cell C1 will automatically be updated after the updation of data value in A1 or B1.

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