How to use conditional statistical functions in Excel
Statistical data is an important step to help you plan your business process. In the calculation process you cannot help but use the quick statistical functions in Excel. In this article, you will learn about statistical functions.
How to use conditional statistical functions in Excel Picture 1
1. Conditional average function
With conditional average function, there are 2 types:
- Calculate the average with 1 condition: Averageif
- Calculate the average with many events: Averageifs
a) Averageif function
Meaning: The function performs the arithmetic mean of numerical values in a given data area under a certain condition.
Syntax: Averageif (Range, Criteria, Average_range)
Inside:
- Range: The data area contains the conditions for calculating the average value.
- Criteria: The condition for calculating the average value, can be the address box containing the condition or directly enter the condition in the formula.
- Average_range: The data area contains the values to calculate the average value according to conditions.
Attention:
- The value to calculate the average must be a numerical value.
- If the conditional expression is not in the database => the function returns the # DIV / 0 error value!
- In the conditional expression that contains alternative characters such as? and * you need to put a ~ sign before the conditional expression.
For example:
How to use conditional statistical functions in Excel Picture 2
If the data range for the average is not a numeric data type -> Excel does not issue a message asking you to re-enter the data.
How to use conditional statistical functions in Excel Picture 3
b) Averageifs function
Meaning: The function performs the arithmetic mean of given numeric values in the data area that satisfy more than one condition.
Syntax: AVERAGEIFS (average_range, criteria_range1, criteria1, criteria_range2, criteria2, .)
Inside:
- Average_range: The data area contains the values to calculate the average value according to conditions.
- Criteria1, criteria2: The condition for calculating the average value, can be the address box containing the condition or directly enter the condition in the formula, up to 127 conditions.
- Criteria_range1, criteria_range2: The data area contains conditions corresponding to Criteria1, criteria2 . corresponding to a maximum of 127 condition areas.
Attention:
- The value to calculate the average must be a numerical value.
- If the conditional expression is not in the database => the function returns the # DIV / 0 error value!
- In the conditional expression that contains alternative characters such as? and * you need to put a ~ sign before the conditional expression.
For example:
How to use conditional statistical functions in Excel Picture 4
Notice the conditions in the form of calculation expressions or characters are in quotation marks.
2. Conditional counting function
a) The Countif function
Meaning: Counts the number of cells that meet 1 condition in the range.
Syntax: Countif (range, criteria)
Inside:
- range: The data area containing the data to count or statistics.
- criteria: Counting conditions.
Attention:
- When you use commas to separate the thousands in numeric data, pay attention when writing conditions that you write the correct comma values in the data table.
For example:
How to use conditional statistical functions in Excel Picture 5
b) Countifs function
Meaning: Counts the number of cells that meet more than 1 condition in the specified data area.
Syntax: Countif (criteria_range1, criteria1, criteria_range2, criteria2, .)
Inside:
- criteria_range1, criteria_range2, .: The data area containing the conditions to be counted.
- criteria1, criteria2, .: The condition corresponds to the data regions criteria_range1, criteria_range2, .
For example:
How to use conditional statistical functions in Excel Picture 6
3. The function finds the maximum, minimum, conditional values
a) Dmax function
Meaning: The Dmax function returns the maximum value in a data range with a given condition.
Syntax: = DMAX (database, field, criteria)
Inside:
- database: list or related database including column headers. If the database does not contain a column header, the result will be incorrect.
- field: the field (column) needs to get the maximum value. You can enter the column header directly in quotation marks or some representing the column position in the database: 1 for the first column, 2 for the second column, etc., you can also enter the cell containing the required column header. Using example B3, C1 .
- criteria: is the range of cells that contain conditions, you can choose any as long as the range contains at least one column header and the cell below the column header contains conditions for the column in which the column header coincides with the title. column in the data table.
Notes :
- You should set the criteria range on the worksheet so that when adding data, the range of conditions does not change.
- The scope of the conditions that need to be separated is not inserted into the list or database to be processed.
- Criteria must contain at least column headers and a cell containing conditions under column headers.
b) Dmin function
Meaning: The Dmin function returns the smallest value in a data range with a given condition.
Shot law : = DMIN (database, field, criteria)
Inside:
- database: list or related database including column headers. If the database does not contain a column header, the result will be incorrect.
- field: the field (column) needs to get the maximum value. You can enter the column header directly in quotation marks or some representing the column position in the database: 1 for the first column, 2 for the second column, etc., you can also enter the cell containing the required column header. Using example B3, C1 .
- criteria: is the range of cells that contain conditions, you can choose any as long as the range contains at least one column header and the cell below the column header contains conditions for the column in which the column header coincides with the title. column in the data table.
Notes :
- You should set the criteria range on the worksheet so that when adding data, the range of conditions does not change.
- The scope of the conditions that need to be separated is not inserted into the list or database to be processed.
- Criteria must contain at least column headers and a cell containing conditions under column headers.
For example:
How to use conditional statistical functions in Excel Picture 7
The Dmax function gives the largest numeric value in the data field so if the value is not numeric data -> Excel requires you to re-enter the data.
How to use conditional statistical functions in Excel Picture 8
The above article introduces you to using some conditional statistical functions in Excel hoping to help you. Good luck!
You should read it
- The syntax and function of each function in the group of statistical functions in Excel
- The MIN and MAX functions (the smallest and largest value functions) in Excel
- How to use Conditional Formatting to conditional formatting in Excel
- Common statistical functions in Excel
- Instructions on how to use the Dmax function in Excel
- Summary of trigonometric functions in Excel
- Ms Excel - Lesson 13: Use conditional formatting in Excel
- How to use conditional formatting in Microsoft Excel 2016
- Conditional counting function in Excel
- How to fix the SUM function doesn't add up in Excel
- How to calculate and color blank cells in Excel
- How to create Progress bar using conditional formatting in Excel 2013, 2010 and 2007
May be interested
Wings calculates the number of days, the difference between two dates in Excel
How to use Group to hide, show rows and columns in Excel 2007, 2010, 2013, 2016, 2019
Cell counting function with data in Excel - Enclose example
How to create Header and Footer borders in Word 2019, 2016, 2013, 2010 and 2007
How to use SUMIF with 2 or more conditions in Excel
How to join PDF files with Foxit Reader