+ Reply to Thread
Results 1 to 6 of 6

Formula returning result of "True" or "False" instead of "1" or "0"

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Formula returning result of "True" or "False" instead of "1" or "0"

    I have the following forumla which should tell Excel to return the value of 1 if true or 0 if false. However, I am getting the words and not the numbers.

    Please Login or Register  to view this content.
    Can anyone see where I am going wrong?

    Thanks for your help.

  2. #2
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Formula returning result of "True" or "False" instead of "1" or "0"

    Please attach a dummy sheet see we can see what you have in the other cells etc.
    Regards
    Peter

  3. #3
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula returning result of "True" or "False" instead of "1" or "0"

    Try to sort out your brackets. I assume you may want

    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: Formula returning result of "True" or "False" instead of "1" or "0"

    Hi Peter, unfortunately, I am not in a position to upload a dummy sheet which I appreciate would help understand the context.

    Teylyn, unfortunately, this counts everything in column Q which is 1000, my answer should be 50. I need to spend a little more time on this which I can't do right now but really appreciate your help.

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Formula returning result of "True" or "False" instead of "1" or "0"

    Let's take apart your original formula

    =IF(OR(Properties!$AX3="Y",Properties!$AT3="N",Properties!$Q3={"Y","O","AO"}),
    AND(OR(Properties!$AX3="",Properties!$AT3="N",Properties!$Q3={"O","AO"}),
    1,0))

    The fact that it ends in 2 brackets is enough to tell me that the brackets are not placed correctly. Red is the condition, blue is the TRUE part, Green is the FALSE part. I'm sure that's not what you have in mind.

    Your formula delivers TRUE because in some cases the TRUE part is executed. In your formula, the TRUE part is in itself a comparison, which will deliver TRUE or FALSE as the result.

    Again, your brackets are not in the correct positions.

    I can't quite picture your spreadsheet in my crystal ball, so unless you manage to place your brackets in the correct places yourself, you'll have to post it, and an example of the expected result.

  6. #6
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Formula returning result of "True" or "False" instead of "1" or "0"

    Can you not copy or recreate a small section, remove or change personal details.
    Regards
    Peter

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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