Complex Formulae in Excel

-
Complex Formulae in Excel
A complex formula has more than one mathematical operator, such as 6+7*9. When there is more than one operator in a formula, the order of operations tells Excel which operation to calculate first.
The Precedence order of operators
Excel calculates formulas based on the following order of operations:
1.      Operations enclosed in parentheses
2.      Exponential calculations (3^2, for example)
3.      Multiplication and division, whichever comes first
4.      Addition and subtraction, whichever comes first
A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Adorable  Student.

Solution to this expression is as follows:

How to Create a complex formula

Let us show how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of sales tax for a catering invoice. To do this, we'll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.

 Excel follows the order of operations and first adds the values inside the parentheses: (44.85+39.90) = $84.75. It then multiplies that value by the tax rate: $84.75*0.075. The result will show that the sales tax is $6.36.

It is especially important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the result is incorrect. Parentheses are the best way to define which calculations will be performed first in Excel.
To create a complex formula using the order of operations:
In our example below, we will use cell references along with numerical values to create a complex formula that will calculate the total cost for a catering invoice. The formula will calculate the cost for each menu item and then add those values together.
1.      Select the cell that will contain the formula. In our example, we'll select cell C4.
2.      Enter your formula. In our example, we'll type =B2*C2+B3*C3. This formula will follow the order of operations, first performing the multiplication: 2.29*20 = 45.80 and 3.49*35 = 122.15. It then will add those values together to calculate the total: 45.80+122.15.
3.      Double-check your formula for accuracy, then press Enter on your keyboard. The formula will calculate and display the result. In our example, the result shows that the total cost for the order is $167.95.
Complex Formula

You can add parentheses to any equation to make it easier to read. While it won't change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.
Excel will not always tell you if your formula contains an error, so it's up to you to check all of your formulas.

Comments

Post a Comment

Most Popular Posts

Functions in Excel

-
Image
Functions in Excel 2013 A  function  is a  predefined formula  that performs calculations using specific values in a particular order. Excel includes many common functions that can be useful for quickly finding the  sum ,  average ,  count ,  maximum value , and  minimum value  for a range of cells. In order to use functions correctly, you'll need to understand the different  parts of a function  and how to create  arguments  to calculate values and cell references The parts of a function A function must be written a specific way,  called   syntax . The basic syntax for a function is the  equals sign (=) , the  function name  (SUM, for example), and one or more  arguments . Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20. Working with arguments Arguments can refer to both  individual cells  and  cell ranges  and must be enclosed within  parentheses . You can include one argumen
Read more

Mixed Supply and Composite Supply under GST

-
Mixed Supply and Composite Supply under GST Let us understand first the term   “supply” simply means all forms of supply of goods/ services. It is carried out during the course of business. There are two types of supplies under GST, Composite and Mixed. Both of these supplies help to determine the correct GST rate and provides uniform tax treatment under GST for such supplies. Bundled Supply A bundled supply means supply of goods and/or services in a combination. This concept was mainly found in service tax where a bundled service meant a combination of two or more services. Here are some ways to identify whether it is a naturally bundled(Composite) or mixed one them: 1.      If buyers mostly expect such services to be provided as a package, then the package will be treated as naturally bundled. For example, most business conventions look for combination of hotel accommodation, auditorium and food. 2. If most of the service providers in the industry provide a package o
Read more

Learn How to Create Company in Tally.ERP9

-
Image
Company Creation Tally.ERP9 Tally.ERP 9 is not a just an  accounting software  but its a complete ERP solution to business organisations such as managing Accounts, Inventory, Payroll and taxations (GST)   compatibility from version Tally.ERP 9 release 6. How to create a company in Tally.ERP 9? To create a company in Tally.ERP 9, Double Click on   tally icon on the desktop. Press Alt+F3 -> Create Company Details to be filled in company creation screen Directory The tally directory will store all data you entered in Tally ERP 9. By default, the data storage will be inside the installation folder. i.e. c:\users\public\tally.erp9\data Name In this field, we are required to type the name of the company, for example. XYZ INC. Primary mailing details Mailing name In this field, we are required to type the name of the company, for example. XYZ INC. Address In this data field, we are required to enter the address of the company. Country Here we
Read more