Excel - what's the use of the common "--" in formulas?
Glutinous rice 2021-02-23 00:46:46

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 :
  1. Convert text numbers to numeric format
  2. 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 ?

Excel – Common in formulas “--” What's the point ?


Solution 1:


1. We use the most common sum Sum up the formula , See if it's possible to come out

Excel – Common in formulas “--” What's the point ?


2. Sure enough, it can't be calculated , I want to be blind

Excel – Common in formulas “--” What's the point ?


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

Excel – Common in formulas “--” What's the point ?


Excel – Common in formulas “--” What's the point ?


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 .

Excel – Common in formulas “--” What's the point ?


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

Excel – Common in formulas “--” What's the point ?


2. If you remove n function , The statistical result is 0, Because logical values cannot participate in summation

Excel – Common in formulas “--” What's the point ?


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

Excel – Common in formulas “--” What's the point ?

版权声明
本文为[Glutinous rice]所创,转载请带上原文链接,感谢
https://fheadline.com/2021/02/20210222172814895g.html
相似文章

2021-08-09