Forum Discussion

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
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

Diamond Contributor

Sep 26, 2020

pysnyp

As variant

=MAX(20,20+(A1-20)*0.7)*(A1>0)

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).
- SergeiBaklan
  Diamond Contributor
  Sep 26, 2020
  Rajesh_Sinha
  
  Perhaps, but where is the mistake?
  - 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 .