Forum Discussion

PRA76's avatar
PRA76
Copper Contributor
Feb 03, 2020

a question about excel

Hi

 

I have a question about an excel formula . I did the percent formula , it works perfectly , but it is above 120 % . I want to put a limit of 120 % , because the calculation it must not exceed  120 % . How can I do this ? 

excel

4 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 03, 2020

    PRA76 

    That would be:

     

    =MIN(120%,B1/A1)

     

    If B1 = 125 and A1 = 100, B1/A1 would give 125%, but the "MIN(120%" , sees to it that it will not exceed your upper limit.

    • PRA76's avatar
      PRA76
      Copper Contributor
      Feb 03, 2020

      Riny_van_Eekelen my formula is =(F7-E7)*100/(D7-E7) . The result is 197.92 . I want the formula make the calculation but to post only to 120 . I tried your suggestion but it didn't work . Thank you anyway .

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Feb 03, 2020

        PRA76 

        Try this:

         

        =MIN(120,(F7-E7)*100/(D7-E7))

         

         My earlier example works if you would have a value of 1.25, being 125%. So, if you put 120% in your formula, you set the upper limit to 1.2

        You could re-write your formula to:

        =MIN(120%,(E7-F7)/(E7-D7))

        and format the cell with the result as a %. 

Resources