SOLVED

Please help

%3CLINGO-SUB%20id%3D%22lingo-sub-1715749%22%20slang%3D%22en-US%22%3EPlease%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1715749%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%2C%20I%20would%20like%20to%20advise%20with%20function%20IF%20AND%20or%20maybe%20others.%3C%2FP%3E%3CP%3EFor%20example%2C%20we%20have%20a%20table%20with%20two%20columns%20and%20one%20row.%20(A1%3BB1)%3C%2FP%3E%3CP%3EConditions%3A%3C%2FP%3E%3CP%3Eif%20is%20the%20value%20of%20A1%20between%20%220%2C01-20%2C00%22%20%3D%20B1%20must%20be%20%2220%2C00%22%3C%2FP%3E%3CP%3Eif%20is%20the%20value%20of%20A1%20%220%22%20%3D%20B1%20must%20be%20%220%22%20(zero)%3C%2FP%3E%3CP%3Eif%20is%20the%20value%20of%20A1%20%2220%2C01-and%20more%20%3D%20B1%20It%20shall%20be%20calculated%20as%20follows%20(20%2B((A1-20)*0%2C7))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20help%20me%2C%20someone%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1715749%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1715818%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1715818%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F809969%22%20target%3D%22_blank%22%3E%40pysnyp%3C%2FA%3E%26nbsp%3B%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20may%20use%20this%20one%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(AND(A1%26gt%3B%3D0.01%2CA1%26lt%3B%3D20)%2C20%2CIF(A1%3D0%2C0%2C(20%2BA1-20)*0.7))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%20%3C%2FSTRONG%3E%3CEM%3EI'm%20using%20US%20keyboard%26nbsp%3B%26amp%3B%20according%26nbsp%3Bto%20the%20Regional%20Settings%2C%20DOT%20is%20Decimal.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1715827%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1715827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F93699%22%20target%3D%22_blank%22%3E%40Rajesh-S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot%2C%20it%20works.%20Have%20a%20nice%20day.%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1715911%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1715911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F809969%22%20target%3D%22_blank%22%3E%40pysnyp%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DMAX(20%2C20%2B(A1-20)*0.7)*(A1%26gt%3B0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1715986%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1715986%22%20slang%3D%22en-US%22%3EGlad%20to%20help%20you%2C%2C%20if%20you%20feel%20then%20you%20may%20mark%20my%20post%20as%20best%20response%20as%20well%20like%2C%2C%20keep%20asking%20%E2%98%BA%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1716003%22%20slang%3D%22en-US%22%3ERe%3A%20Please%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1716003%22%20slang%3D%22en-US%22%3EYour%20formula%20needs%20little%20correction%20in%20case%20of%20value%20is%20other%20than%2020%20gives%20wrong%20result%2C%2C%20please%20check%20this%20part%20(A16-20)*0.7).%3C%2FLINGO-BODY%3E
New 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 (New 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-S 

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

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

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.