logo-wlogologo-wlogo-w
  • Home
  • Classes
  • Training
  • Consulting
  • Blog
  • Contact
✕
How to delete Alternate Rows
October 15, 2021
Working with Data Tables
October 28, 2021

The Double Negative

October 21, 2021

Double negative, sometimes referred to as double unary, forces TRUE or FALSE values to their numeric equivalents, 1 or 0. Let's say that you have a list of words in a range, and you want to count how many of them contain more than 7 characters.

To find this out, we can type a LEN function:
=LEN(B4:B9)>7
For each of the five cells in the range, LEN returns a character count that is checked with >7. The result is an array of 7 TRUE or FALSE values like this.

{FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}

Screenshot (29)

The 3 TRUEs are for each of the text values that qualify for the function. Now if we drop the same function under the SUMPRODUCT to find the same results, we'll get zero.

Screenshot (30)

This is because TRUE and FALSE are logical, not numeric. To make Excel treat logical numbers, we need to add double negatives to convert TRUE to 1 and FALSE to 0. We're going to wrap the LEN function in the parentheses and put the double negatives in front of it like this.

Screenshot (31)

Contributor : Team Leveraged Growth

Share
1

Related posts

December 16, 2021

Stacking in EXCEL


Read more
December 9, 2021

SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW


Read more
November 25, 2021

Circular Referencing Solution


Read more

Comments are closed.

✕

Categories

  • Google
  • MS Excel
  • MS Word
  • Power BI
  • Uncategorized
  • Stacking in EXCEL
    December 16, 2021
  • SIMPLE EXCEL FUNCTIONS YOU SHOULD KNOW
    December 9, 2021
  • Tips and Tricks of MS PowerPoint
    December 3, 2021
  • Circular Referencing Solution
    November 25, 2021
  • Printing tricks
    November 18, 2021
    Disclaimer Policy | Terms of Service
    Powered by Aswini Aswini Bajaj
    Website Designed & Developed by 'A PIXEL HOUSE'