Common calculation functions in excel

NOTE: Download Now 70+ basic and advanced excel exercises

You are frequently exposed to calculating and using Excel functions. Surely the release of the full version of Microsoft Office 2013, you are also familiar with trigonometric and mathematical functions in Excel and many other mathematical functions.

Common functions in Excel

In today’s article, Taimienphi.vn will summarize the calculation function the most common in Excel Basic to help you more convenient in your calculation, the function is the basic function in Excel used by most users.

Common functions are often used throughout learning and working with excel, common functions include basic and advanced functions, if you have already learned some basic functions in excel, then with Advanced functions in Excel, you should also learn to improve your level.

Summary of common calculation functions in Excel

In Excel spreadsheets There are quite a few calculation functions, from the basic calculation functions to the advanced ones, all of which we will update in this article. All of Excel’s calculation functions below are used by Taimienphi.vn on Office version Excel 2013, you can completely apply to other versions of Office Excel as Excel 2016, 2010, 2007 or Excel 2003

1. Normal sum function, conditional sum function

SUM function

Adds all numbers in a selected range.
Syntax: SUM (Number1, Number2 …)
Parameters: Number1, Number2 … are the numbers to be summed.

SUMIF function

Sums the cells specified by the criteria entered.
Syntax: SUMIF (Range, Criteria, Sum_range)

Parameters:
– Range: The range that you want to define.
– Criteria: the criteria that you want to sum. This standard can be numbers, expressions or strings.
– Sum_range: The cells that really need to sum.

2. The function calculates the average value in Excel AVERAGE

AVERAGE function

Returns the mean value of the arguments.
Syntax: AVERAGE (number1, number2 …)

Parameters: number1, number2 … are numbers to calculate the average value.

For example:

Recipe E6 = AVERAGE (C6: D6)

SUMPRODUCT function

Take the product of the given series, then calculate the sum of those products
Syntax: SUMPRODUCT (Array1, Array2, Array3 …)

Parameters: Array1, Array2, Array3 … are the ranges of cells you want to multiply and then sum the products.

Attention

The arguments in the arrays must be in the same direction. Otherwise the function will return the #VALUE error value

3. The function finds the largest and smallest values ​​in Excel MAX, Min, LAGRE, SMALL

MAX function

Returns the largest number in the entered range.
Syntax: MAX- (Number1, Number2 …)
Parameters: Number1, Number2 … is the range in which you want to find the largest value in it

LAGRE function

Finds the kth major in an entered range.
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.

MIN function

Returns the smallest number in the entered range.
Syntax: MIN (Number1, Number2 …)

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

SMALL function

Finds the kth smallest number in an entered range.
Syntax: SMALL (Array, k)
Parameters: Array: An array or a range of data.
k: Is the rank of the number you want to find from the smallest number in the sequence

4. Data counting function

COUNT function

This function is used to count cells containing numeric data in a range
Syntax: COUNT (Value1, Value2, …)

With parameters: Value1, Value2 … are arrays or ranges of data.

COUNTA function

Counts all cells containing data.
Syntax: COUNTA (Value1, Value2, …)

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

COUNTIF function

This function has functions that count cells containing numeric values ​​according to a given condition.
Syntax: COUNTIF (Range, Criteria)

Parameters:
– Range: Range of data that you want to count.
– Criteria: The standard for counting cells.

Download version Excel 2013

5. Logic function

AND function

With the syntax: AND (Logical1, Logical2, …)

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

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

Attention

– 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!

Example 1: Use the AND function to know if the following formulas are true or false.

The formula for cell A6 is:= AND (A6 = “Nguyen Van Dat”, B6 = “Nam”), will return as TRUE

OR function

With the syntax: OR (Logical1, Logical2 …)

-In which arguments: Logical1, Logical2 … are conditional expressions.

– The OR function will return TRUE (1) if any of its arguments are true and FALSE (0) if its arguments are false.

NOT function

Syntax: NOT (Logical)

– Logical argument is a value or a logical expression

– The NOT function has the function to reverse the value of the argument. The function is used when you want to negate the value of the argument in this operation

6. Math functions in Excel

ABS function

A function that returns the absolute value of a number. The absolute value of a number is that number without a sign
Syntax: ABS (number)

– Number argument is a numeric value, a reference, or an expression.

For example: The formula D9 is: = ABS (C9) and we get the results:

POWER function

The function returns the result of a number raised by a factorial
With the syntax: POWER (number, power)

– Parameter Number: Is any real number

– Power: Exponential function that you want to raise the base power

PRODUCT function

You can use the PRODUCT function instead of the * operator to calculate the product of a range.
Syntax: PRODUCT (number1, number2 …)

– Where parameter: Number1 is the first number you want to multiply
Number2 is the additional numbers or ranges you want to multiply, up to 255 arguments

Attention

If an argument or reference is given, only the numbers in the array or reference will be multiplied

For example: Calculate each column’s product in the following data table:

MOD function

A function that returns the remainder after dividing a number by a divisor, resulting in the same sign as the divisor

Syntax: MOD (number, divisor)

– Arguments: Number is the dividend
Divisor is the divisor

Attention

If the divisor is 0, the MOD function will return the error value # DIV / 0!
The MOD function can be expressed by the terms of the INT function: MOD (n, d) = n – d * INT (n / d)

ROUNDUP function

This function helps round a number to 0

Syntax: ROUDUP (Number, Num_digits)

– Parameters: Number: Is a real number that you want to round up.
Number_digits: is the number of digits you want to round to

Attention

If Num_digits> 0 Will round the specified decimal
If Num_digits = 0 Will round to the nearest natural number
If Num_digits

EVEN function

A function that returns a number rounded up to the nearest even integer

With the syntax: EVEN (Number)

-In which parameter number is the value to be rounded

Attention

If Number is not a number type, the function returns the #VALUE!

ODD function

This function has the function of rounding up to the nearest odd integer

Syntax: ODD (Number)

-T Parameters: Number is the number you want to round

ROUNDDOWN function

This function helps you round down a number

Syntax: ROUNDDOWN (number, num_digits)

-The parameters:

Number is the real number you want to round
Num_digits is the number of digits you want to round to

https://thuthuat.taimienphi.vn/ham-tinh-toan-thong-dung-trong-excel-2013-1515n.aspx
The above article, Taimienphi.vn has summarized the basic calculation functions in Excel 2016, 2013, 2010, 2007, 2003,. Hope to help readers in their work. If interested, you can refer to the financial functions in Excel. Thank you for following the article.