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")

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")

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")

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")

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")

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")