You can count the maximum value using the Max function, or the second largest value using the LARGE function in a range. However, you will need to change the formula when counting the second-largest value for a group.
Example: You work with the data below with the groups in column A, regions in column B, and salaries in column C.
Formula 1: To count the second-largest value for group A
To find the second-largest value in Group A, we will use the following formula:
=LARGE(IF(A2:A12="Group A", C2:C12),2)
- (A2:A12="Group A"): returns "true" if Group A, and "false" if not Group A;
- IF(A2:A12="Group A", C2:C12): if Group A in column A, values in column C, otherwise, returns zero;
- LARGE(IF(A2:A12="Group A", C2:C12),2) is to find the second-largest value from the new value list.
Formula 2: To count the second-largest value for group A
You can also modify the formula as the one below
=LARGE((A2:A12="Group A") * (C2:C12),2)
- (A2:A12="Group A"): returns "true" if Group A, and "false" if not Group A;
- (A2:A12="Group A") * (C2:C12): if Group A in column A, values in column C, otherwise, returns zero;
- LARGE((A2:A12="Group A")*(C2:C12),2): To find the second-largest value from the new list.
- Please note that the result will return an error if you have only one value in the group (e.g., North region).