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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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?