Buzz
Statistical analysis is crucial for effective business planning. Utilizing fast statistical functions in Excel is essential for accurate calculations. This article helps you understand these statistical functions.
1. Conditional Average Function
The conditional average function has two forms:
- Calculating average with one condition: Averageif
- Calculating average with multiple conditions: Averageifs
a) Averageif Function
Meaning: This function computes the average of numerical values in a given data range based on a specified condition.
Syntax: Averageif(Range, Criteria, Average_range)
Where:
- Range: Data range containing the condition for calculating the average value.
- Criteria: Condition for calculating the average value, which can be a cell address containing the condition or directly input the condition in the formula.
- Average_range: Data range containing the values to calculate the average based on the condition.
Note:
- Values to calculate the average must be numerical values.
- If the condition expression is not found in the database => the function returns the error value #DIV/0!
- In the condition expression, if there are wildcard characters such as ? and *, you need to precede those characters with ~.
For example:
If the data range for calculating the average is not numeric -> Excel does not prompt you to re-enter the data.
b) The Averageifs function
Meaning: The function calculates the average of numerical values within a given data range that satisfy more than one condition.
Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Where:
- Average_range: The data range containing the values to calculate the average based on the condition.
- Criteria1, criteria2: Conditions for calculating the average, which can be cell references or directly entered conditions in the formula, with a maximum of 127 conditions.
- Criteria_range1, criteria_range2: Data ranges containing the corresponding conditions for Criteria1, criteria2… respectively, with a maximum of 127 condition ranges.
Note:
- The values used to calculate the average must be numerical values.
- If the condition expression is not found in the database => the function returns the error value #DIV/0!
- When the condition expression contains wildcard characters like ? and *, you need to prefix the condition expression with ~.
For example:
Make sure the conditions, whether in the form of calculation expressions or characters, are enclosed in double quotes.
2. Conditional Count Function
a) Countif Function
Purpose: Count the number of cells that meet a single condition within a range.
Syntax: Countif(range, criteria)
In this context:
- range: The data range containing the information to be counted or analyzed.
- criteria: The counting condition.
Note:
- When using commas to separate thousands in numeric data, ensure that you correctly include the comma value when writing conditions in the data table.
For example:
b) Countifs Function
Purpose: Count the number of cells that meet more than one condition in a specified data range.
Syntax: Countif(criteria_range1, criteria1, criteria_range2, criteria2, …)
Where:
- criteria_range1, criteria_range2,…: Data range containing the conditions to count.
- criteria1, criteria2,…: Conditions corresponding to the data ranges criteria_range1, criteria_range2,…
For example:
3. Function to find the maximum, minimum value with conditions
a) Dmax Function
Meaning: The Dmax function returns the maximum value in a data range with specified conditions.
Syntax: =DMAX(database, field, criteria)
Where:
- database: the list or database related including Column Headers. If the database does not contain column headers, the result will be incorrect.
- field: the field (column) from which to retrieve the maximum value. You can directly input the column header in double quotes or a number representing the column position in the database: 1 for the first column, 2 for the second column..., you can also input the cell containing the column header to use, for example B3, C1...
- criteria: refers to a range of cells containing conditions. You can select any range as long as it contains at least one Column Header and a cell below the column header containing the condition for the column where the column header matches the column header in the data table.
Note:
- It is advisable to place the criteria range on the worksheet so that when adding data, the range containing the criteria does not change.
- Separate the criteria range without inserting into the list or database to be processed.
- criteria must contain at least one Column Header and one cell containing the condition below the column header.
b) Dmin Function
Meaning: The Dmin function returns the smallest value in a data range given a condition.
Syntax: =DMIN(database, field, criteria)
Trong đó:
- database: The list or database related including Column Headers. If the database does not contain column headers, the result will be incorrect.
- field: The field (column) from which to retrieve the maximum value. You can directly input the column header in double quotes or a number representing the column position in the database: 1 for the first column, 2 for the second column, and so on. You can also input the cell containing the column header you want to use, for example, B3, C1, etc.
- criteria: is a range of cells containing the condition. You can select any range as long as it contains at least one Column Header and the cell below the column header contains the condition for the column where the column header matches the column header in the data table.
Note:
- It is recommended to place the criteria range on the worksheet so that when adding data, the criteria range does not change.
- The criteria range should be separated and not inserted into the list or database to be processed.
- The criteria must contain at least Column Headers and one cell containing the condition below the column header.
Example:
The Dmax function returns the largest number in the data field. Therefore, if the value is not a numeric data, Excel will prompt you to re-enter the data.
The above article introduces you to how to use some conditional statistical functions in Excel, hoping to be helpful to you. Wish you success!
