1. Basic Arithmetic Operations
Apart from the basic arithmetic operators (+ - x /), Excel has in-built functions such as SUM and COUNT, which I'll show you in this lesson.
Writing and pasting simple equations
- To write an equation, type '=' and then write the formula
=SUM(range): Adds all the cells in the selected range.
=COUNT(range): Counts the number of cells (with numbers) in the selected range.
CTRL + C: Copy cells.
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
F2: Jump back inside a formula
CTRL + →: Move to the last cell in the data region
Formulas are the real power behind Excel, but unfortunately most users don't know how to use them properly.
In this lesson, we're going to begin by exploring basic arithmetic functions, such as multiplication and division, before progressing to some in built Excel functions, such as SUM and COUNT.
As you probably know, you type equals to create a formula.
To calculate the order dollar amount for the first order, we type equals D2, which is the product price, multiply by, which is the star, E2, which is the order quantity, and then press ENTER to calculate the order dollar amount.
And we can see that 40,000 equals 2,000 multiplied by 20.
When we have cells and formulas we can either type the cell reference like I did in the first row, or we can navigate to the cell using the keyboard arrows.
So in the case of the second row, I can type equals, then navigate to D3, multiply by, and then navigate to E3, and press ENTER, complete the formula.
Obviously I don't want to type in the formula for the remaining rows, so we'll paste the formula.
First, we'll copy the top cell.
We'll then select the remaining cells, and then we'll paste formulas with ALT + E S, and then select F for formula and click OK.
And now we have the order dollar amount for every order.
Sometimes it can be hard to visualize which cells are in a formula.
But we can perform a quick check by pressing the F2 button.
This creates a colored boarder around each cell that is included in the formula.
This is particularly useful for more complicated formulas that may reference five or six cells, which we'll be creating in later courses.
Let's now perform some additional calculations on our data set.
If I scroll down to the bottom, I have some metrics that I'd like to calculate written down here.
Let's start with Total revenue for the three month period.
Total revenue is obviously the sum of the order dollar amount column.
And we could type in "=F2,+F3", etc., but this is going to take quite a long time, so instead we'll use the SUM formula.
And the SUM formula simply adds together all the cells within a selected range.
We'll type “= sum”, then open a bracket, then we'll use the arrows to select the range, and then we'll close the bracket and press ENTER.
And this calculates a total of $792,000 for the three month period.
To calculate the number of orders, we use the COUNT formula.
So I'll type “= COUNT”, and then I'll select a range of cells that includes every order.
And I'll then close the bracket and press ENTER.
And obviously, the number of orders, 23, equals the last order number we have on the left hand side.
One thing to watch with COUNT is that it only counts numbers and not text.
If we use the product type column in the last formula, it wouldn't actually work.
If you'd like to count cells with text, use COUNT A.
And I'll show this with an example, "=counta", and then I'll select the product type column, close the bracket, and press ENTER.
COUNT by itself isn't terribly useful.
But what is useful, is when we want to count the number of orders for a particular product, or from a particular state, or even for a particular week or month.
And we can do this using the COUNT IF command.
COUNT IF, along with SUM IF, will allow us to perform conditional arithmetic on our data set.
I'll show you how to use these functions in the next lesson.