Excel functions 01

When using Excel for addition, subtraction, multiplication, division, the first thought was to combine the formula with the relevant operator numbers (+.

But after careful consideration, I will find out how to implement the function.

Add:

SumFunction:

1. The parameters can be 1 to 30 parameters that need to be summed.

2. the text expression that is directly typed into the number, logical value, and number in the parameter table will be calculated (the logical value false is converted to 0, and the true is converted to 1, note that the logical value is displayed in the center by default. If it is displayed on the left, the true or false values of the table name are not calculated ).

3. If the parameter is an array or reference, only the number is calculated. Empty cells, logical values, text, or error values in an array or reference will be ignored (this is important, please separate them by second point ).

4. If the parameter is an error value or a text that cannot be converted to a number, an error will occur.

Sum can specify up to 30 parameters in plaintext. Parameters can be numbers and can be converted to text characters, logical values (note what is a logical value), arrays or references. When it is an array or reference, non-numeric parameters are ignored. Because the parameters can be arrays or references, the parameters can indirectly break through 30 plaintext limits.

SumifFunction:

Function: sums several cells according to the specified conditions.

Syntax: sumif (range, criteria, sum_range)

Range is the area of cells used for condition determination.

Criteria is a condition for determining which cells will be added and summed. The form can be a number, expression, or text. For example, a condition can be expressed as 32, "32", "> 32", or "Apples ".

Sum_range is the actual cell for summation.

Note:

1. The cells in sum_range are summed only when the corresponding cells in the region meet the conditions.

2. If sum_range is ignored, the cells in the region are summed.

 

Minus:

In Excel, this function is not subtracted, but how can this function be reduced by the subtraction method.

Use the sum function, for example, {= sum (C11,-(C11: C14 ))}

Adding "-" before a cell (region) can be converted to "subtraction". However, if it is a cell region, the preceding example must be input using an array formula.

 

Multiplication:

ProductFunction:

The sum function in the format is very similar. It is just a multiplication and addition, which is not mentioned here.

Product ({, 3}, {, 6}, {, 9}) = 1*2*3*4*5*6*7*8*9

However, there is a bug here (or a problem with the help translation of Excel). Text-type numbers cannot be calculated in this function.

SumproductFunction:

A maximum of 30 parameters are allowed, but these parameters are different from others. They must be arrays or cell references with the same dimension.

This function is used to multiply all elements of two arrays and then add the product.

Sumproduct ({1, 2, 3}, {4, 5, 6}, {7, 8, 9 })= 1*4*7 + 2*5*8 + 3*6*9

 

Except:

This function is not available in Excel.

 

There are also some tips for doing operations:

(1) calculate the average value of the number and ignore the value 0.

= Average (if (A2: A7 <> 0, A2: A7, ""), of course, this formula must also come from the array formula.

(2) Hide the Error Code except 0

= If (b1 = 0, "", A1/B1) OR = If (iserror (A1/B1), 0, A1/B1)

Of course, can the formula and function be used to complete addition, subtraction, multiplication, division? The answer is yes, it is implemented through selective pasting. It is too simple to talk about it.

If function is mentioned here, you can also list it by the way.

If Function

Purpose: Perform true/false value determination and return different results based on the true/false values of logical calculation.

Syntax: If (logical_test, value_if_true [, value_if_false])

Note:

1. The function if can be nested with a maximum of seven layers, but it can also be broken through.
2. After the value_if_true and value_if_false parameters are calculated, the IF function returns the returned values after the corresponding statement is executed.
3. If the if parameter of the function contains an array, every element in the array will be calculated when the if statement is executed (see the example above ).

CountifFunction

 Purpose:Calculates the number of cells in the region that meet the given conditions.

Syntax:Countif(Range,Criteria)

Range is the area of cells that need to calculate the number of cells that meet the conditions.

Criteria is a condition used to determine which cells will be calculated. The format can be numbers, expressions, or text. For example, a condition can be expressed as 32, "32", "> 32", or "Apples ".

 

Since we talk about the branches, we can't help but mention the three functions related to logical operations:And (), or (), not ()

And (logical1, logical ,...)

Logical1, logical,... indicates the values of 1 to 30 conditions to be detected. Each condition value can be true or false.

Description

1. The parameter must be a logical value of true or false, or an array or reference containing the logical value.
2. If an array or reference parameter contains text or blank cells, these values are ignored.
3. If the specified cell area contains non-logical values, and returns the error value # Value !.

Or (logical1, logical ,...)

The syntax and description of or () are the same as that of and (). However, you can use the OR array formula to check whether the array contains specific values.

Not (logical)

It is easy to obtain the inverse.

Of course, * Two logical expressions are linked to indicate the relation "and", and "+" indicates the relation "or"
= Sum (if (A2: A7 = "Zhang San") + (A2: A7 = "Li Si"),) counts the number of occurrences of Zhang San and Li Si in A2: A7.

= Sum (if (B2: B7 <10000) * (B2: B7> 50000), 10000) calculates the number of people who sell B2: B7 between 50000 and.

= Sum (if (A2: A7 = "James", if (B2: B7 <30000, 1, 0): calculates the number of orders with the sales of Michael in A2: A7.

(Note: The above are all array formulas)