Formula in B1

  • Thread starter Thread starter Trainee
  • Start date Start date
T

Trainee

Could somebody be so kind and provide me with a formula to go in
B1 that will change this cell accordinly to A1.
If A1= blank, then B1= blank
If A1= "green" or "blue" or "plant" or "street" (TEXT),then B1 will show
letter "f".(all examples without quotation of course)
If A1= any other different word, then B1= blank.
Thank you very much.
 
Trainee said:
If A1= blank, then B1= blank
If A1= "green" or "blue" or "plant" or "street" (TEXT),then B1 will show
letter "f".(all examples without quotation of course)
If A1= any other different word, then B1= blank.
Click to expand...

You have over-specified the requirements. In B1, the following is
sufficient:

=if(OR(A1={"green","blue","plant","street"}), "f", "")

The quotes are required around string constants. They will not appear, and
they will not be considered part of the text.

PS: Note that you cannot leave B1 "blank" (empty), if by that you mean that
ISBLANK(B1) returns TRUE. But B1="" will be true, whether B1 is empty (no
formula and no constant value) or the null string (""). ISBLANK is a
misnomer :-(.


----- original message -----
 
As a blank in a1 or any othoer word than the ones listed have the same result
they need not be defined... putting this formula in cell b1 will provide the
results requested.

=IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f","")

but if when A1 is blank needs to be considered seperately then use the
following

=IF(A1="","",IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f",""))
 
Rik_UK said:
As a blank in a1 or any othoer word than the ones listed have the same result
they need not be defined... putting this formula in cell b1 will provide the
results requested.

=IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f","")

but if when A1 is blank needs to be considered seperately then use the
following

=IF(A1="","",IF(OR(A1="green",A1="blue",A1="plant",A1="street"),"f",""))
Click to expand...

It works good, Thanks Rik Uk.
Now can the same formula be use with range of words instead of
writing each one each time. This come for another worsheet that
has 50 words and I need the "f" in B1 too.
The range is called "listedwords"
Thanks again.
 
Try this:

=IF(A1="","",IF(ISNA(MATCH(A1,listedwords,0)),"","f"))

Hope this helps.

Pete (also from the UK)
 
Sorry, Rik, your post wasn't visible when I responded - I thought you
might have called it a day.

Pete
 
Pete_UK said:
Sorry, Rik, your post wasn't visible when I responded - I thought you
might have called it a day.

Pete



.
Thank you too Peter Uk, you guys are doing a good job.
Click to expand...
 
Back
Top