# Microsoft Office Application Help - Excel Help forum > Excel General >  >  how to set a max value?

## cale392

Hi there, 

I have a cell (lets say A1) which is generating a value based on the calculation of separate inputs (ie. in B1 and C1). How do I set a numerical limit on what is shown in A1, without causing the calculation to error? (eg. the calculation may have A1 equal 25, but i want it to only show 20 with the limit enforced). 

I tried using a whole number maximum with the Validation menu, but this only works if you are physically entering the value into that specific box (A1). 

thanks!

----------


## Claymation

Welcome to the board.

You should be able to do this using an "IF" formula.  This formula is an example that I have in A1 of my sheet:

=IF(SUM(B1:C1)>=20,20,SUM(B1:C1))

This will return a value of 20 if the resulting calculation is greater than 20, if it returns a value of less than 20, then the formula is not limited.

Hope that helps.





> Hi there, 
> 
> I have a cell (lets say A1) which is generating a value based on the calculation of separate inputs (ie. in B1 and C1). How do I set a numerical limit on what is shown in A1, without causing the calculation to error? (eg. the calculation may have A1 equal 25, but i want it to only show 20 with the limit enforced). 
> 
> I tried using a whole number maximum with the Validation menu, but this only works if you are physically entering the value into that specific box (A1). 
> 
> thanks!

----------


## cale392

thanks claymation, that did the trick!

----------


## daddylonglegs

You could also try

=MIN(20,B1+C1)

----------


## hjensen

Hi,

I need help to write a formel where there is a cap at 125% of a value of 2500. It should also show the value of ie 5%, 63%, 87% or 105% etc up to 125%. Anyone who can help me with that? ( hope the info mentioned is enough otherwise let me know and i will try to elaborate more).

Thanks

----------


## jeffreybrown

Hello hjensen & Welcome to the Forum,

*Administrative Note:*Somebody would be happy to help with your query, but first, before we can proceed…Please see Forum Rule #2...*Do not post a question in the thread of another member -- start your own thread.*.If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## hjensen

Hi Jeff,

Didnt read the rules properly, sorry for that. Will create a new thread for my issue.
cheers

----------

