You may have experience working with formulas that contain a single operator, but do you know how to calculate complex formulas containing more than one operator? Mathematics has a set of rules on how formulas are interpreted, and Excel follows that protocol.
Keep this order in mind when building formulas to ensure accurate results:
(1) Parentheses;
(2) Colon, space, comma;
(3) Negative numbers;
(4) Percentages;
(5) Exponents;
(6) Multiplication and division;
(7) Addition and subtraction; and
(8) Less than, less than or equal to, greater than, greater than or equal to, equal, not equal to.
Note: When a formula includes operators from the same level (e.g., multiplication and division, both level 6), Excel evaluates them from left to right.
How does it work? Applying this order, Excel calculates the equation =(B4+C4)/(B5+C5)*100 as follows: First adding cells B4 and C4, then adding B5 to C5, dividing the first sum by the second sum, and then multiplying the result by 100.
Another Idea: Name That Data!
Name data ranges can help you build formulas more easily by allowing you to apply names—i.e., plain English titles—to a constant value or a cryptic groups of cells.
How does it work? Rather than selecting all the numbers in your Prices column (e.g., cells B4:B12), simply name this group of cells “Prices.” This way, when you’re adding everything in this column, the formula will be =SUM(Prices) as opposed to the less clear =SUM(B4:B12).
This method is even more helpful as you create increasingly complex formulas, using multiple ranges of data—and especially when multiple people will be working with and trying to make sense of the data.
How to name a range: To name a range, select the range. For our example, we selected cells B4:B12. Click once on the Name box, located to the left of the Formula Bar. Type the name of your range and click [Enter].
To use your named range in a formula, begin typing the name of the range until it appears below the formula bar. Double-click on the name to insert it in your formula.
Improve Efficiency By Using Excel Tools and Functions
Excel is one of the most common business tools, but its power is still untapped by many professional users. To be truly proficient in Excel, you must be familiar with its many features to manage data efficiently. Don’t use it just to calculate data—tackle your powerful data management needs with Excel!
Try this: Register for the power-packed webinar “Excel for Banking and Finance Professionals” by expert speaker Thomas Fragale.
Thomas will explore many ways to use Excel formulas, functions, and other tools to make spreadsheets more efficient for banking and financial professionals. Thomas will also show you ways to retrieve important information from your data more quickly and efficiently. With just a bit of know-how, you will be able to take advantage of the power of Excel to sort and filter data and use its many expanded functions to analyze the data in any list, regardless of size.
