The logic gate

Have you heard about Logic Gate?  I learned logic gate in high school physics.  Decades ago… so don’t expect I can remember what it is.  😛

Having said that, I do remember the fundamental concept about AND gate and OR gate, which is very helpful for me to master logical functions in Excel.  Before we move to discuss advanced use of SUMPRODUCT as a function to do conditional SUM, we need to understand the behavior of  TRUE and FALSE in Excel first.

You may download a sample file to follow through.

What is TRUE and FALSE in Excel?

Boolean…?  Let’s skip this kind of jargon (the truth is I don’t know it either)…. TRUE or FALSE is simply a result of a logical test:Excel Tips - logic gate 0.PNGTip: Did you notice that by default, TRUE/FALSE is center-aligned; while number is right-aligned; text is left-aligned.

TRUE/FALSE is a not number nor text, it is a logical value.Excel Tips - logic gate.PNG

Note: You can input “TRUE” or “FALSE” (without the double quotation, or apostrophe comma before) as logical value directly.

TRUE and FALSE as 1 and 0 respectively

Although TRUE/FALSE are not number, they behave just like 1 (as TRUE) and 0 (as FALSE) when you put them into mathematics operations:

Excel Tips - logic gate 1.PNG

 

Excel Tips - logic gate 2.PNG

Try substitute TRUE with 1, FALSE with 0, you will get the same results.

However, most FUNCTIONS ignore logical values.

Excel Tips - logic gate 3.PNG

Please study the results carefully.  Although the results highlighted in green seem to be correctly treated TRUE and FALSE as 1 and 0 respectively, it is not the case. The logical values are simply ignored.

How about SUMPRODUCT?  How does it treat logical values?

If you don’t know it, I suggest you read my previous post about the basic of SUMPRODUCT.

“Note 1 – All non-numeric values in array will be treated as 0″Excel Tips - logic gate 4.PNG

Indeed, this is a common hurdle for many people to use SUMPRODUCT for conditional SUM.

To make SUMPRODUCT work for the above example, we need to convert the array of TRUE/FALSE into an array of 1/0.

HOW?  If you have followed through the content so far, you should be able to figure out many ways by applying simple operations to the array.  E.g.

Excel Tips - logic gate 5.PNGI have highlighted the common ways for your reference, which is array + 0 and double negative sign (- -) before the array.

No matter which operation you deploy, you will get the same result.Excel Tips - logic gate 6.PNG

Which one is the best practice?   I don’t have an answer for you.  My preference is double negative because the chance of “unaware” typo is low.  Imagine this: You want to convert the TRUE/FALSE into 1/0 by +0, however you mistype it as +01.  What results are you going to get?

Indeed, the best practice should be evaluating your formula to make sure it works the way you want.

“Perhaps there is no right or wrong… but there is absolutely TRUE or FALSE”.

Let’s dive in SUMPRODUCT in the next post.  Stay tuned. 🙂

Advertisements
This entry was posted in Formula and tagged , . Bookmark the permalink.

4 Responses to The logic gate

  1. Michael (Micky) Avidan says:

    Hi, Wong,
    Is it my poor eyes or you didn’t find it apropriate to attach the Workbook from which you demonstrated the various formulas ?
    If I’m not mistaken I suggested, more than once, to attach the WB in order to asloow the readers to excersice the issues.
    Before I’ll comment on one of the issues – I’ll wait for the WB.
    Have anice weekend,
    It will be more than nice if you will adopt it as a habit !
    Micky

    Like

    • MF says:

      Hi Micky,
      Thank you very much for your reminder again. I have attached the sample file. Please feel free to take a look and comment.
      I know I don’t have a habit to attach a sample file yet. I am working hard to improve that.
      Looking forward to hearing from you the issue you mentioned.
      You too have a nice weekend.
      Cheers,
      MF

      Like

      • Michael (Micky) Avidan says:

        Thanks, Wong,
        Please try to remember my, useful, proposal in your next posts.
        I have one comment regarding the use of SUMPRODUCT while T/F are involved.
        Although it will not be exactely the basic format of the Function – one can, easily, replace the comma with an asterisk and it will workת as expectedת without converting the T/F array into a 1/0 array.
        =SUMPRODUCT(A1:A6*B1:B6)
        Micky

        Like

        • MF says:

          Hi Micky,
          I will bare that in mind. 🙂
          And thanks for your suggestion on multiplying the two arrays together within the SUMPRODUCT. Indeed, I have that in the sample file, which I plan to dive in the next post, while this post focuses on how the True False works.
          Cheers,

          Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s