- 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.
.
