colA colB colC 12 3 11

[workshop]

colA colB colC
12 3
11 1
in colC I try to get if a1 has value is >5 but < 11 combine with b1 >=2. I use =if(a1>5<11)*(b1>=2) but result in c1 is 0, what do I do wrong...please help

 

[Zathras]

Your conditions are not clearly stated. If you mean that you want C1 to be the product of A1 times B1 when all conditions are met:
(1) a1 has value is >5 but < 11 , and
(2) b1 >=2
then this formula should work for you:

  =IF(AND(A1>5,A1<11,B1>=2),A1*B1,0)

Both of your examples do result in zero:
A1 is greater than 11
B2 is less than 2

 

[workshop]

Thanks Zathras..
it close to what I want but from your I figure out =IF((AND(a1>5,a1<11))*(b1>=2),1,0) and it work for me
Thanks again

 

[Zathras]

I strongly recommend that you be consistent with your method.

The formula you figured out works because (b1>=2) produces either 1 or 0 depending on whether it is True or False. Then when you multiply (with the &quot;*&quot; symbol) the total condition becomes either 1 or 0. But mixing that style with the AND( ) function makes the formula harder to understand.

To be consistent, you could use this:

   =IF((A1>5)*(A1<11)*(B1>=2),1,0)

or as long as you only want 1 or 0, you could even use this:

   =(A1>5)*(A1<11)*(B1>=2)

But if you want your formulas to be understood by anyone beside yourself, it would be preferable to use this:

   =IF(AND(A1>5,A1<11,B1>=2),1,0)

Of course, the final choice is yours.

 

[xlbo]

LOL LOL LOL LOL
Do you want to keep your prizes or gamble for the staaaar prize ????
Were you a game show host in a previous life Zathras ???
;-) Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde