Sign in or start a free trial to avail of this feature.
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.
To explore more Kubicle data literacy subjects, please refer to our full library.
Writing a Formula (00:21)
To create a formula, you start with the equals symbol, then the formula. For example, typing =D2*E2 in a cell multiplies the values in the cells D2 and E2. When the formula is complete, press Enter and the result of the formula will be placed in the cell.
You can type out the cell references, but this is quite slow. Instead, when you’re typing a formula, you can navigate to a cell using the keyboard arrows. To enter a formula in multiple cells, first type the formula in one cell. Then copy the formula, select the remaining cells and use the paste formulas command.
The SUM and COUNT functions (01:52)
To add a number of cells together, we use the SUM function, which adds all the cells in a selected range. For example, SUM(F1:F24) adds together all the numbers in the column of cells from F1 to F24. An example of this would be finding the total revenue from a list of orders.
The COUNT formula counts the number of cells within a range that have a number in them. For example, COUNT(F1:F24) returns the number of cells between F1 and F24 that contain a number. An example of this would be finding the number of orders in a list.
If you want to count cells with text instead of numbers, use the formula COUNTA instead. COUNTA counts the number of cells in a range containing any kind of text, including numbers.
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.
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, multiplied 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 2000 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 keyboard arrows. So, in the case of the second row I can type equals, then navigate to D3, multiplied 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 okay.
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 border around each cell that is included in the formula. This is particularly useful for more complicated that may references five or six cells, which we'll be creating in later courses. Let's now perform some additional calculations on our data sets.
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 equals F2 plus F3, etc, this is gonna take quite a long time, so instead, we'll use the sum function. The sum function simply adds together all the cells within a selected range. We'll type equals 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 function. So I type equals 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 number 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, equals count A, and then I'll select the product type column, close the brackets, and press enter.
Count, by itself, isn't terribly useful, but what is useful, is when we want to count the number 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. Countif, along with sumif, will allow us to perform conditional arithmetic on our data sets. I'll show you how to use these functions in the next lesson.