Excel contains a special function for repeating text named REPT which stands for “repeat.” If I expose these arguments on the worksheet, I can quickly add a few more examples. In each case, Excel simply repeats the text you provide as many times as you specify. So, what can you use the REPT function for? Well, one thing you’ve got the option to do with REPT is build a simple histogram....
Here we have test score data for a group of students. Let’s use thestatus barto look at the information shown in column J. First note that the status bar can be customized to display different information. Just right-click to show available options. You’ll find the statistical information grouped together. Let’s look first at Count. Count will count all cells that contain values, numeric or otherwise. Using Count, we can see that there are 20 people in this list and 99 test scores total....
Abstract Transcript TheSUM functionis a simple function that adds up numbers. Let’s take a look. The SUM function sums numeric values. SUM simply ignores blank cells. The formula =SUM(D7:E9) will return “1,350”. Like blanks, SUM ignores text values. In addition to ranges, SUM can work with individual cells, too. Just separate each additional item with a comma. As you enter these values in, you’re free to combine both ranges and individual cell references....
Let’s take a look. TheSUMIF functionsums cells that satisfy a single condition that you supply. It takes three arguments: range, criteria, and sum range. Note that sum range is optional. If you don’t supply a sum range, SUMIF will sum the cells in range instead. Excel then returns “30” because two cells in the range contain “15”. If I temporarily enter another 15, that result will change. you could add logical operators to the criteria....
Let’s take a look. SUMIFS has three required arguments:sum_range, criteria_range1,andcriteria1. After that, you could enter additional range and criteria pairs to add additional conditions. When you use a number without an operator, there’s no need to use double quotes. Excel returns “30”. The next example also requires two pair of range and criteria. One to test for numbers equal to 10, and the other to test for a color of blue....
Abstract Transcript In this lesson we’ll look at the Time format. Like the Date format, the Time format includes a number of built-in options for displaying time. Let’s take a look. Here we have a set of times in column B of our table. The asterisk means that this time format is controlled by the regional configs on the computer. This means that this time format may look different on different computers, depending on regional options....
Abstract Transcript VLOOKUPis one of the most important lookup functions in Excel. Let’s take a look. Here we have a list of employees in a table. By using named ranges, our lookup formulas will be easier to understand and easier to copy. I’ll use “869” for Julie Irons. Now let’s use VLOOKUP to get the first name. In this case, the lookup value is the named range “id.” The table is our named range “data....
A classic case is using VLOOKUP to find a commission rate based on a sales number. Let’s take a look. I’ll call it “commission_table”. This will make our VLOOKUP formula a lot easier to read and copy. Now let’s use VLOOKUP to get the first commission rate for Applebee. The table is our named range “commission_table”. For column, we need to provide a number for the column that contains the commission rate....
Abstract Transcript In this video we’ll look at how to use theVLOOKUP functionwith wildcards. This is useful when you want to allow a lookup based on a partial match. Let’s take a look. Here we have the employee list we’ve looked at previously. This time, however, notice that the ID column has been moved into the data. We’ll cover that in a separate video. However, we can still use VLOOKUP in this case to match on the last name....
In this worksheet we have two tables. In the first table, we have order data. you could see that we’ve got a date, customer id, product, and total. In a second sheet we have customer data. We’ve got first and last name, street, city, state and so on. First, I’llcreate a named rangefor the customer data. I’ll use this name inside VLOOKUP in just moment. You’ll see how this works in just a minute....
On this worksheet, I have a table that contains employee data, named Table1. Then I’ll add some formatting, and an ID value so I have something to match against. Now I’ll write the first VLOOKUP formula. Fortable_array, I want the lookup table itself, Table1. In this table, the ID is the leftmost column, so we can get any value. For column ID, I need 2, since the first name is the second column in the table....
A row or column of related numbers plotted in a chart is called a data series. Excel can plot data arranged in rows or columns. When you create a new chart, Excel makes a decision about data series. When the data contains more rows than columns, Excel uses columns for the data series. When the data contains more columns than rows, Excel uses rows for the data series. For example, here we have chocolate sales data for 6 months....
Let’s take a look. Here we have a set of random dates in column B. Using built-in functions, I’ll extract the year, month, and day from each date. First, I’ll extract the day value using a function calledDAY. Day takes just one argument: a valid date in serial number format. When I give it the value in column C, it just returns the day from the date. To get the month value, I need to use theMONTH function....
