LOGICAL OPERATOR "IF" FOR AN ARRAY
 

Can V have a formulas like the following?

if ( any of the cells in array A1:A100 = "LOS ANGELES" , then "AMERICAN",
otherwise "NON-AMERICAN")

OR

if ( any of the cells in array A1:A20 < "40" , then "FAIL", otherwise "PASS")

LOGICAL OPERATOR "IF" FOR AN ARRAY
 

Thanx buddy,

But I am very sorry, probably I was not able to present my question
appropriately.

Lets consider a formula for:

if ( Entry in B1, IS NOT PRESENT IN THE ARRAY A1:A100, then "INVALID ENTRY",
otherwise "CORRECT ANSWER")

Sorry for any problems but please do solve this problem of mine urgently.

"Prasadkakarla" wrote:

try this :

=IF(B2="LOSANGELS","AMERICANS","NON-AMERICANS")

same way u can try numbers also

=if(B2<40,"fail","Pass")
--
Name : K.Prasad
Inventory Manager
CRYSTALGALLERY
DUBAI



"FARAZ QURESHI" wrote:

Can V have a formulas like the following?

if ( any of the cells in array A1:A100 = "LOS ANGELES" , then "AMERICAN",
otherwise "NON-AMERICAN")

OR

if ( any of the cells in array A1:A20 < "40" , then "FAIL", otherwise "PASS")

LOGICAL OPERATOR "IF" FOR AN ARRAY
 

For each of your formula try


=IF(A1:A100="Los Angeles","American","Not American")

=IF(MIN(A1:A20)<40,"Fail","Pass")

"FARAZ QURESHI" wrote:

Can V have a formulas like the following?

if ( any of the cells in array A1:A100 = "LOS ANGELES" , then "AMERICAN",
otherwise "NON-AMERICAN")

OR

if ( any of the cells in array A1:A20 < "40" , then "FAIL", otherwise "PASS")

LOGICAL OPERATOR "IF" FOR AN ARRAY
 

Hi Faraz,

I'm working with Excel 2002 French.
This works : =si(nb.si(A1:A100;B1)=0;"Invalid";"correct")

With English version, I expect to be (to be checked) :
=if(count.if(A1:A100,B1)=0,"Invalid","correct")

Regards
FxM


FARAZ QURESHI a écrit :
Thanx buddy,

But I am very sorry, probably I was not able to present my question
appropriately.

Lets consider a formula for:

if ( Entry in B1, IS NOT PRESENT IN THE ARRAY A1:A100, then "INVALID ENTRY",
otherwise "CORRECT ANSWER")

Sorry for any problems but please do solve this problem of mine urgently.

"Prasadkakarla" wrote:

try this :

=IF(B2="LOSANGELS","AMERICANS","NON-AMERICANS")

same way u can try numbers also

=if(B2<40,"fail","Pass")
--
Name : K.Prasad
Inventory Manager
CRYSTALGALLERY
DUBAI



"FARAZ QURESHI" wrote:

Can V have a formulas like the following?

if ( any of the cells in array A1:A100 = "LOS ANGELES" , then "AMERICAN",
otherwise "NON-AMERICAN")

OR

if ( any of the cells in array A1:A20 < "40" , then "FAIL", otherwise "PASS")

LOGICAL OPERATOR "IF" FOR AN ARRAY
 

Faraz,

I think you want one formula that examines all of A1:A100, and if it finds
what's in B1, it says so, otherwise, it says not. Put this in a cell:

=IF(OR(A1:A100=B1),"Entry in B1 is present in A1:A100","Entry in B1 is not
present in A1:A100")

It must be entered as an array formula (press Ctrl-Shift-Enter instead of
Enter any time you've typed or changed it).
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"FARAZ QURESHI" wrote in message
...
Can V have a formulas like the following?

if ( any of the cells in array A1:A100 = "LOS ANGELES" , then "AMERICAN",
otherwise "NON-AMERICAN")

OR

if ( any of the cells in array A1:A20 < "40" , then "FAIL", otherwise
"PASS")

LOGICAL OPERATOR "IF" FOR AN ARRAY
 

YAHOO!!!

THANX!!!

GREAT!!!

You actually opened my eyes. Never ever knew what array formulas are!

REALLY GREAT!!!

THANX AGAIN!!!

YAHOO!!!

"Earl Kiosterud" wrote:

Faraz,

I think you want one formula that examines all of A1:A100, and if it finds
what's in B1, it says so, otherwise, it says not. Put this in a cell:

=IF(OR(A1:A100=B1),"Entry in B1 is present in A1:A100","Entry in B1 is not
present in A1:A100")

It must be entered as an array formula (press Ctrl-Shift-Enter instead of
Enter any time you've typed or changed it).
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"FARAZ QURESHI" wrote in message
...
Can V have a formulas like the following?

if ( any of the cells in array A1:A100 = "LOS ANGELES" , then "AMERICAN",
otherwise "NON-AMERICAN")

OR

if ( any of the cells in array A1:A20 < "40" , then "FAIL", otherwise
"PASS")