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

  1.
    yclik Posted messages 75 Registration date   Status Member Last intervention   1 612
     
    Good evening
    if well understood 
    =IF( (A1+D1)/12>1000,1000,(A1+D1)/12)
    0
    1. 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
  2. Raymond PENTIER Posted messages 58229 Registration date   Status Contributor Last intervention   17 491
     
    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
  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