I don't know if you've noticed , We often see some formulas with “--”, A lot of people don't understand , This “--” What does it mean ？ What role does it play in the formula ？
Let's talk about it in detail today .
“--” The role of ：
“--” The core function of the system is to convert characters that cannot be calculated into numbers that can be calculated , So that the formula works properly , such as ：
- Convert text numbers to numeric format
- amount to n function , Put the logical value （True/False） Convert to number （1/0）
Case study 1： Convert text numbers to numeric format
Below C Column , There is a small green arrow in the upper left corner of the number cell , You can tell by the naked eye that these are numbers in text format , Can't participate in computation .
Without changing the cell format , Is it possible for these text numbers to participate in the calculation ？
Solution 1：
1. We use the most common sum Sum up the formula , See if it's possible to come out
2. Sure enough, it can't be calculated , I want to be blind
3. But let's modify the formula a little bit , As shown below , Add... Before the sequence of cells “--”--> And press at the same time Ctrl+Shift+Enter Three key enter , The problem is solved ：
=SUM(--C2:C15)
* Please note that ： Use Sum Function time , You need to press at the same time Ctrl+Shift+Enter Triple bond , Call the array formula ; If you use sumproduct function , Just go straight back
Case study 2： amount to n function , Put the logical value （True/False） Convert to number （1/0）
Or use this watch , We need to count the number of students in class one .
Solution 2：
1. Usually , It can be used as follows sumproduct+n Function to count the number of people in a class ：
=SUMPRODUCT(N(" Class one "=$A$2:$A$15))
Definition of formula ：
- n The function here is to convert the logical value of the conditional operation in brackets true/false Convert to numeric 1/0
- Reuse sumproduct Sum to get the result
of n For a detailed explanation of the function, see Excel function (11)–n Function to numeric
2. If you remove n function , The statistical result is 0, Because logical values cannot participate in summation
3. But we can use “--” To replace n function , The formula is as follows ：
=SUMPRODUCT(--(" Class one "=$A$2:$A$15))
Definition of formula ：
- “--” The effect in this case is exactly the same as n function , the true/false Convert to 1/0
- In the last case, I said ,sumproduct Function coordination “--” Words , You don't need to use array functions , Just go back
