Catégorie : Excel function

This function returns values of the cumulative beta distribution, which is commonly used to analyze variance across samples (e.g., modeling proportions like daily computer usage time).

Syntax:
BETA.DIST(x; alpha; beta; cumulative; [A]; [B])

Arguments:

• x (required): The value (between A and B) to evaluate.
• alpha (required): A shape parameter of the distribution.
• beta (required): A second shape parameter.
• cumulative (required): A logical value (TRUE = cumulative probability; FALSE = probability density).
• A (optional): Lower bound for x (default = 0).
• B (optional): Upper bound for x (default = 1).

Background

• The beta distribution models probabilities for variables bounded within a fixed interval (e.g., 0 to 1).
• alpha and beta control the distribution’s shape (see Figure below).
• If A and B are omitted, x must be between 0 and 1.

Example

Question:
What is the cumulative probability of x = 2 for a beta distribution with:

• Shape parameters alpha = 8, beta = 10
• Bounds A = 1, B = 3?

Formula:
BETA.DIST(2, 8, 10, TRUE, 1, 3)

Result: 0.68547 (see Figure below).

Interpretation:
There is a 68.547% probability that a randomly selected value from this distribution falls between 1 and 2.

Key Notes

1. Bounds: If A and B are specified, x must lie within [A, B].
2. Cumulative vs. Density:
   • TRUE: Returns the cumulative distribution (area under the curve up to x).
   • FALSE: Returns the probability density at x.
3. Applications: Useful for modeling proportions (e.g., task completion rates, survey responses).

This function calculates the average of cells that meet multiple specified criteria.

Syntax:
AVERAGEIFS(average_range; criteria_range1; criteria1; [criteria_range2; criteria2]; …)

Arguments:

• average_range (required): The range of cells to average (must contain numeric values).
• criteria_range1 (required): The first range to evaluate against criteria1.
• criteria1 (required): The condition applied to criteria_range1 (number, expression, cell reference, or text).
• criteria_range2, criteria2, … (optional): Additional ranges and their associated criteria (up to 127 pairs).

Background

For general details on averages, see the AVERAGE() function description. Like AVERAGEIF(), AVERAGEIFS() measures central tendency but allows for multiple filtering conditions.

Example

Continuing the email marketing analysis from AVERAGEIF():

• You previously calculated the average click rate per mailing type.
• Now, you want to refine the analysis by excluding low click rates (<10,000) to avoid outlier distortion.

Formula Setup:

• average_range: D2:D30 (click rates to average).
• criteria_range1: C2:C30 (mailing types, e.g., « Software A »).
  • criteria1: « Software A » (only include this mailing type).
• criteria_range2: D2:D30 (click rates again, for the second filter).
  • criteria2: « >10000 » (only include clicks >10,000).

Result:
The average click rate for « Software A » mailings exceeding 10,000 clicks is 15,221.50 (as seen below).

Key Notes

1. Order Matters: The first argument is always the range to average, followed by criteria pairs.
2. Flexibility: Supports numeric, text, and logical criteria (e.g., « >10000 », « =Completed »).
3. Exclusion Logic: Unlike AVERAGEIF(), AVERAGEIFS() requires all criteria to be met for a cell to be included.

This function calculates the average of all cells in a range that meet a specified criterion.

Syntax:
AVERAGEIF(range; criteria; [average_range])

Arguments:

• range (required): The range of cells to evaluate against the criteria. These can include numbers, names, arrays, or references.
• criteria (required): The condition that determines which cells are averaged. It can be a number, expression, cell reference, or text.
• average_range (optional): The actual cells to average. If omitted, the function uses the range argument for both criteria evaluation and averaging.

Background:
For general information on averages, see the description of AVERAGE().

The AVERAGEIF() function measures central tendency—the location of the center of a group of numbers in a statistical distribution. Unlike AVERAGE(), it allows you to apply a criterion that filters which values contribute to the mean.

The three most common measures of central tendency are:

• Average (Mean): The arithmetic mean of the distribution.
• Median: The middle value in a sorted list of numbers.
• Mode: The most frequently occurring value in a dataset.

In a symmetrical distribution, these measures are identical. In a skewed distribution, they may differ.

Example:
Your software company markets its products through email newsletters. To analyze engagement, you track the click rates on your website after each mailing over the past 30 months.

Question: What is the average click rate after « Software B » mailings?

Using AVERAGEIF():

• Range (criteria evaluation): C2:C30 (contains mailing types, e.g., « Software B »).
• Criteria: « Software B » (only cells matching this label are considered).
• Average_range: D2:D30 (contains click rates to average).

Result: The average click rate after « Software B » mailings is 7,157.57 (see Figure below).

This function calculates the average of the values in an argument list. Unlike AVERAGE(), it includes not only numbers but also text and logical values (TRUE and FALSE) in the calculation.

Syntax:
AVERAGEA(value1; [value2] …)

Arguments:

• value1(required) and value2 (optional): At least one and up to 255 arguments  for which you want to calculate the average.

Background:
For more details on averages, refer to the definition of AVERAGE().

The following rules apply to AVERAGEA():

• If an argument contains text (specified as an array or reference), it is evaluated as 0.
• Arguments containing TRUEevaluate as 1, and those containing FALSE evaluate as 0.

Note: If you do not want to include text values in the calculation, use the AVERAGE() function instead.

Example:
You work in the controlling department of a software company and create an Excel table containing sales data for the past twelve months.

Since the list includes text and logical values, you calculate the average sales using AVERAGEA().

• In the first column, you enter the text « Closed » for February (see Figure below). Because AVERAGEA()automatically converts text to 0, all 12 values are summed, and the total is divided by 12. The result is $916.67.
• The second column contains TRUEinstead of « Closed. » This logical value is evaluated as 1. Again, all 12 values are summed, and the total is divided by 12. The result is $916.75.

If you had used AVERAGE() instead, text values would be excluded—only 11 values would be summed, and the total divided by 11. In this case, the result would be $1,000.00.

This function returns the average (arithmetic mean) of the arguments. To calculate the average, interval-scaled variables are summed and then divided by their count.

Syntax

AVERAGE(number1, number2, …)

Arguments

• number1 (required) and number2 (optional): At least one and up to 255 arguments (30 in Excel 2003 and earlier versions) for which you want to calculate the average.

Background

The arithmetic mean is the most well-known measure of central tendency and is widely used, even among non-statisticians. Because it incorporates all values in its calculation, it plays a key role in inferential statistics.

To compute the mean:

1. Sum all values in a range.
2. Divide the total by the number of values.

The arithmetic mean requires interval-scale data.

The combined mean of two datasets can also be derived from their individual arithmetic means.

Limitations

• Sensitivity to outliers: Extreme values significantly affect the mean since all data points are included.
• Potential misrepresentation: The mean may not align with actual observed values, especially in skewed distributions.
• Grouped data: For grouped or continuous variables, the arithmetic mean is only an estimate unless additional information about central tendency is available.

Applicability to Ordinal Data

Although the arithmetic mean typically requires metric-scale data, it can sometimes be applied to ordinal-scale data (e.g., customer satisfaction surveys) under certain conditions:

• The sample size is sufficiently large (n > 30).
• Data distribution approximates normality.
• A confidence interval is provided for the mean.

Comparison with Other Measures of Central Tendency

For datasets allowing arithmetic mean calculation, the mode and median can also be determined. The best measure depends on the context:

• Mean: Uses all data points but is sensitive to outliers.
• Median: Robust against outliers, ideal for skewed distributions.
• Mode: Best for categorical or highly clustered data.

Example

As a marketing manager for a software company, you need to calculate the average webpage visits in 2024 to identify high- and low-traffic areas.

Steps:

1. Import website visit data into Excel.
2. Use a PivotTable to organize visits by section (Products, Publications, Team, Training, Knowledge).
3. Apply the AVERAGE() function to determine the mean visits per area.

Findings (see Figure below):

• The Products section has significantly more visits than Publications.
• The yearly average across all sections provides an overview of overall website activity.
• Comparing results with prior years enables trend analysis.

This function returns the average of the absolute deviations of data points from their mean. The function calculates the arithmetic mean of the deviations of a data set based on the average, excluding the sign.

Syntax:
AVEDEV(number1, number2, …)

AVEDEV() is a measure of the variance in a data set.

Arguments:

• number1(required) and number2 (optional): At least one and up to 255 arguments for which you want to calculate the absolute deviation. You can also use a single array or a reference to an array instead of arguments separated by commas.

Background:
To calculate the deviation of sales or, as in our example, the monthly website visits relative to the mean, use the AVEDEV() function.

AVEDEV() is a measure of the variance in a data set.

In a sense, measures of dispersion serve as a quality criterion for the measure of central tendency. These measures indicate the accuracy of a measure of central tendency. Variance parameters refer to the difference between the following:

• Location values (range, quartile, or semi-quartile distance)
• Individual values and a mean (average linear deviation, variance, standard deviation)

Example:
The marketing department of a software company wants to analyse customer website visits. The visits to various website areas over the past 18 months are recorded in an Excel table (see table below).

Since the average deviation refers to the mean values in the data sets, the marketing department calculates the mean value for each website area using the AVERAGE() function. Afterwards, they calculate the average deviation for each data set. The AVEDEV() function returns the results—the arithmetic mean of the deviation from the mean value.

Now, the mean values and average deviations can be compared and analyzed. The following conclusions can be drawn from this result:

The AVEDEV() function is a measure of the variance in a data set, where the variance parameters refer to the differences between individual values and mean values.

For example, the average deviation for the DOWNLOAD area is 378.3 per month. This means that, compared to the calculated mean value, the visits to the DOWNLOAD area vary by 378.3 each month.