Lesson 1, Topic 1
In Progress

1.12. Use MS Excel to apply formulae to provide alternative solutions to the given problem

ryanrori February 3, 2021

[responsivevoice_button rate=”0.9″ voice=”UK English Female” buttontext=”Listen to Post”]

After completing this section, the learner will be able to apply formula to worksheets to provide alternative solutions to the given problem, by successfully completing the following:

  • Enter formula taking into consideration the natural order of operation
  • Enter formula to deliberately change the natural order of operation
  • Interpret formula error messages and the correct the formula accordingly
  • Demonstrate the difference between relative and absolute cell addressing by using it in a formula
  • Change data to test possible solutions to the given problem without having to change formulae
  • Create scenarios that demonstrate different possible outcomes

One of Excel’s most useful features is that it allows users to create custom formulas to perform calculations on their data. Excel also contains built-in formulas called functions that make it easy to perform common calculations on data.

Formulas in Microsoft Excel begin with an equal sign. The equal sign tells Excel that the succeeding characters constitute a formula. If you don’t enter the equal sign, Excel will treat your entry as text and the calculation will fail. 

To show how formulas work, we’ll begin with a simple exercise by selecting blank cell A1. Then type =5+5, and press Enter. Excel performs the calculation and produces a result of 10 in cell A1. 

Notice the formula bar shows the formula you just typed. What appears in the cell is the result; what appears in the formula bar is the underlying value, which is a formula in this case.

Enter formula taking into consideration the natural order of operation

When performing calculations in a formula, Excel follows certain rules of precedence:  Excel calculates expressions within parentheses first.  Excel calculates multiplication and division before addition and subtraction.  Excel calculates consecutive operators with the same level of precedence from left to right. 

For example, the formula = 10+10*2 gives a result of 30 as Excel multiplies 10 by 2 and then adds 10. However, the formula =(10+10)*2 produces a result of 40. This is because Excel calculates the expression (10+10) within the parentheses first. It then multiplies by 2.

If you are unsure of the order in which Excel calculates, use parentheses – even if the parentheses aren’t necessary. Parentheses also make your formulas easier to read.

Enter formula to deliberately change the natural order of operation

If more than one operator is used in a formula, there is a specific order that Excel will follow to perform these mathematical operations. This order of operations can be changed by adding brackets to the equation. 

An easy way to remember the order of operations is to use the acronym: 

BEDMAS

The Order of Operations is: 

Brackets
Exponents
Division
Multiplication
Addition
Subtraction

How the Order of Operations Works 

Any operation(s) contained in brackets will be carried out first followed by any exponents. 

After that, Excel considers division or multiplication operations to be of equal importance, and carries out these operations in the order they occur left to right in the equation. 

The same goes for the next two operations – addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction, is the operation carried out first. 

Interpret formula error messages

If you create a formula in Excel that contains an error or circular reference, Excel lets you know about it with an error message. A handful of errors can appear in a cell when a formula or function in Excel cannot be resolved. Knowing their meaning helps correct the problem.

ErrorMeaning
#DIV/0!Trying to divide by 0
#N/A!A formula or a function inside a formula cannot find the referenced data
#NAME?Text in the formula is not recognised
#NULL!A space was used in formulas that reference multiple ranges; a comma separates range references
#NUM!A formula has invalid numeric data for the type of operation
#REF!A reference is invalid
#VALUE!The wrong type of operand or function argument is used
Correct the formula accordingly

Rather than trying to remember all the error codes, you can learn about them when they appear. Notice that when we click on the cell with the error message that there is a small box with a yellow diamond and an ‘!’ to the left of the cell. When we scroll the mouse over it an arrow appears, as does a text box explaining the error. 

https://library.barnard.edu/sites/default/files/images/inline/function30a.jpg

If the by-line box doesn’t clarify what the problem is, you can click on the arrow and get a number of options. 

https://library.barnard.edu/sites/default/files/images/inline/function31.jpg

Sometimes selecting Show Calculation Steps… will be enough to help you see where the mistake is.

https://library.barnard.edu/sites/default/files/images/inline/function32.jpg

This formula is trying to divide a number by text.  This can easily be resolved.

Formula and Function Errors

While the previous errors are derived from the information in the cells referred to in the function or formula, other errors occur because of a problem in the function (or formula) itself. 

Circular Reference

It happens to the best of us. Sometimes when you are selecting a range of data for a function you misjudge where the dataset ends and quickly press ENTER. Then you see this:

The range of data for calculating the average includes the cell (D8) where the average function is written. By clicking OK Excel 2010 gives us this:

To fix this:.

  1. Click on the cell (D8) and edit the data range in the formula bar OR
  2. Double click on the cell (D8) and edit the data range in the cell OR
  3. Delete the function and start over- making sure to stop the range selection at D7 (or typing D2:D7).