Welcome to this session Excel formulas and functions in this module we will learn how to perform basic calculations using formulas and functions.
The backbone of excel is its ability to perform calculations. Formulas are mathematical expressions that you build yourself you need to follow proper math principles in order to obtain the expected answer.
You may either watch the video below or follow the steps by reading the text in this blog located below.
To begin creating formulas and functions in Excel, you must understand basic math principles.
Basic arithmetic operators include the Plus Sign (+) which adds value, the Minus Sign (-) which subtracts value the Asterisk (*) which multiplies values the Forward Slash (/) which divides values the Percent Sign (%) which finds the percentage of a value and the carrot which finds the exponential value.
Basic comparison operators include the Equal Sign (=) which equates value, the Greater Than Sign (>) which indicates that one value is greater than the other, and the Less Than Sign (<) which indicates that one value is less than the other.
Let's review some of these.
Excel is like a calculator with simple formulas. Excel can perform calculations on numbers using operators we just talked about and functions such as Sum to add numbers.
The formula always starts with the Equal Sign(=) ,then type a number, then the plus sign, then another number and press Enter and the cell will display the results.
You can add many numbers this way, not just two. Instead of typing numbers in a formula, you can reference cells that contain the numbers to make adding easier and faster. It also gives you more more flexibility.
Let's create a formula that Adds the cells.
Type equals, click a cell, type the plus sign, then click another cell and press Enter.
If I change a number in one of the cells referenced, the results of the formula will automatically update.
When you double click a cell, you can see if the cell is a Formula. Double clicking or pressing F2 lets you see the contents of the cell. This places you in Edit mode for the cell. You can also see the contents in the formula bar here.
Subtracting is just like adding, but instead you use the minus sign, click a cell, then enter the minus sign, then another cell, and press Enter.
Multiplying and Dividing are similar.
To Multiply, use the Asterisk (*), press the equal sign, click a cell, then the asterisk, then another cell, then press enter.
To Divide use the Forward Slash (/), click a cell, press the forward slash, another cell, and then press enter.
AutoSum makes it easy to add adjacent cells in rows and columns.
You could type =A3 + A4+A5, etc, but that could be tedious with a large amount of data.
Instead, click the cell to the right of a row, or just below the column of where your data is housed, then on the Home tab click the AutoSum (Σ).
Confirm the formula is what you want to calculate and simply press Enter.
When I double-click the cell with the AutoSum you'll see it's a function. The syntax of the Sum function is =SUM(number1, number2, number3)
This formula is =SUM(A3:A6) which indicates a range of cells. It adds the cells from A3 through A6. If you try to do AutoSum here where there's missing data, it will only capture the adjacent cells with data, since row five is empty.
Excel doesn't recognize the entire column or group of adjacent cells to add cells and ranges of cells that aren't adjacent.
Type =SUM( and then hold the CTRL key and click the desired cells and ranges of cells, close parentheses and press Enter.
As a side note, the keyboard shortcut for AutoSum is ALT +.
Excel recognizes negative signs and it subtracts these numbers, even when using the Plus Sign for addition.
Excel uses the standard math order of operations you've probably heard PEMDAS, which stands for "Parentheses, Exponents, Multiplication, Division and last our Addition and Subtraction," all from left to right.
Excel understands and follows the order of operations, so when you write formulas, be sure to remember this rule as well.