Subtitles of the Movie

There are special functions for performing calculations on a database. Here we're looking for the maximum order from any given department. We'll also find how many orders are from a given department and the total amount of those orders added together. These can be found using special database functions. First we'll look for the maximum order. We'll use the paste function wizard - make sure that database category is selected. As you can see there are quite a few functions that pertain strictly to a database. We'll want to look up the D-Max. Then there are 3 areas to fill in. Under the first area you select the list of your entire database. In this case cell A1 through G26. Once your entire list is selected you then fill in the field area. Since we're looking for the total order amount for each person to calculate on, this is the column heading you select for field. This is basically the value that gets returned after it searches the list and performs the calculation. In the criteria area we're going to select our column headings over in this criteria area and up one row underneath those columns. Once these 3 areas are filled in, click ok. The value we see here is the maximum order for anyone in the sales department. If I change this department to HR for instance, my maximum order changes, since we have someone from that department who placed a larger order. Let's try this same idea with our number of orders and total amount of orders - but this time we'll build the function ourselves. Notice, here we have the D-max, which was inserted for us using the function wizard. There are 3 parts of the formula in the parentheses. The first part is the list A1 through G26. This will be the same for all 3 of our functions. Next is the field name that we want returned. Cell E1 contains the field name for total orders. This is always the field we will use - so this part of the formula will not change. The last part of the formula, our criteria area, is always going to be in the same place - so this will not change either. The only thing that needs to change about the formula is the name of the formula. Since in this case we want the number of orders, and in this case the total amount of the orders. A quick and easy way to get the same formula copied down is to use auto fill. But to ensure that none of these cell references change as we copy it down we're going to select them, and then apply an absolute reference using the F4 key. We'll then copy this formula down into the next 2 cells. Going into each of these cells then we will simply change the name of the function. In this case we don't want a maximum, but we want a count of how many orders. So we'll use the D count function. And then the total amount of orders we want a sum of the orders from each department - so we'll change d-max to d-sum. Now all we have to do is come up and change our department and hit enter to see the maximum order, the number of orders from that department, and the total amount of those orders put together for this particular department. Database functions are a little longer than a normal function because it needs the database list, the name of the field to pull the information from, and a criteria area so you can look for values within a certain range of the list. field to pull the information from, and a criteria area so you can look for values within a certain range of the list.