Jun
21

How to do calculations in Excel

The tutorial shows how to do arithmetic calculations in Excel and change the order of operations in your formulas.

When it comes to calculations, there is almost noting that Microsoft Excel cannot do, from totaling a column of numbers to solving complex linear programming problems. For this, Excel provides a few hundred predefined formulas, called Excel functions. In addition, you can use Excel as a calculator to do math - add, divide, multiply, and subtract numbers as well as raise to power and find roots.

How to do calculations in Excel

Making calculations in Excel is easy. Here's how:

  • Type the equal symbol (=) in a cell. This tells Excel that you are entering a formula, not just numbers.
  • Type the equation you want to calculate. For example, to add up 5 and 7, you type =5+7
  • Press the Enter key to complete your calculation. Done!

Instead of entering numbers directly in your calculation formula, you can put them in separate cells, and then reference those cells in your formula, e.g. =A1+A2+A3

The following table shows how to perform basic arithmetic calculations in Excel.

Operation Operator Example Description
 Addition + (plus sign) =A1+A2 Adds up the numbers in cells A1 and A2.
Subtraction - (minus sign) =A1-A2 Subtracts the number in A2 from the number in A1.
Multiplication * (asterisk) =A1*A2 Multiplies the numbers in A1 and A2.
Division / (forward slash) =A1/A2 Divides the number in A1 by the number in A2.
Percent % (percent) =A1*10% Finds 10% of the number in A1.
Raising to power (Exponentiation) ^ (caret) =A2^3 Raises the number in A2 to the power of 3.
Square root SQRT function  =SQRT(A1) Finds the square root of the number in A1.
Nth root ^(1/n)
(Where n is the root to find)
=A1^(1/3) Finds the cube root of the number in A1.

The results of the above Excel calculation formulas may look something similar to this:
Excel calculation formulas

Apart from that, you can combine values from two or more cells in a single cell by using the concatenation operator (&) like this:

=A2&" "&B2&" "&C2

A space character (" ") is concatenated in between cells to separate the words:

Concatenating values from several cells

You can also compare cells by using logical operators such as "greater than" (>), "less than" (<), "greater than or equal to" (>=), and "less than or equal to" (<=). The result of comparison are logical values of TRUE and FALSE:

Comparing cells in Excel using logical operators

The order in which Excel calculations are performed

When you do two or more calculations in a single formula, Microsoft Excel calculates the formula from left to right, according to the order of operations shown in this table:

Precedence Operation
1 Negation, i.e. reversing the number sign, as in -5, or -A1
2 Percent (%)
3 Exponentiation, i.e. raising to power (^)
4 Multiplication (*) and division (/), whichever comes first
5 Addition (+) and subtraction (-), whichever comes first
6 Concatenation (&)
7 Comparison (>, <, >=, <=, =)

Since the order of calculations affects the final result, you need to know how to change it.

How to change the order of calculations in Excel

Like you do in math, you can change the order of Excel calculations by enclosing the part to be calculated first in parentheses.

For example, the calculation =2*4+7 tells Excel to multiply 2 by 4, and then add 7 to the product. The result of this calculation is 15. By enclosing the addition operation in parentheses =2*(4+7), you instruct Excel to add up 4 and 7 first, and then multiply the sum by 2. And the result of this calculation is 22.

Another example, is finding a root in Excel. To get the square root of, say, 16, you can use either the formula =SQRT(16) or an exponent of 1/2: =16^(1/2)

Technically, the above equation tells Excel to raise 16 to the power of 1/2. But why do we enclose 1/2 in parentheses? Because if we don't, Excel would raise 16 to the power of 1 first (an exponent operation is performed before division), and then divide the result by 2. Since any number raised to the power of 1 is the number itself, we would end up dividing 16 by 2. In contrast, by enclosing 1/2 in parentheses you tell Excel to divide 1 by 2 first, and then raise 16 to the power of 0.5.

As you can see in the screenshot below, the same calculation with and without parentheses produces different results:
Changing the order of operations in Excel

This is how you make calculations in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

5 Responses to "How to do calculations in Excel"

  1. Dave says:

    Could you show us how to build a financial model in Excel?

  2. Rae says:

    Hi, I have a 2 part question.
    1st, upon looking at my formula to try to reverse engineer it, I noticed the math seems a bit weird. I have this formula to calculate the amounts of 2 components based on the ratio and the total weight (tw=total weight, ra=ratio a, rb=ratio b, aw=weight of part a, bw=weight of part b. I'll use these instead of the cell #'s here). My formula looks like this bw=tw*(rb/(ra+rb)) and the formula for the other part is ra=tw*(ra/(ra+rb)).
    the issue is that when I do out the math in steps, based on order of operations, I get a decimal, but the formula gives me a whole #. Why don't I get the same result?
    Part 2; how can I create 2 formulas that will solve this backwards, as in, plug in the weight of a or b and get what my total weight and the weight of the other part as a result?
    Thank you so much! I've been cracking my brain open over this for days and searching all over the web for an answer that I don't know an easy question to!!

  3. Rae says:

    One last point, sorry to go on:
    to illustrate, I'll plug in 66 as the tw, 100 as ra and 10 as rb. in the formula I get 60 and 6 for a and b, which seems like it did not respect the parenthesis, as 66*(100/(100+10))= 66*(100/110)= 66*0.9090909091= 60.0000000006, while 66*100/(100+10)=6600/110=60 which is what the formula gives me.

  4. andrews says:

    You are so good, I've learn a lot from you expecially the capitalization and the calculations.

    good programmer keep it up

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard