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
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:
Code:
=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
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 "*" 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:
Code:
=IF((A1>5)*(A1<11)*(B1>=2),1,0)
or as long as you only want 1 or 0, you could even use this:
Code:
=(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:
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 "Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.