Try using ROWS and MAX Functions
Posted on January 9, 2018 by Julie Duncan
There are hundreds of functions in Excel, but the ones you use will depend on the type of data your workbook has and what you need to analyze. You probably don’t need to know each of them, but the ROWS and MAX functions can be especially helpful – especially when you want to find unique values or largest numbers quickly in your spreadsheet.
Let’s see how you can use them to your advantage.
Find unique values: When you need to determine the unique values in a list of data, where some items repeat, trust the ROWS function.
How? Let’s assume your worksheet lists attendee registration information for your company’s recent training session, and you’d like to know how many states (or countries) were represented at your training. A quick Excel filter will provide a list of every unique entry.
Follow these steps to get started:
1. First, you’ll need to ensure that all locations used the same spelling or abbreviation. When collecting data, ensure that attendees select from an exclusive list of options rather than typing in their own answers for their home state.
2. Once your data is ready, select the range of cells, including the column heading. In our example, the heading might be “State.”
3. Select the Data tab from the main ribbon, and in the Sort & Filter group, select Advanced. In the resulting Advanced Filter dialog box, select the Copy To Another Location check box. Click in the Copy To box, and then select a range of cells into which you’ll copy the results (the Advanced Filter dialog box automatically collapses as you perform this step).
4. Click on the Expand dialog button to return to the Advanced Filter dialog box.
5. Select the Unique Records Only check box, then click OK.
6. Each state name is listed in the location you selected. Click in the first empty cell below your new filtered list of states, and type =ROWS(I2:I29), where I2:I29 represents the entire range of your filtered list.
This ROWS function returns the number of unique states.
When to use the MAX function:
You can use the MAX function to return the largest number quickly and be certain that you didn’t overlook any values.
Try this:
Simply select a blank cell and type =MAX(B1:B25), where B1:B25 is the range of cells. Press Enter to return the largest value.
The arguments in your formula don’t have to be a range; you can select multiple cells and separate them with commas. For example: =MAX(A7,B2,F19).
Another idea:
You can even include a number as an argument to find out if any cells in your range exceed a certain value. The formula =MAX(B1:B25,49) will return the largest number from the range, or it’ll return 49 if 49 exceeds the value of each cell in the range B1:B25.
(This post first appeared in a ProfEd blog)
Yes. There is happiness in simplicity – even in excel data
