SOLVED

Please help

Copper Contributor

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" = B1 must be "20,00"

if is the value of A1 "0" = B1 must be "0" (zero)

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

 

Could you help me, someone?

 

Thank you.

Peter

 

11 Replies
best response confirmed by pysnyp (Copper Contributor)
Solution

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

@Rajesh_Sinha 

Thanks a lot, it works. Have a nice day.

Peter

@pysnyp 

As variant

=MAX(20,20+(A1-20)*0.7)*(A1>0)
Glad to help you,, if you feel then you may mark my post as best response as well like,, keep asking ☺
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 

Perhaps, but where is the mistake?

image.png

You have the best response, thank you, my friend.
Glad to help you, keep asking ☺
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 .

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

 

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.
1 best response

Accepted Solutions
best response confirmed by pysnyp (Copper Contributor)
Solution

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

View solution in original post