+ Reply to Thread
Results 1 to 3 of 3

Nested AND in IF formula returning wrong results

  1. #1
    Registered User
    Join Date
    03-08-2019
    Location
    New Haven
    MS-Off Ver
    14.7.3
    Posts
    1

    Question Nested AND in IF formula returning wrong results

    Moderator note:Hello nihau. Welcome to the forum. As jason has noted below in post #3 we require informative thread titles (Rule #1). Think Google search terms you would use to do a search for your particular question.
    Since you are new to the form I went ahead and changed it for you this time.

    Hi!

    So I want to create a new dummy variable (switch) in a dataset out of two other variables (in columns C and D). It should always say "shift" when in column D a value was 1 and in the following row in column C the value is 0. Otherwise it should say "stay". I tried it with the following formula:

    = IF(AND(C5="0";D4="1");"SHIFT";"STAY")

    however I always end up having "STAY" in the new variable, even though the criteria for "SHIFT" are met. Somebody got an idea?

    Thanks in advance!
    Last edited by FlameRetired; 03-08-2019 at 08:52 PM. Reason: Title change

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,739

    Re: IF AND formula

    "0" & "1" are string characters representing the numeric values 0 and 1 they are not the same. Get rid to the quote marks in the formula.
    Ben Van Johnson

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: IF AND formula

    Try

    =IF(AND(C5=0;D4=1);"SHIFT";"STAY")

    When you enlose a number in double quotes it is no longer valued as a number.

    For future reference, please take a look at the forum rules that you agreed to follow when you joined. Your thread title does not comly with them, up until recently that meant that we were not allowed to help you until you changed it. The rules have been relaxed a little now but you are still expected to follow them.

    If you post future threads with non compliant titles then the moderation team will be more likely to apply stricter enforcement.

    I would also suggest that you change the title of this thread before anyone else jumps in and mentions it. Some, but not all mods will change it for you as it's your first post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  2. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  3. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  4. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  5. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  6. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1