What does — do in Excel formulas?

by

The double-dash is known as a double unary operator.

Try this link: Why use — in SUMPRODUCT formulae

Specifically:

SUMPRODUCT() ignores non-numeric entries. A comparison returns a boolean (TRUE/FALSE) value, which is non-numeric. XL automatically coerces boolean values to numeric values (1/0, respectively) in arithmetic operations (e.g., TRUE + 0 = 1).

The most efficient way to coerce the value is first to apply the unary minus operator, coercing TRUE/FALSE to -1/0, then applying it again to negate the value, e.g., +1/0.

A single unary operator (-) coerces true/false values into -1/0. By using the double unary operaor, we coerce the values again to 1/0.

More Related Contents:

  1. How to convert to percentage [closed]
  2. Excel Number Separation [closed]
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Error in finding last used cell in Excel with VBA
  6. Getting unique values in Excel by using formulas only
  7. Vlookup using 2 columns to reference another
  8. Excel: last character/string match in a string
  9. Referencing value in a closed Excel workbook using INDIRECT?
  10. Simple Pivot Table to Count Unique Values
  11. Excel UNIQUE Across Columns
  12. Ignore Duplicates and Create New List of Unique Values in Excel
  13. How to create a string or formula containing double quotes in Excel?
  14. Find last used cell in Excel VBA
  15. Two column lookup in table array using INDEX and MATCH
  16. Excel Date to String conversion
  17. Excel: Formulas for converting data among column / row / matrix
  18. If two cells match, return value from third
  19. Excel: Searching for multiple terms in a cell
  20. Three Dimensional Lookup Using INDEX/MATCH
  21. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  22. Countif With Multiple OR Criteria
  23. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  24. How to split texts from dynamic range?
  25. Excel: Search for a list of strings within a particular string using array formulas?
  26. Convert from US to UK format
  27. How to roundup a number to the closest ten?
  28. Convert an Array Formula’s Text Results into a Usable Format
  29. Can’t pass a range to a lambda using Let and Makearray functions
  30. Unique Rank value for a subgroup within a group

Leave a Comment