question

Copper Contributor

Can I put a minimum and a maximum number, that a cell with a formula, can have?

6 Replies

@lfox2 

Yes, you will need to use MIN() and MAX() formula.

adversi_0-1606400348512.png

 

Hey. Yes it's possible to do that !

@lfox2 

It is not like data validation.  If the result of the calculation is a scalar value then the MEDIAN function can be used to place upper and lower bounds of the result returned by the formula.  So

= value*(1+rate)

becomes

= MEDIAN( min, max, value*(1+rate) )

 

If, like me, you always use array formulas, life become more difficult because the aggregation functions such as MIN, MAX, SUM aggregate entire array rather than value by value.  In that case the formula might need to be

 

=  LET(
   result, array*(1+rate),
   IFS(result>max, max, 
       result<min, min, 
       1, result))

 

 

 

@luse fox2 

use if-then-else logic

If < m then whatever or if < n then whatever else formula

@lfox2   Learning to provide concrete examples will go a long way toward getting applicable answers.

 

Suppose the original formula is =SUM(A1:A100), and you want the result to be no less than 10 and no more than 100.  You could write:

 

=MAX(10, MIN(100, SUM(A1:A100))

@lfox2 

Hi,

You may apply Custom Data validation to the cell or range where you want to create the formula with upper and lower limits. To do that, 

Select the destination range

Go to the Data Tab and click on data Validation (Shortcut is Alt , D ,L)

Under "Allow" select ► "Custom"

Under Data ► Select Between

Type the Min and Max values and hit OK

 

nabilmourad_0-1606436506099.png

If the result of the function violates the Upper or lower limit ► you get an error message.
Please Note that You cannot create a Formula/ Function in a cell then copy it because copying overwrites the Data Validation.
You can create a very simple code to prevent overwriting  the Data validation, here is a link:
https://www.youtube.com/watch?v=9JhCT6VMzBQ 
Hope that helps

Good Luck
Nabil Mourad