The basic calculation functions in Excel

Those who work in the field of accounting, administrative personnel often have to work with Excel spreadsheets, it is necessary to master the common functions in Excel.

Microsoft Office 2010 Microsoft Office 2016

These are basic functions, predefined, you can combine functions together to calculate. Invite you to follow the article:

The group function sums in Excel 2016

SUM function: Sum

The SUM function is the most commonly used sum function, and can quickly and quickly calculate a total of columns and rows.

Syntax: SUM (Number1, Number2 ..).

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

For example: = SUM (A2: A10) sums the values ​​from cell A2 to cell A10.

SUMIF function: Calculate conditional sum

The SUMIF function is a conditional sum function that can combine with many other functions. Used when it is necessary to sum a certain area of ​​an Excel worksheet according to a given condition.

Syntax: SUMIF (Range, Criteria, Sum_range).

Parameters:

• Range: The sequence of numbers that you want to determine.
• Criteria: The condition and criteria you want to sum (can be numbers, expressions or strings).
• Sum_range: These are the cells that really need to be summed.

For example: = SUMIF (B1: B5, "Female", C1: C5). Sum cells C1 through C5, provided the values ​​in columns B1 through B5 are Female.

SUMIFS function: Sum multiple conditions

The SUMIF function is a conditional sum function that can combine many different conditions. Used when it is necessary to sum an area in an Excel spreadsheet under a variety of conditions.

Syntax: SUMIFS (Sum_range; Range1; Criteria1; [Range2; ...]; [Criteria2; ...])

Parameters:

• Sum_range: These are the cells that really need to be summed.
• Range: The sequence of numbers that you want to determine.
• Criteria: The condition and criteria you want to sum (can be numbers, expressions or strings).

For example: = SUMIFS (A1: A10; B1: B10; "> 20"; C1: C10; "<30"). Sum cells A1 through A10 provided that the values ​​in cells B1 through B10 are greater than 20 AND the values ​​in cells C1 through C10 are smaller than 30.

Logic function group in Excel

AND function: Conditional function AND

The AND function is used a lot in Excel, with TRUE and FALSE results returned. The function returns TRUE (1) if all arguments are true, returns FALSE (0) if one or more arguments are false.

Syntax: AND (Logical1, Logical2 ...)

In which: Logical1, Logical2 ... are conditional expressions.

Note:

• The arguments must be either logical values ​​or arrays that contain logical values.
• If the reference arguments are text values ​​or null (null) then those values ​​are ignored.
• If the reference range does not contain a logical value, the function returns the #VALUE! Error.

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

OR function: Conditional function OR

The OR function is a logical function in Excel. Returns TRUE (1) if any of the arguments are true, returns FALSE (0) if all arguments are false.

Syntax: OR (Logical1, Logical2 ...).

The arguments: Logical1, Logical2 ... are conditional expressions.

Example: = OR (B1 <20, B3> 100)

• If B1 is less than 20 or B3 is less than 20, the function returns TRUE.
• If B1 is greater than 20 and B3 is less than 100, the function returns the FALSE value.

NOT function: Negative conditional function

The NOT function is the function that takes the inverse of a value you already know the result of, using the NOT function when you want to negate the value of the argument.

Syntax: NOT (Logical).

Arguments: Logical is a value or a logical expression.

The function group calculates the average value in Excel

AVERAGE function: Calculate the average value

The AVERAGE function is the average function of a series of numbers, enabling faster calculations if the number of elements in the range is large and long.

Syntax: AVERAGE (Number1, Number2 ...).

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

For example: = AVERAGE (A10: J10). Calculate the average value of cells A10 through J10.

SUMPRODUCT: Calculate the sum of products

The SUMPRODUCT function takes the product of the input ranges, and then sums those products.

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

Parameters:

• Array1: Required, the first array argument you want to multiply its components and then add up.
• 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.

The function group finds the largest and smallest values ​​in Excel

MAX function : Find the maximum value

The MAX function returns the largest number in the entered range.

Syntax: MAX (Number1, Number2 ...).

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

For example, = MAX (B1: B5) gives the maximum value in cells B1 through B5.

MIN function: Find the minimum value

The MIN function returns the smallest number in the entered range.

Syntax: MIN (Number1, Number2 ...).

Parameters: Number1, Number2 ... are ranges that want to find the smallest value.

For example: = MIN (B1: B7). Returns the smallest number of cells B1 through B7.

LAGRE: Find the largest value k

LAGRE is the function that returns the largest value k.

Syntax: LARGE (Array, k).

Parameters:

• Array: An array or a range of data.
• k: Is the rank of the number you want to find since the largest number in the sequence.

SMALL function: Find the smallest value k

The SMALL function is the function that finds the smallest k number in an input range.

Syntax: SMALL (Array, k).

Parameters:

• Array: An array or range of data.
• k: Is the rank of the number you want to find from the smallest number in the sequence.

Group function for counting data in Excel

COUNT function: Counts numeric data

The COUNT function is used to count data, objects in a series, or in a certain worksheet.

Syntax: COUNT (Value1, Value2 ...).

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

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

COUNTA function: Counting data

The COUNTA function is a function that counts all cells containing data.

Syntax: COUNTA (Value1, Value2 ...).

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

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

COUNTIF function: Conditional counting

The COUNTIF function helps to count and count the number of employees in a spreadsheet such as calculating the number of employees in the company, counting how many male employees, how many female employees ...

Syntax: COUNTA (Range, Criteria).

Parameters:

• Range: The range of data you want to count.
• Criteria: Conditions and criteria for counting cells.

For example: = COUNTIF (B1: B9, "<20"). Counts all cells B1 through B9 containing numbers less than 20.

COUNTIFS function: Count multiple conditions

The COUNTIFS function helps to count, count the number in a worksheet such as the number of employees in the company, count how many male employees, how many female employees ... with many other conditions.

Syntax: COUNTIFS (Range1; Criteria1; [Range2; ...]; [Criteria2; ...])

Parameters:

• Range: The range of data you want to count.
• Criteria: Conditions and criteria for counting cells.

For example: = COUNTIFS (A1: A10; "> 20"; B1: B10; "<30"). Counts all cells in the range A1 to A10 containing numbers greater than 20 AND cells in the range B1 to B10 less than 30.

Math function group in Excel

ABS function: Calculate absolute value

ABS function is the function that calculates the absolute value of a number or an arithmetic expression.

Syntax: ABS (Number).

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

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

POWER function: Power of a number

The POWER function is a power function of a number, given a base number.

Syntax: POWER (Number, Power).

Parameters:

• Number: A real number you want to get a power.
• Power: Exponential.

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

PRODUCT function: Calculate product

The PRODUCT function helps multiply all the numbers given together, then return their product quickly.

Syntax: PRODUCT (Number1, Number2 ...).

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

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

MOD function: Get the remainder of the division

The MOD function is a function that takes the remainder of a division to combine and use with other functions that perform calculations on an Excel spreadsheet.

Syntax: MOD (Number, divisor).

The arguments:

• Number: The number to be divided.
• Divisor: A divisor.

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

ROUNDUP function: Rounds a number to a given decimal place

The ROUNDUP function helps round up decimal numbers in Excel worksheet quickly.

Syntax: ROUNDUP (Number, Num_digits)

Parameters:

• Number: A real number that you want to round up.
• Num_digits: The decimal places you want to round.

Note:

• If Num_digits> 0 will round the decimal.
• If Num_digits = 0 will round up to the nearest natural number.
• If Num_digits <0 will round the whole part after the decimal point.

EVEN function: Rounds to the nearest even integer

The EVEN function rounds up to the nearest even integer.

Syntax: EVEN (Number).

Parameter: Number is the number you want to round.

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

ODD function: Rounds to the nearest odd integer

The ODD function helps round up to the nearest odd integer.

Syntax: ODD (Number).

Parameter: Number is the number you want to round.

ROUNDDOWN function: Rounds down 1 number

The ROUNDDOWN function rounds down a number.

Syntax: ROUNDDOWN (Number, Num_digits).

Parameters:

• Number: The actual number you want to round up.
• Num_digits: The decimal places you want to round.

Mastering the above common Excel functions, you can easily apply to your calculations and statistics. In the process of using Excel you can use the function Index , VLOOKUP ...

I wish you successful implementation!