Working with complex Excel formulas

Creating more complex formulas in Excel

Once you’ve mastered creating basic formulas in Excel, you can perform more complex calculation using formulas that contain more than one mathematical operation. For example, you may wish to know the value of cell A1 added to cell B1 then divided by cell D1.

Excel follows the accepted order of mathematical operations to know which operation to calculate first. So, the calculation 400+100/25 will be rewritten by Excel as =(400+100)/25 or, more commonly, using cell references such as =(C3+D3)/E3.

Order of Excel operations

  1. Parentheses.
  2. Exponents (to the power of).
  3. Multiplication and division, whichever comes first.
  4. Addition and subtraction, whichever comes first.

Create a complex formula in Excel

As an example, the steps below show how to create an Excel formula using cell references to work out the amount of VAT payable on goods for sale.

Step 1  Click the cell where you want the calculation to appear.

Step 2  Type the equal sign (=).

Step 3  Type an open parenthesis, then click the cell with the first value to be used in the formula.

Step 4  Type the first mathematical operator (for example, the division sign) and value – in this example 100 – and then type a closed parenthesis.

Step 5  Type the next mathematical operator (for example, the multiplication sign).

Step 6  Type the next value in the formula.

Step 7  Click Enter to calculate your formula.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>