View original (French)

Limit a cell's value between a high value and a low value

Solved
Noem794 Posted messages 6 Status Member -  
Noem794 Posted messages 6 Status Member -
Hello everyone,

I am currently working on creating a tool in Excel and I'm stuck on the formula to apply to a cell.

A simple calculation formula ((A1+D1)/12) is applied to this cell and I want the result to be limited between 0 and a maximum value (let's say 1000). I can set a high limit (at 1000) with MAX, or a low limit (0) with MIN, but I can't figure out how to combine the two...

Is there a formula for that?

Thank you in advance!

3 answers

Answer 1 / 3
yclik Posted messages 69 Registration date   Status Member Last intervention   1 607
 
Good evening
if well understood 
=IF( (A1+D1)/12>1000,1000,(A1+D1)/12)
0
Noem794 Posted messages 6 Status Member
 
Thank you very much for your response!
Unfortunately, in my final calculation, your formula didn't work... But the other formula proposed in this post worked well!
Have a good evening!
0
Answer 2 / 3
Raymond PENTIER Posted messages 58207 Registration date   Status Contributor Last intervention   17 476
 
Yes, Noémie, there are several formulas ...
Here is mine: 
=MIN(1000;MAX( (A1+D1)/12 ;0))


--
It's great, retirement! Especially in the Antilles ...
Raymond (INSA, AFPA)
0
Answer 3 / 3
Noem794 Posted messages 6 Status Member
 
Thank you very much for your reply! The formula you suggested worked perfectly!
You've taken a nice thorn out of my side...
Have a good evening!
0