How to quickly find the largest value but smaller than X in Excel?
To find the largest value in a range is easy for you during the Excel work, but to find the largest value but smaller than a certain value such as 100 in a range may trip some of the Excel green hands over. Here, this article is going to introduce the ways to help you find the largest value but less than X quickly in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Here are some formulas which can quickly find the largest value but less than 100 from a range (A1:E7).
|Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.|
1. Select a blank cell that you want to output the calculating result, and type this formula =MAX(IF(A1:E7<100,A1:E7,"")), and remember to press Shift + Ctrl + Enter keys simultaneously to get the correct result. See screenshots:
Tip: You also can use this formula =SUMPRODUCT(MAX((A1:E7<100)*A1:E7)) and press Enter key to find the largest number but smaller than 100, too.
|Never need to worry about long long formulas in Excel anymore! Kutools for Excel's Auto Text can add all formulas to a group
as auto text, and liberate your brain! Know About Auto Text Get Free Trial
Actually, in Kutools for Excel’s multiple functions, you can use the Select Specific Cells to select all numbers which are less than 100, and then find out the max value by Select Cells with Max & Min Value to identify the largest value.
|Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.|
After free installing Kutools for Excel, please do as below:
1. Select the range you want to find the largest value less than X, and click Kutools > Select > Select Specific Cells. See screenshot:
2. Then in the Select Specific Cells dialog, check Cell option, and select Less than from the first drop down list in the Specific type list section, then type the number you need into the next textbox, and click Ok, a dialog pops out to remind you the number of selected cells. See screenshot:
3. Click OK to close dialogs. And then keep selecting cells, click Kutools > Copy Ranges to enable the Copy Multiple Ranges dialog, check All option and click OK button in this dialog. Next in the second Copy Multiple Range dialog please select a blank cell to output the copied ranges and click OK button. See screenshot:
4. Select the pasted range, and click Kutools > Select > Select Cells with Max & Min Value, and check Maximum value and Cell options, and then you can specify to select all max values or the first appears one. See screenshot:
5. Click Ok, and now the largest value but smaller than 100 has been found.
- How to extract all duplicates from a column in Excel?
- How to count the number of shaded cells in Excel?
- How to maximize all columns in Excel?
Recommended Productivity Tools