Common calculation functions in Excel

Excel is a spreadsheet that supports functions that help you in the process of calculating and processing data, if you know the calculation functions, how to use them, your work will be processed more quickly.

Common calculation functions in Excel Picture 1

The following article lists the common calculation functions in Excel.

I. STATISTICS GROUP.

A. Group function to sum.

1. The SUM function.

Syntax: SUM (Number1, Number2 .).

Parameters: Number1, Number2 . are the numbers to be summed.

Function: Adds all numbers in a selected range.

For example: = SUM (D7: D12) sums the values ​​from cell D7 to cell D12.

Common calculation functions in Excel Picture 2

2. The SUMIF function.

Syntax: SUMIF (Range, Criteria, Sum_range).

Parameters:

+ Range: is the range of numbers you want to identify.

+ Criteria: condition, criteria you want to sum (be it number, expression or string).

+ Sum_range: is the real cell to sum.

Function: Sums the cells specified by the criteria entered.

For example: = SUMIF (A1: A5, "Male", B1: B5) sums cells B1 through B5 provided that the values ​​in columns A1 through A5 are Nam.

B. Group function calculates the average value.

1. The AVERAGE function.

Syntax: AVERAGE (Number1, Number2 .).

Parameters: Number1, Number2 . are numbers to calculate the average value.

Function: Returns the average of the arguments.

For example: = AVERAGE (D7: D12) calculates the average of the cells from D7 to D12.

Common calculation functions in Excel Picture 3

2. SUMPRODUCT function

Syntax: SUMPRODUCT (Array1, Array2, Array3 .).

Parameters: Array1: required, the first array argument you want to multiply its components and then add.

Array2, Array3 . optionally, the array arguments from 2 to 255 that you want to multiply its components and then add up.

Note: Arguments in ranges must be the same direction, otherwise the function will return the #VALUE error value.

Function: Take the products of the given sequences, then sum them.

C. Function group finds maximum and minimum values.

1. The MAX function.

Syntax: MAX (Number1, Number2 .).

Parameters: Number1, Number2 . is the range in which you want to find the largest value in it.

Function: The function returns the largest number in the entered range.

For example, = MAX (A5: A9) gives the largest value in cells A5 through A9.

2. LAGRE function.

Syntax: LARGE (Array, k).

Parameters:

+ Array is an array or a range of data.

+ k is the rank of the number you want to find since the largest number in the range.

Function: Find the kth major in a range to be entered.

3. The MIN function.

Syntax: MIN (Number1, Number2 .).

Parameters: Number1, Number2 . is the range in which you want to find the smallest value in it.

Function: The function returns the smallest number in the entered range.

For example: = MIN (A4: A7) returns the smallest number of cells from A4 to A7.

4. The SMALL function.

Syntax: SMALL (Array, k).

Parameters:

+ Array is an array or a range of data.

+ k is the rank of the number you want to find since the smallest number in the range.

Function: Finds the kth smallest number in an entered range.

D. Data counting function group.

1. The COUNT function.

Syntax: COUNT (Value1, Value2 .).

Parameters: Value1, Value2 . are arrays or ranges of data.

Function: Counting cells containing numeric data in a range.

For example, = COUNT (D7: D12) counts cells that contain numeric data in the range from cell D7 to cell D12.

Common calculation functions in Excel Picture 4

2. The COUNTA function.

Syntax: COUNTA (Value1, Value2 .).

Parameters: Value1, Value2 . are arrays or ranges of data.

Function: The function counts all cells containing data.

For example, = COUNTA (A1: A7) counts cells that contain data in the range from cell A1 to cell A7.

3. The COUNTIF function.

Syntax: COUNTA (Range, Criteria).

Parameters:

+ Range: the data range you want to count.

+ Criteria: conditions, criteria for counting cells.

Function: The function counts cells that contain values ​​according to a given condition.

For example: = COUNTIF (A1: A8, "<50") counts all cells A1 through A8 that contain numbers less than 50.

II. LOGIC FUNCTION.

1. AND function.

Syntax: AND (Logical1, Logical2 .).

The arguments: Logical1, Logical2 . are conditional expressions.

Function: The function returns TRUE (1) if all of its arguments are true, returns FALSE (0) if one or more of its arguments are false.

Note:

+ Arguments must be logical values ​​or arrays or references containing logical values.

+ If the reference argument is text value or Null (empty), those values ​​are ignored.

+ If the reference area does not contain a logical value, the function returns the #VALUE!

For example: = AND (C7 = "Female", D7 = 7) because both expressions are true on the return value is TRUE.

Common calculation functions in Excel Picture 5

2. OR function.

Syntax: OR (Logical1, Logical2 .).

The arguments: Logical1, Logical2 . are conditional expressions.

Function: The function returns TRUE (1) if any of its arguments are true, returns FALSE (0) if all of its arguments are false.

Example: = OR (A1 <10, A3> 100)

+ If A1 is less than 10 or A3 is less than 10, the function returns TRUE.

+ If A1 is greater than 10 and A3 is less than 100, the function returns FALSE value.

3. The NOT function.

Syntax: NOT (Logical).

Arguments: Logical is a value or a logical expression.

Function: The function reverses the value of an argument, using NOT when you want to negate the value of the argument.

III. MATHEMATICS GROUP.

1. ABS function.

Syntax: ABS (Number).

Argument: Number is a numeric value, a reference, or an expression.

Function: Get the absolute value of a number.

For example: = ABS (D10) returns the absolute value of cell D10.

Common calculation functions in Excel Picture 6

2. POWER function.

Syntax: POWER (Number, Power).

Parameters:

+ Number is a real number that you want to get power.

+ Power is the exponent.

Function: The function returns the power of a number.

For example: = POWER (10.2) the result is 100.

3. PRODUCT function.

Syntax: PRODUCT (Number1, Number2 .).

The parameters: Number1, Number2 . are the numbers you want to multiply.

Function: Use the Product function instead of the multiplication operator to calculate the product of a range.

For example: = PRODUCT (A1, A5) multiplies the numbers in the range from A1 to A5.

4. The MOD function.

Syntax: MOD (Number, divisor).

The arguments:

+ Number is the divided number.

+ divisor is the divisor.

Function: Get the remainder value of the division.

For example: = MOD (25,2) the return value is 1.

5. ROUNDUP function

Syntax: ROUNDUP (Number, Num_digits).

Parameters:

+ Number is a real number that you want to round up.

+ Num_digits is the decimal point you want to round.

Function: Rounding up a number.

Note:

+ If Num_digits> 0 will round the decimal.

+ If Num_digits = 0 will round up to the nearest natural number.

+ If Num_digits

6. The EVEN function.

Syntax: EVEN (Number).

Parameter: Number is the number you want to round.

Function: Rounds up to the nearest even integer.

Note: If number is not a numeric type, the function returns the #VALUE! Error.

7. ODD function

Syntax: ODD (Number).

Parameter: Number is the number you want to round.

Function: Rounds up to the nearest odd integer.

8. ROUNDDOWN function.

Syntax: ROUNDDOWN (Number, Num_digits).

Parameters:

+ Number is a real number that you want to round up.

+ Num_digits is the decimal point you want to round.

Function: Rounds down a number.

With you working in accounting or office, the contact with Excel is regular. Hopefully the article above will help you process and calculate data more quickly and accurately. Good luck!