Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

So I have two columns:

  1. Values can be "Yes - Start", "Yes - End", "Yes - Both" and "No"
  2. Values are the same as column 1

They don't match all the time, so I want to create an additional column that would say:

  • "Rare", if cell in column 2 is different from "No"
  • "Common", if cell in column 2 is equal to "No"
  • "C&R", if cells from both columns (A1 and B1, for e.g.) have a value different from "No"

I tried

=IF(B1="No";"Common";"Rare")

But this only solves the first two points, the "C&R" one I don't know how to solve.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

If I've understood this right, then you could use the following formula:

=IF(B1="No","Common",IF(AND(A1<>"No",B1<>"No"),"C&R","Rare"))

It basically breaks down to:

  • If B1 is no then it's Common
  • If B1 is one of the yes values then it's C&R if both A1 and B1 are yes values, otherwise B1 is Rare.

For your additional requirement in the comments of returning "None" if both A1 and B1 are no then change the formula to:

=IF(B1="No",(IF(A1="No","None","Common")),IF(AND(A1<>"No",B1<>"No"),"C&R","Rare"))

This breaks down to:

  • If B1 is no then it's Common if A1 is one of the yes values, otherwise it's None
  • If B1 is one of the yes values then it's C&R if both A1 and B1 are yes values, otherwise B1 is Rare.
share|improve this answer
    
Thanks! This does it, although I noticed something now.. when both A1 and B1 are "no", the result should be "None", or else it will look odd. How can I do that? Also, what do <> mean? Thank again for your time! –  Santiago Apr 20 '11 at 14:40
    
@Santiago <> means "not equal to". I'll edit my answer in a second to add in your latest requirement. –  8088 Apr 20 '11 at 14:51
    
thanks you so much! this does it! I'll study the formula well so next time I'll be good on my own. –  Santiago Apr 20 '11 at 15:04

this should do it:

=IF(AND(A1<>"No",B1<>"No"), "C&R",IF(B1="No", "Common", "Rare"))

This basically says -

if both columns aren't "No", then "C&R" // your last case
else 
  if second column is "No", then "Common" // your 2nd case
  else "Rare" // i.e., it's not "No"   // your first case
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.