Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, Washington, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Excel 2007 on XP.
    I'm trying to convert raw scores from a rating scale form.

    The formula is in A1 and refers to the value in B1:
    The rule I need is
    IF B1 = 0, THEN A1 = 1.
    IF B1 = 1, THEN A1 = 0.
    IF B1 = 2, THEN A1 = 0.
    IF B1 = <blank> , THEN A1 = 0.

    So my formula is this:
    =IF(B1=0,1,IF(B1=1,0,IF(B1=2,0,IF(B1="",0))))

    The result is, if I enter 0, it correctly gets "1" and if I enter 1 or 2, it correctly gets "0," but if cell B1 is blank, then it DOES NOT get "0," but get an erroneous "1."

    Any ideas how to get this to work? Thanks -steve

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts
    =if(or(isblank(b1)=true,b1=1,b1=2)=true,0,1)

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Assuming they are the ONLY values in B then

    =IF(OR(B1=1,B1=2,B1=""),0,1)
    Andrew

  4. #4
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, Washington, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes, these work perfectly--thank you! -steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •