Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Double Unary Operator in Excel Array Formulas?
Microsoft Excel is undoubtedly one of the most powerful tools for organizing and analysing data. With its extensive range of features, it often becomes challenging to uncover all its hidden tips and tricks. In this article, we will explore a simple yet effective technique to use the Double unary operator in the Excel Array formula. For intricate scenarios, AND and OR operators proved to be more powerful and defined along with the SUM function to perform the complex task. The double unary operator transforms the Boolean values that are True/False into the binary form (0 or 1) which is understandable by the array function. It is denoted by a double dash(--).
Utilizing Double Unary Operator
This example uses the two functions SUM and Month function in addition to the Double Unary operator.
Step 1 − To Open the Excel spreadsheet, launch Microsoft Excel on the computer. Create or open an existing spreadsheet containing the data we need to work with.
Employee's names are highlighted in the first column and the number of dates is shown in the second column. Now, we must calculate the number of dates that occurred in a particular month. Consider the sample data as shown below −
Step 2 − Enter the formula =SUM(--(MONTH(B2:B9)=F1)) in the F2 cell as illustrated in the below image.
Explanation of the Formula
The month function contains only one argument where the range B2:B9 is specified, which generates the array {9;10;11;9;11;10;10;10}. Each array’s item is verified to the value specified in the F1 cell and returns the result in a Boolean value.
Furthermore, the double unary operator permutes the Boolean values into the binary form that is 0 or 1. The first unary operator returns -1 and the second unary operator reverses the negative sign to make it a positive sign and the value becomes +1.
The sum function performs the addition of the computed values and returns the result that is 2.
Step 3 − Then press the ctrl+shift+ Enter keys to compute the array formula. The resultant number is 2 which means only two dates of the September month reside in the B column.
Conclusion
In this article, the notion of the Double unary operator is explained with an interesting example. By diligently following these steps, you have successfully learned how to quickly use the double unary operator in MS Excel. Start applying this method today and unlock the full potential of Microsoft Excel when presenting complex information.
116 Views
