If you’re comfortable working with Excel formulas, you probably don’t need to read this little article. It covers pretty basic stuff. But if you’re new to Excel or rusty about formula construction or maybe just have never “pushed the envelope” so to speak, it's a good idea to buff up your formula construction skills. Such buffing will make it easier to work with Excel in general. And such buffing will really simplify working with Excel’s data statistical and statistical functions in your analysis.
With that in mind, then, here are the basics every budding data analyst needs to know:
Excel provides five basic arithmetic operators
Excel provides five basic arithmetic operators for the five basic calculations you’ll make: addition, subtraction, multiplication, division and exponentation.
The following table provides examples of these operators in action. Probably the only odd thing that appears in the table is the exponentiation operator, which raises some value to an exponential value. The value 10 raised by the exponent 2, for example, is the same thing as 10 times 10. The value 10 raised by the exponent 3, as another example, is the same thing as 10 times 10 times 10.
| Operator | What It Does | Example | Result |
|---|---|---|---|
| + | Adds | =10+2 | 12 |
| - | Subtracts | =10-2 | 8 |
| * | Multiplies | =10*2 | 20 |
| / | Divides | =10/2 | 50 |
| ^ | Exponentiates, or raises a value by an exponent | =10^2 | 100 |
Standard rules of operator precedence apply
If you construct a formula that uses multiple arithmetic operators, standard rules of operator precedence apply: Any exponential operations occur first. Multiplication and division occur next. And subtraction and addition come last. And note that if there are multiple operators of the same precedence, Excel works from left to right
In the formula =1+2-3*4/5^6, for example, here’s what happens:
Excel first raises the value 5 to its 6th power, returning the result 15625.
Excel next multiplies 3 by 4 to get the result 12. And then Excel divides the value 12 by 15625, returning the value 0.000768.
Excel finally adds 1+2 and subtracts 0.000768, returning the value 2.999232.
Copy the above formula to an Excel worksheet cell to test the math and play with the results.
Parenthesis marks override precedence rules
You can use parenthesis marks to override the standard rules of operator precedence.
Take the formula =1+2-3*4/5^6, which I just described, as an example. If I rewrite the formula using a couple of set of parenthesis marks, I can greatly change the order in which the calculations occur.
The formula =((1+2)-(3*4/5))^6 first solves the arithmetic inside the deepest parenthesis marks. Here’s what happens:
Excel adds 1+2 to get 3
Excel multiplies 3 by 4 to get 12 and then divides 12 by 5 to get 2.4
Excel next takes 3 and subtracts 2.4 to get .6
Excel finally raises .6 to the sixth power, return 0.046656.
Cell addresses work in formulas
The formulas in the preceding paragraphs use values. Some formulas use little chunks of text.
But you should know that you can also use cell addresses in place of formulas. For example, if cells A1 and B2 in your worksheet hold the values 4 and 20, the formula =A1*B1 equals 80.
You can, by the way, mix and match values and cell addresses, too. Again, assuming that cells A1 and B1 hold the values 4 and 20, each of the following formulas returns the same result:
=A1*B1 =4*20 =A1*20 =4*B1
You can build bigger formulas with functions
A quick point: You can build big, complicated formulas. And those formulas can use all of the operators here and functions too. A simple example of this “formulas built with functions” approach might look like this:
=SUM(1,2)-3*4/5^6
And you can build way, way longer formulas by using a bigger list of inputs and a bunch of different functions.
Go Boolean
Here are a couple of things you should know about going Boolean in your formulas.
First, you can use Boolean logic expressions in your formulas. A Boolean expression returns the value 1 if the expression is true and 0 if the expression is false.
For example, the expression =((2+2)=4) returns the value 1 because as it happens, 2+2 equals 4. And by the way, the formula =((2+2)=4)*25 returns the value 25 because the Boolean expression value of 1 times 25 equals 25.
The expression =((2+2)>4) in comparison returns the value 0 a because 2+2 does not return a value greater than 4. And just to leave no doubt, the formula =((2+2)>4)*25 returns the value 0 because the Boolean expression value of 0 times 25 equals 0.
Note: Excel may display the result of a Boolean expression as TRUE if the value equals 1 and as FALSE if the value equals 0.
Boolean operators have precedence too. The lowest possible precedence, in fact. So if you constructed a formula that used Boolean logic, the actual comparison operator would get used last. As the examples in the preceding paragraphs show, you can use parenthesis marks to override standard precedence.