Forum Discussion

pysnyp's avatar
pysnyp
Copper Contributor
Sep 26, 2020
Solved

Please help

Good morning, I would like to advise with function IF AND or maybe others. For example, we have a table with two columns and one row. (A1;B1) Conditions: if is the value of A1 between "0,01-20,00"...
Formulas and Functions
  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Sep 26, 2020

    pysnyp ,,

     

    You may use this one:

     

    =IF(AND(A1>=0.01,A1<=20),20,IF(A1=0,0,(20+A1-20)*0.7))

     

    N.B. I'm using US keyboard & according to the Regional Settings, DOT is Decimal. 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 26, 2020

pysnyp 

As variant

=MAX(20,20+(A1-20)*0.7)*(A1>0)
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Sep 26, 2020
Your formula needs little correction in case of value is other than 20 gives wrong result,, please check this part (A16-20)*0.7).
    • Rajesh_Sinha's avatar
      Rajesh_Sinha
      Iron Contributor
      Sep 27, 2020
      With some of the values it returns wrong result, like if A1 has 21,, your formula returns 20.07 and mine is returning 14.70 , actually formula should calculate =(20+A1-20)*0.7 ,, where (20+A1-20)= 21 and then 21*0.7 = 14.70 .
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 27, 2020

        Rajesh_Sinha 

        Still didn't catch. Initial requirement

        if is the value of A1 "20,01-and more = B1 It shall be calculated as follows (20+((A1-20)*0,7))

         

        21 is more than 20.01, thus formula shall return (20+((21-20)*0,7))=(20+((1)*0,7))=(20+1*0,7)=20,7

         

        Why did you decide the formula shall calculate A1*0.7 ?

         

Resources