How to limit formula result to maximum or minimum value in Excel?
Here are some cells needed to be entered, and now I want to use a formula to sum up the cells but limit the result to a maximum value such as 100. In other words, if the summation is smaller than 100, display the summation, otherwise display 100.
To handle this task, you only need to apply the Max or Min function in Excel.
Limit formula result to maximum value (100)
Select a cell which you will place the formula at, type this formula =MIN(100,(SUM(A5:A10))), A5:A10 is the cell range you will sum up, and press Enter. Now, if the summation is greater than 100, it will display 100, if not, display the summation.
|Summation is greater than 100, show 100|
|Summation is smaller than 100, show summation|
Limit formula result to minimum value (20)
Select a cell which you place the formula at, type this =MaX(20,(SUM(A5:A10))), A5:A10 is the cell range you will sum up, and press Enter. Now, if the summation is smaller than 20, it will display 20; if not, display the summation.
|Summation is smalller than 20, show 20|
|Summation is greater than 20, show summation|
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!