In this practice, you will learn how to organize data in an Excel spreadsheet by rank.
Use this workbook for the chapter.
Maximum and Minimum
Looking at the table, notice the different columns: employee, position, age, college GPA, and salary. Let's suppose that we wanted to find the oldest age in this list of employees.
We could scan the list with our eyes and find the answer that way, but suppose the list was much longer, perhaps thousands of rows. You wouldn’t be able to do that. However, there is a way to have Excel calculate the answer for us.
In this practice, we will explore how to find the maximum age, the minimum, the third-largest, and the fourth smallest. Type these into 4 cells in the same column, then copy and paste the headers for Age, College GPA, and Salary in the next three columns. (See Figure 7.1)
Let’s begin with the maximum age. Complete the following steps.
- Type =MAX into the cell, followed by an open parentheses.
- Select all the values in the Age column, then close the parentheses and push Enter.
This will automatically calculate the maximum age from the selected range. Your formula should look similar to the one in Figure 7.2.
To calculate the minimum age, do the following in the cell beside the one labeled Min.
- Type =MIN into the cell, followed by an open parentheses.
- Select all the values in the same range as before, then close the parentheses and push Enter.
This will automatically calculate the minimum age from the selected range.
Large and Small Functions
To find the third-largest, there is a formula called “large” that allows you to find within a given array, what largest number you would find. This is represented in the formula as k. This variable tells Excel which number “largest” you’d like to identify. For example, in the circumstance of third largest, k would equal three. (See Figure 7.3)
To calculate the third-largest, do the following in the cell beside the one labeled Min.
- Type =LARGE into the cell, followed by an open parentheses.
- Select all the values in the same range as before, replace the spot for k with 3, then close the parentheses and push Enter.
This principle is also applicable to finding, for example, the fourth smallest. This formula is called “small.”
- Type =SMALL into the cell, followed by an open parentheses.
- Select all the values in the same range as before, replace the spot for k with 4, then close the parentheses and push Enter. (See Figure 7.4)
Matching the Format
Now, because we didn't anchor the references for these functions, we can drag them across to calculate the maximum and the minimum for GPA and salary as well. Since we want the number formats to match, use the format painter.
- Select one row of the original age, GPA, and salary.
- Select the format painter on the upper left-hand side. (See Figure 7.5)
- Drag your mouse over the new age, GPA, and salary. (See Figure 7.6)
- This will automatically adjust the format to match that of the original reference. (See Figure 7.7)
One slight alteration we can do to our formulas to make them even more dynamic is that if we didn’t want to be “hard-coding” or just typing in, for example, a three for “third largest” we can reference an additional cell.
- Copy the cells with the =LARGE and =SMALL functions, and paste them below. (See Figure 7.8)
- Notice that the pasted cells are now referencing the wrong set of cells.
- Delete the incorrect information.
- Off to the side, type a 3 beside the 3rd Largest cell, and a 4 beside the 4th Smallest cell.
- Type =LARGE in the cell beside the 3rd Largest, choose your array, (which is just all of the ages,) and then reference the 3 cell. (See Figure 7.9)
- Hit F4 to anchor the formula so that as you drag and copy it over, it won't move.
- Repeat with the =SMALL function.
- Drag over to the right, hover over the icon in the corner, and choose Fill Without Formatting to maintain the number formats it had previously.
- Now, if instead of the third-largest we'd like to see, for example, the fifth-largest, we can change the 3 to a 5, and the referenced information will change along with it.
- Manually change the label to match, from third largest to fifth largest.
We can also create a numbered list to rank the items from smallest to largest.
- You can manually type and drag a list of, for example, 1–8, or you can use the fill series option as learned in a previous video.
- Then, type =LARGE, select the array, and then anchor it, or make it an absolute reference, by pressing F4.
- Then, for k, (which rank we want), we want the first largest, so you will select the number 1 from the list you previously created. (See Figure 7.10)
- You can then drag it down, and because you have attached it to the proper references, it should fill in with the corresponding numbers.
- Label the list with the word Large.
You’ll notice that if you attempt to drag over the ages for the GPA and salary, it will give you an error. This has to do with the anchoring because rather than being referenced to the number 1, k is now referenced to 52.
To anchor just the cell, you will need to anchor to just the row, rather than the column and the row.
- Change your formula so that only the row is being referenced. (See Figures 7.11 and 7.12)
- This makes it so that it won’t move when you move cells down, but it will move columns to the right if dragged over.
- Use the format painter to keep the data looking consistent.
You could do the same exercise with the small formula.
For our next example of ranking, let’s say that instead of creating a list off to the side, we want to add a column into our data to give it a rank relative to the list and will rank every single item in the list.
- Select columns G through L, right-click, and select Insert.
- This will shift all the formulas to the right so you don’t lose them but have a space to work in.
- Label columns for ranking each of the elements.
- The ampersand gives you the ability to combine terms.
- Type =”Rank ”&
- Select D2, the cell labeled Age
- Press Enter.
- Drag this cell over, and the information should fill in until all the columns are labeled properly.
If there's more than one age in the list that are the same and you choose RANK.AVG, the rank assigned to both of them will be the average of their rank.
- Select the first cell under Rank Age, G3, and type =RANK.AVG.
- Select the corresponding Age cell in the same row, D3, and type a comma after.
- Select the entire Age array, and anchor just the rows.
- Type a comma, and it will give you the option between descending or ascending. Choose descending (the largest number gets the highest rank).
- Note that when the formula is complete, it will rank the age with a dollar sign. Update the format by selecting Number rather than Currency. Remove the decimals.
- Drag this cell all the way down.
- Drag that new array across.
- If your anchoring is set up correctly, each cell should correlate with the number in the same row in the assigned column of that information.
- If you expand the decimals, note that some ranks contain a .5 at the end. This is because the RANK.AVG formula is being used. It is averaging the two places to give you together and giving them the average of the two ranks.
- For example, the average of place 10 and place 11 would be 10.5.
If you choose rank.eq, instead of averaging the two ranks that happen to be the same, it will take the highest of the two and it will assign that rank to both of them.
- Copy the formulas from the rank titles by referencing them.
- Add a note in the column above that says AVG for the averaged ranks, and EQ for the ones you are about to complete.
- Color code them using the fill colors.
- Set up the rank formula again, but this time, choose RANK.EQ instead of RANK.AVG.
- Don’t forget to anchor only the rows and not the columns.
- Copy the cell over to the rest of the columns.
- Copy these three cells down to the rest of the rows.
- Change the formatting from Currency to Numbers.
- Notice that there’s no .5 in the decimal places this time because the higher rank of the two that match is being used.
- For example, if two values at the 10 and 11 place were the same, they would both become 10.
That’s how the RANK function works, along with the MIN, MAX, LARGE, and SMALL functions.