The MAXIFS function in Excel is a powerful tool that allows you to find the maximum value based on one or more criteria. In this tutorial, we will be using this function to find the topper female and male students in a class.
Step 1: Add Data to Your Worksheet
To get started, add the data for which you want to find the maximum values to your worksheet. In our case, we will add a list of student names, genders and grades.
Step 2: Set Up Your Criteria
Now you need to set up your criteria in order to find the maximum values based on gender. To do this, select an empty cell and enter “=MAXIFS(” followed by the range of data that includes the grades and then the criteria range with gender information.
Step 3: Enter the Criteria
Next, enter the criteria you want to use for the maximum value search.
To find the topper female, we will be using the MAXIFS function. In the formula bar, type =MAXIFS and then select the marks range (in this case, C2:C14). Next, select the female range wherever it is defined (B2 to B14). In the criteria_range1 field, enter “F” (which indicates the females). The formula will become =MAXIFS(C2:C14,B2:B14,“F”). Press Enter to get the result.
To find the topper male, we will use the same formula. In the formula bar, type =MAXIFS and then select the marks range (C2:C14). Next, select the male range (B2 to B14). In the criteria_range1 field, enter “M” (which means males). The formula will become =MAXIFS(C2:C14,B2:B14,“M”). Press Enter to get the result.
To find the topper female in a specific class (Class X), we will use the MAXIFS function with two criteria. In the formula bar, type =MAXIFS and then select the marks range (C2:C14). Next, select the female range (B2:B14).
In the criteria_range1 field, enter “F“, and in the criteria_range2 field, select the class range (D2:D14) and enter “X” in the criteria2 field. The formula will become =MAXIFS(C2:C14,B2:B14,“F”,D2:D14,“X”). Press Enter to get the result.
Conclusion
By following these steps, you can use the MAXIFS function in Excel to find the maximum value based on one or more criteria. If you want to learn more about the Excel MAXIFS function, check out the video on it from our Youtube channel.
