Finding the second largest value in Excel can be implemented by the LARGE function. The formula is =LARGE(range, 2), where range is the data area; if the maximum value appears repeatedly and all maximum values ??need to be excluded and the second maximum value is found, you can use the array formula =MAX(IF(rangeMAX(range), range)). Old version of Excel needs to be executed by Ctrl Shift Enter; for users who are not familiar with formulas, you can also manually search by sorting the data in descending order and viewing the second cell, but this method will change the order of the original data. It is recommended to copy the data first and then operate.

It is actually not difficult to find the second largest value in Excel, but many people may only look for the maximum value and ignore the search method of the submaximum value. If you have multiple different values ??in your data and want to quickly locate the "second place", you can use built-in functions to achieve it.

Use the LARGE function to get it directly

Excel provides a very convenient function: LARGE , which can return the kth largest value in the dataset. To find the second largest value, just set k = 2.

The formula is as follows:

 =LARGE(range, 2)

Where range is the data area you are looking for, such as A1:A10.

For example, if your data is between A1 and A10, enter:

 =LARGE(A1:A10, 2)

You can get the second largest value. This method is most straightforward, provided that you have at least two different values ??in your data, otherwise you may return the same result as the maximum value.

Handle the case of repetition maximum value

If the maximum value appears many times, you are looking for the number "second only to these maximum values", and the above method is still valid at this time. For example, the following data:

 10, 10, 9, 8, 7

The result with =LARGE(A1:A5, 2) is 9, which is exactly what we want.

However, if you want to make sure that "exclude all maximums and find the maximum", you need to combine functions. You can use MAX and IF to match array formulas (applicable to old versions before Excel supports dynamic arrays):

 =MAX(IF(range<>MAX(range), range))

After entering, remember to press Ctrl Shift Enter (old Excel) to run it as an array formula.

Use sorting helper columns (suitable for users who are not familiar with formulas)

If you don't like using functions, you can also find the second largest value by sorting:

• Select the data area;
• Click the "Sort" button and select descending order;
• After being arranged, the second cell is the "second largest value" you are looking for.

This method is simple and intuitive, suitable for use when searching at one time or when operating a small amount of data.

But it needs to be noted:

• If there is a duplicate maximum, the "first non-maximum" that ranks behind is the second place you really want;
• This method will change the order of the original data. It is recommended to copy a copy of the data before operating.

Basically these are the methods. You can choose to use LARGE functions, combine formulas, or sort manually according to your habits and data situations. Although it doesn't seem complicated, it can make mistakes if you don't pay attention, especially when the maximum value repeats itself.

The above is the detailed content of how to find the second largest value in excel. For more information, please follow other related articles on the PHP Chinese website!