Common calculation functions in Excel, formulas and examples

Excel is an indispensable tool for calculating and managing data. Common calculation functions in Excel such as SUM, AVERAGE, MIN/MAX will help you save time and work more effectively.

Common calculation functions in Excel, formulas and examples Picture 1

Common calculation functions in Excel

Note: In this article, Free Download will summarize the basic functions in Excel , which are used by most people when working.
 

Contents:
1. SUM AND SUMIF functions
2. Average, SUMPRODUCT functions
3. MIN, MAX, LARGE, SMALL functions
4. Data counting functions
5. Logic functions
6. Mathematical functions in Excel

1. SUM AND SUMIF functions in Excel

SUM function

The SUM function is used to calculate the sum of all values ​​in a range or list of numbers.

Syntax: =SUM(number1;[number2]; .)

Parameters: Number1, Number2… are the numbers to sum.

Common calculation functions in Excel, formulas and examples Picture 2 SUM is a common calculation function in Excel that you need to remember.

SUMIF function

The SUMIF function is used to sum values ​​in a range that meet a specific condition.

Syntax: SUMIF(range; criteria; [sum_range])

Parameters:
- Range: Is the range you want to define.
- Criteria: The criteria you want to sum. This criteria can be numbers, expressions or strings.
- Sum_range: Are the actual cells to sum.

Common calculation functions in Excel, formulas and examples Picture 3
 

2. Average and SUMPRODUCT functions in Excel

AVERAGE function

The AVERAGE function helps you calculate the average of values ​​in a range or list of numbers without including non-numeric values ​​or blank cells.

Syntax: =AVERAGE(number1, number2…)

Parameters: number1, number2 … are the numbers whose average value needs to be calculated.

For example : To calculate the average from column A1 to A5, enter the formula: =AVERAGE(A1:A5) or =AVERAGE(10;20;30;40;50).

Common calculation functions in Excel, formulas and examples Picture 4 Common calculation function in Excel: AVERAGE 
 

SUMPRODUCT function

The SUMPRODUCT function calculates the sum of products between arrays or ranges in Excel, you can combine with conditions to perform more complex calculations.

Syntax: SUMPRODUCT(Array1; Array2; Array3; …)

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

For example, suppose you have the following data table, if you want to calculate the sum of the products of column A and column B (ie 1×10 + 2×20 + 3×30 + 4×40), you use the following formula: =SUMPRODUCT(A1:A4; B1:B4).

Common calculation functions in Excel, formulas and examples Picture 5

3. Min, Max, LARGE, SMALL functions in Excel

Min and Max are the two most commonly used calculation functions in Excel. Each function will have different syntax and references. 

MAX function

Purpose: Returns the smallest value in a set of numbers.

Syntax: =MIN(number1; [number2]; .)

Parameters: Number1, Number2… are the arrays in which you want to find the largest value.

For example, in the data below, to find the smallest value in the range A1:A5, you use the formula: =MIN(A1:A5).

Common calculation functions in Excel, formulas and examples Picture 6 Common calculation function in Excel: MIN

LAGRE function

Find the kth largest number in an entered array.
Syntax: =LARGE(Array; k)
Parameters: Array: Is an array or a data range.
                    k: Is the rank of the number you want to find from the largest number in the array.

MIN function

Returns the smallest number in the entered array. 

Syntax: =MIN(Number1; Number2; …)

Parameters: Number1, Number2… are the arrays in which you want to find the smallest value.

SMALL function

Find the kth smallest number in an entered array.
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 starting from the smallest number in the array
 

4. Data counting function in Excel

COUNT function

This function is used to count cells containing numeric data in an array.
Syntax: COUNT(Value1; Value2, …)

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

COUNTA function

Count all cells containing data.
Syntax: COUNTA(Value1; Value2; …)

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

COUNTIF function

This function counts cells containing numeric values ​​according to a given condition.
Syntax: COUNTIF(Range; Criteria)

Parameters:
- Range: The range of data you want to count.
- Criteria: The criteria for the cells to be counted.

Download Excel 2013

5. Logical Functions in Excel

AND function

With the syntax: AND (Logical1; Logical2; ….)

- Arguments: Logical1, Logical2… are conditional expressions.

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

For example: With the data table below, if you want to check the results with 2 conditions:

- A1 is greater than 5.

- B1 is greater than 15.

Then the formula will be: =AND(A1>5; B1>15)

Common calculation functions in Excel, formulas and examples Picture 7

OR function

With syntax: OR (Logical1, Logical2…)

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

- The OR function returns TRUE (1) if any of its arguments are true and returns FALSE (0) if its arguments are false.

NOT function

Has syntax: NOT(Logical)

- With the Logical argument being a value or a logical expression

- The NOT function reverses the value of an argument. The function is used when you want to negate the value of an argument in this operation.

6. Mathematical functions in Excel 

The next calculation function in Excel is the math function. Please pay attention to know the syntax and use the function correctly. 

ABS function

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

- The number argument is a numeric value, a reference, or an expression.

For example, to get the absolute value of number A1 in the table below, the formula would be: =ABS(A1)

If A1 = -10, the result will be 10.

Common calculation functions in Excel, formulas and examples Picture 8 Common calculation function in Excel: ABS
 

POWER function

The function returns the result of a number raised to a power.
With the syntax: POWER(number; power)

- Number parameter: Any real number

- Power: Is the exponential function that you want to raise the base number to the power

PRODUCT function

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

- Where the parameters: Number1 is the first number you want to multiply
                             Number2 are the additional numbers or ranges you want to multiply, up to a maximum of 255 arguments

For example, let's say you want to calculate the product of the numbers: 2, 3, and 4.

The formula would be: =PRODUCT(2; 3; 4) or =PRODUCT(A1:A3).

Common calculation functions in Excel, formulas and examples Picture 9

MOD function

Is a function that returns the remainder after dividing a number by a divisor, the result has the same sign as the divisor.

Syntax: MOD(number; divisor)

- Arguments: Number is the dividend
                   Divisor is the divisor

ROUNDUP function

This function rounds a number towards 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 that you want to round the number to.

EVEN function

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

With syntax: EVEN(Number)

- In which the number parameter is the value to be rounded.

ODD function

This function rounds up to the nearest odd integer.

Syntax: ODD(Number)

- Parameter: Number is the number you want to round

ROUNDDOWN function

This function helps you round down a number.

Syntax: ROUNDDOWN(number; num_digits)

- Parameters:

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

In the above article, Free Download has compiled common calculation functions in Excel 2016, 2013, 2010, 2007, 2003. Hopefully it will help readers in their work. If interested, you can refer to the financial functions in Excel. Thank you for following the article.

Common functions are often used throughout the process of learning and working with Excel. Common functions include basic functions as well as advanced functions. If you have mastered some basic functions in Excel, then with advanced functions  in Excel  you will improve your level.

Isabella Humphrey

Update 09 December 2024