Forum Discussion
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"...
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.
Your formula needs little correction in case of value is other than 20 gives wrong result,, please check this part (A16-20)*0.7).
- 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 .
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 ?
- Yes,,, this is the situation =(20+21-20)*0.7 ,,, where A1 has 21 returns,, 14.70.
Since 20+A1 (i.e. 21) = 4,,, and next -20 = 21,,
ultimately is 21*0.7 = 14.70
If use =(20+(21-20)),,, returns 21,,, and next is 21*07 = 14.70 , is my interpretation.