excel AVERAGEIFS - Free Excel Tutorial

April 4, 2023April 4, 2023

How to Calculate Average If Criteria Not Blank/Ignore Blank Cell

In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of criteria range and criteria. In this article, we will show you how to calculate average of numbers whose corresponding cells in another group are not blank. Thus, we need to know some knowledge of AVERAGEIFS function.

In this article, we will let you know the syntax, arguments, and basic usage about AVERAGEIFS function, and apply it to build a formula, also let you know the calculation steps of the formula.

1. EXAMPLE

How to Calculate Average If Criteria Not Blank 1

Refer to “Score” column, some numbers are listed in range B2:B10. “Level” column is used for providing a level based on scores. In E2 and F2, we calculate the average of all scores and qualified scores separately. To calculate average of all scores, we can directly use AVERAGE function. To calculate average of qualified scores (cell is not blank in Level group), we need to add criteria to filter them, we can apply AVERAGEIFS function, it is also one of the most common used functions in Excel.

First, In C2, enter “=AVERAGE(Score)”. Then press Enter, AVERAGE function returns 73.33333333.

How to Calculate Average If Criteria Not Blank 2

You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.

How to Calculate Average If Criteria Not Blank 11

Keep clicking on “Decrease Decimal” button till decimal places is ok for you. Keep two decimal places in this case.

Now we can apply AVERGAEIFS function to calculate the average of qualified scores.

Before creating the formula, name range “B2:B10” to “Score”, “C2:C10” to “Level” in Name Box.

How to Calculate Average If Criteria Not Blank 4

2. CREATE A FORMULA with AVERAGEIF FUNCTION

Step 1: In F2, enter the formula:

 =AVERAGEIFS(Score,Level,"<>").

How to Calculate Average If Criteria Not Blank 5

Step 2: Press Enter after typing the formula.

How to Calculate Average If Criteria Not Blank 6

Only B2, B4, B5, B6, B8, B9 and B10 meet our given criteria “level is A or B or C”, so we calculate the average of numbers in above cells, total 7 numbers. (60+62+90+83+88+74+90)/7=78.14285714, keep two decimal places 78.14. The formula works correctly.

a. FUNCTION INTRODUCTION

AVERAGEIFS function is AVERAGE+IFS. It returns the average of some numbers in a range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)

It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical expressions like ‘>=’,’<=’. If wildcards or logical operators are required to build criteria, they should be enclosed into double quotes (““). In this case we entered “<>” to represent criteria.

b. EXPLANATION

After expanding values, the formula is displayed as:

=AVERAGEIFS({60;55;62;90;83;58;88;74;90},{"C";0;"C";"A";"B";0;"A";"C";"A"},"<>")

Note: 0 is recorded in this array to represent blank cells.

In the formula, the criteria is “<>” (“not equals to” operator), based on this criteria, cells without any level in “Level” column cannot meet our condition, so for the corresponding values in the average range, they are excluded in calculation.

{60;55;62;90;83;58;88;74;90} -> {60;0;62;90;83;0;88;74;90} – Ignore blank cells

Now this new array only contains numbers. We can calculate the average now (60+62+90+83+88+74+90)/7=78.14.

3. Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

April 15, 2022May 1, 2022

Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter of seconds? Then congratulations because you have just landed on the right article.

In this article, you will get to know the easiest way to calculate the average of the numbers with respect to multiple criteria in no time.

So read this article till the end, and let’s dive into it;

5 Average cells based on multiple criteria1

General Formula

=AVERAGEIFS(values,range1,criteria1,range2,criteria2)

Explanations for Syntax:

Before learning how to use the above formula, let’s first understand the role of each syntax in quickly calculating the average of the numbers concerning multiple criteria.

AVERAGEIFS: In Excel, this function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
Comma symbol (,): It acts as a separator that helps to separate a list of values.
Parenthesis (): The main function of this symbol is to group the elements.
Range: It represents the input value from your worksheet.
Criteria: The specific criteria or values used to calculate the average.

Explanation

You can use the AVERAGEIFS function to average numbers based on multiple criteria. In the example, the formula in H2 is as follows:

=AVERAGEIFS(sales,product,E2,region,F2)

5 Average cells based on multiple criteria1

Where “sales” (C2:C9), “product” (A2:A9), and “sales” (C2:C9) are named ranges.

The AVERAGEIFS function, like the COUNTIFS and SUMIFS functions, is intended to handle multiple criteria that are entered in [range, criteria] pairs. AVERAGEIFS’s behavior is fully automatic as long as this information is provided correctly.

In the example, we have product values in column E and region values in column F. We use these values directly by using cell references as criteria.

The first argument holds the range of values to average:sales range

To limit the calculation by product, we provide:product, E2

We use the following formula to limit the calculation by region:region, F2

The answer in cell H2 is 396:

Now, let’s go to the 2^nd example in which we want to average the product of “excel” and the sales greater than 300. Please follow these instructions:

Enter the formula given below into a blank cell:

=AVERAGEIFS(sales,product,E2,sales,F2)

(product range is the data that contains the criteria1, sales range is the range which we want to calculate the average, E2 and F2 are the criteria1 and criteria 2), then press the Enter key to get the desired result. Take a look at this screenshot:

Note: If you need more than two criteria, add the criteria ranges and criteria you require as follows:

=AVERAGEIFS(sales,product,E2,region,F2,sales,G2)

Note: the first criteria range is product and E2, the second criteria range is region and F2 and the third criteria is sales and G2 and the range you want to average the values is the criteria, sales range.

For Better understanding, we are adding up the 3^rd example as follows:

Consider the example below to calculate the average of numbers based on multiple criteria.
The image below will show the input values for Columns B, C, and D.
Next, enter the given formula in the formula bar section.
Finally, we’ll see the result in cell H3.

5 Average cells based on multiple criteria1

Summary

This article explains how to use the formulas in Excel to calculate the average of numbers with multiple criteria. I hope you found this article interesting. If you have any ideas, please share them with us.

Related Functions

Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

April 11, 2022

Calculate Average Response Time Per Month In Microsoft Excel

Assume that you have been assigned a task of calculating the average response time per month in MS Excel, then; if you are new to MS Excel, doing this task manually might be your first attempt which would not only make you tired, but you won’t complete your work on time.

But fortunately, there is a more innovative way by which you can do this cumbersome task in a matter of seconds, so kindly read this article till the end and let’s dive into it,

General Formula:

Use the formula below to calculate the average time per month in Excel.

=AVERAGEIFS(response,date_value,">="&E2,date_value,"<="&EOMONTH(E2,0))

Explanations for Syntax:

AVERAGEIFS: This Function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
EOMONTH: In Excel, the EOMONTH Function can be used to return the last day of the month after adding or subtracting a specified number of months from a date.
Response: The response is calculated by subtracting the date opened from the date closed.
Date_value: These are the input dates from your worksheet.
Criteria (E2): It specifies the month used to calculate the average.

Explanation

To average response times by month, use a formula based on the AVERAGEIFS and EOMONTH Functions.

In the example, the formula in F2 is mentioned below:

=AVERAGEIFS(response,date_value,">="&E2,date_value,"<="&EOMONTH(E2,0))

This formula employs the named ranges “ date_value ” (A2:A7) and “ response ” (C2:C7). Column C’s response are in minutes and are calculated by subtracting the date opened from the date closed.

The AVERAGEIFS function is intended to average ranges based on multiple criteria; that’s why we would configure the AVERAGEIFS process to average durations by month based on two criteria:

1^st criteria are in which the matching dates are either greater than or equal to the first day of the month, and the 2^nd criteria are in which the matching dates are either less than or equal to the last day of the month.

In column E, we just type month names, such as: 04,05,07.

This makes it easier to create the AVERAGEIFS function’s criteria we need by using the values in the F column. To match that either the dates greater than or equal to the four of the month, we use:

">="&E2

To match dates that are less than or equal to the last day of the month, we use:

"<="&EOMONTH(E2,0)

By providing zero for the month’s argument, we can get the EOMONTH to return on the last day of the same month.

When the building criteria are based on a cell reference, oncatenation with an ampersand (&) is required.

So we know that using the formula mentioned above, you can obtain the average response times by month. This tutorial will learn how to calculate the average monthly response time in a Microsoft Excel workbook. As we know, the AVERAGEIFS Function is intended to average ranges based on multiple criteria. In the following example, we configure AVERAGEIFS to average durations by month using two criteria: first, we match dates greater than or equal to the first day of the month. Second, by matching dates less than or equal to the last day of the month. So, using this formula, you can obtain the average response time per month in the workbook in Microsoft Excel.

Pivot Table

When you need to summarise or average data by year, month, quarter, and so on, then a pivot table is an excellent solution because pivot tables automatically group dates.

Related Functions

Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

June 10, 2021

Average of Working Hours by Formula in Excel

Suppose you are going to count the average working hours of employees in the company based on different months. If a worksheet including working hours on different dates and months is prepared for you, do you know how can we calculate the average of working hours based on months?

In this article, through explaining the example below, we will introduce you though creating a formula with AVERAGEIFS function and EOMONTH function inside to return average working hours for different months. You can get the basic knowledge and usage of these two functions in this article.

EXAMPLE

We can see that “Start” time and “End” time are recorded clearly for different dates from above table. Total hours are calculated correctly as “Start” minus “End”, the unit is hour. In “Date” column, dates belong to two months “May” and “June”. To calculate average of total hours, we need to calculate average separately for the two months respectively.

For F2 (May-2021) and F3 (Jun-2021), in fact we input date 05/01/2021 into F2 and 06/01/2021 into F3, and set custom format “mmm-yyyy” for the two cells, then they are displayed as “May-2021” and “Jun-2021”. Only in this way they can be processed properly in the following operation.

We want to create only one formula that can be used for returning average working hours based on different months properly, in case of range reference auto adjusted when copying the formula, we use named ranges “Dates” and “Hours” to represent range references (A2:A9) and (D2:D9) in this instance.

In this article, to approach our goal, we will apply AVERAGEIFS function with EOMONTH function inside.

ANALYSIS

a. To calculate average, first we can think about average related functions like AVERAGE, AVERAGEIF, AVERAGEIFS etc.

b. As dates in “Date” belong to two months “May” and “June”, to calculate the average of total hours for one of them, we need to filter dates from “Date” column and make sure they belong to month “May” properly (or “June”); if dates meet our condition, total hours in “Total” will be kept and come to average calculation.

c. To filter date by condition “belong to May”, dates are required to meet below conditions:

1) Date >= 05/01/2021. // date should be greater than or equal to the first date in May.

2) Date <= 05/31/2021. // date should be less than or equal to the last date in May.

As we are going to calculate total hours from “Total” column based on filtered dates, and there are two conditions to filter date from given named range “Date”, so we can select AVERAGEIFS function to calculate average with multiple conditions to solve our problem in this instance.

d. To return the last date of a month, we can apply Excel EOMONTH function. Depends on the given date in F2, it can return the last date of the same month.

Above all, we can create formula =AVERAGEIFS(Hours,Dates,”>=”&F2,Dates,”<=”&EOMONTH(F2,0)) to solve this problem.

FORMULA

Input formula =AVERAGEIFS(Hours,Dates,”>=”&F2,Dates,”<=”&EOMONTH(F2,0)) into G2 and press Enter, verify that 8.50 is returned and shown in G2 properly. As (8+9+8+9)/4=8.5. The return value is correct.

Notice: actually, the returned value may be a number with more than two decimal places (based on your settings), you can click “Decrease Decimal” in “Number” section under Home tab to shorten decimal places and keep only two.

Drag the handle down to copy formula to G4, verify that 9.13 is returned properly.

FUNCTION INTRODUCTION

a. AVERGAEIFS function can be seen as AVERAGE+IFS. It returns the average of a set of numbers in range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

Example.

b. EOMONTH function returns the last date of month based on the given date.

Syntax:

 =EOMONTH(start_date, months)

Comment: Argument months is a serial number; n months represents n months before or after the input start date. For example, if A1 is 05/20/2021, we enter =EOMONTH(A2, 1), it returns 06/30/2021 (set cell in proper date format). If we enter =EOMONTH(A2, -1) (negative value in months), it returns 04/30/2021. If we enter =EOMONTH(A2, 0), it returns 05/31/2021.

Example.

EXPLANATION

=AVERAGEIFS(Hours,Dates,">="&F2,Dates,"<="&EOMONTH(F2,0))

// average range is named range “Hours” (D2:D9)

// criteria range1 and criteria range2 are the same named range “Dates” (A2:A9)

// criteria 1 is “>=”&F2 (F2: date 5/1/2021)

// criteria 2 is “<=”&EOMONTH(F2,0)

a. The first criteria is “>=”&F2, it is combined with logical operator “greater than or equal to” (“>=”) and cell reference F2, they are concentrated by “&”. Value in F2 is “5/1/2021”, but when running the formula, it is converted to number “44317” which represents the date 5/1/2021.

b. The second criteria is “<=”&EOMONTH(F2,0). EOMONTH function returns the last day of one month which is decided by the entered date. For F2 date is 5/1/2021, the second argument months is 0, so EOMONTH() returns the last day of May, it is 05/31/2021. Date 05/31/2021 is displayed as 44347 if change format from date to general format.

c. Then filter dates from “Dates” by the two conditions.

d. After filtering dates by providing conditions, then calculate average of hours refer to the filtered dates.

e. After running above steps, formula returns average value “8.5” properly. In fact, you can select formula in formula bar, and press F9, you can find the formula returns 8.5 directly.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

May 28, 2021

How to Average Ignore Zero Number

AVERAGE function is a frequently used function in our office work. Except this basic function, Excel also provides some other Average related functions like AVERAGEIF, AVERAGEIFS. In this article, we will show you applying AVERAGEIF function to get average with zero numbers ignored from average range. We will calculate average for the same range by AVERAGE and AVERAGEIF separately, troughing comparing the two results, you can find out the difference between them. In this article, we will introduce you the syntax, arguments, and basic usage of above two functions.

EXAMPLE

There are some numbers saved in range A1:C4. Zero numbers are included in this range. E2 is used for saving the average of given numbers in this range with zero numbers included. F2 is used for saving the average with zero numbers excluded. Through setting proper criteria for AVERAGEIF function, we can get correct average with zero numbers ignored.

FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(A1:C4), then press Enter, average of all numbers with zero numbers included is 46.25.

In F2, enter the formula =AVERAGEIF(A1:C4,”<>0″), then press Enter, average of numbers with zero numbers excluded is 61.67.

You can see that we apply different functions to get average. The difference is AVERAGEIF function can calculate average with condition.

FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

 =AVERAGE(number1, [number2], …)

AVERAGEIF function returns the average of a set of numbers from a given range based on one condition or criteria. We can split the function as AVERAGE + IF.

Syntax: =AVERAGEIF(range, criteria, [average_range])

EXPLANATION

=AVERAGEIF(A1:C4,"<>0")

In this case, a set of numbers are saved in range A1:C4, some zero numbers are included in this range. As we want to calculate average with zero number ignored, so criteria range and average range are the same. For AVERAGEIF function, average range is optional, so if it is omitted, criteria range is equal to average range.

“<>0” is the criteria value in this AVERGAEIF function. “<>” is “not equals to” operator. “<>0” is not equal to 0, so set “<>0” as criteria can make zero numbers excluded in calculation.

Actually, some average related functions like AVERAGE, AVERGAEIF and AVERAGEIFS can ignore blank cells and cells contain texts automatically, so if user want to ignore blank cells or cells contain texts, you can directly apply AVERAGE function to get average ignoring these invalid cells. See example below.

But AVERAGE function cannot handle errors like #N/A automatically, so we need to apply AVERGAIF or AVERGAIFS function to filter errors by adding proper criteria.

SUMMARY

AVERAGE function is used for returning the average of a set of numbers in Excel.
AVERAGEIF function returns the average of a set of numbers refer to one given condition or criteria.
AVERAGE/AVERAGEIF/AVERAGEIFS functions can ignore blank cells and cells contain texts.

Related Functions

Excel AVERAGE function
The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
Excel AVERAGEIF function
The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

July 11, 2017July 19, 2017

Excel AverageIFS Function

This post will guide you how to use Excel AVERAGEIFS function with syntax and examples in Microsoft excel.

Description

The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.

The AVERAGEIFS function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.

The AVERAGEIFS function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the AVERAGEIFS function is as below:

= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)

Where the AVERAGEIFS function arguments are:
Average_range – This is a required argument. The range of cells that you want to average.
Criteria_range1 – This is a required argument. The range to apply the associated criteria.
Criteria – This is an optional argument. The criteria used to define which cells are averaged.

Example

The below examples will show you how to use Excel AVERAGEIFS Function to return the average of all numbers in a range of cells based on multiple criteria.

#1 To get the average of all numbers in average range B1:B5 and meet the criteria that equal to “2015” in range A1:A5, and equal to “TRUE”in range C1:C5 , just using the following excel formula: =AVERAGEIFS(B1:B5,A1:A5,2015,C1:C5,”TRUE”)

July 11, 2017April 26, 2018

Excel Statistical Functions

This section will learn how to use Excel’s Statistical Functions such as: Avedev, Average, AverageA, AverageIF, AverageIFS, etc.

AVEDEV – returns the average of the absolute deviations of the numbers that you provided.

AVERAGE – returns the average of the numbers that you provided

AVERAGEA – returns the average of its arguments, including numbers, text, and logical values.

AVERAGEIF – returns the average of all numbers in a range of cells that meet a given criteria.

AVERAGEIFS – returns the average of all numbers in a range of cells that meet multiple criteria.

BETA.DIST – calculate the cumulative beta distribution or beta probability density function.

BETADIST – returns the cumulative beta probability density function.

BETA.INV – returns the inverse of the beta cumulative probability density function.

BETAINV – returns the inverse of the beta cumulative probability density function.

BINOM.DIST – returns the individual term binomial distribution probability.

BINOM.INV – returns the inverse of the Cumulative Binomial Distribution that is greater than or equal to a criterion value.

BINOMDIST – returns the individual term binomial distribution probability.

CHIDIST – returns the right-tailed probability of the chi-squared distribution.

CHIINV – returns the inverse of the right-tailed probability of the chi-squared distribution.

CHITEST – returns the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom.

COUNT – counts the number of cells that contain numbers, and counts numbers within the list of arguments.

COUNTA – counts the number of cells that are not empty in a range.

COUNTBLANK – use to count the number of empty cells in a range of cells.

COUNTIF – count the number of cells in a range that meet a given criteria.

COUNTIFS -returns the count of cells in a range that meet one or more criteria.

COVAR – returns covariance, the average of the products of deviations for each data point in two given sets of values.

FORECAST – used to calculate or predict a future value by using existing values.

FREQUENCY – calculates how often values occur within a range of values.

GROWTH – calculates the predicted exponential growth based on existing data.

INTERCEPT – calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.

LARGE -returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.

LINEST – calculates the statistics for a line by using the “least squares” method to calculate a straight line that best fits your data, and then returns an array that describes the line.

MAX – returns the largest numeric value from the numbers that you provided.

MAXA – returns the largest numeric value from a range of values.

MEDIAN – returns the median of the given numbers.

MIN – returns the smallest numeric value from the numbers that you provided.

MINA -returns the smallest numeric value from the numbers that you provided, while counting text and the logical values.

MODE – returns the most frequently occurring number found in an array or range of numbers.

MODE.MULT – returns a vertical array of the most frequently occurring number found in an array or range of numbers.

MODE.SNGL -returns the most frequently occurring number found in an array or range of numbers.

PERCENTILE -returns the kth percentile from a supplied range of values.

PERCENTRANK – returns the rank of a value in a set of values as a percentage of the set.

PERMUT – returns the number of permutations for a given number of items.

QUARTILE – returns the quartile from a supplied range of values.

RANK – returns the rank of a given number in a supplied range of cells.

SLOPE – returns the slope of the linear regression line through data points in known_y’s and know_x’s.

SMALL -returns the nth smallest numeric value from the numbers that you provided.

STDEV – returns the standard deviation of a population based on a sample of numbers.

STDEVA – returns the standard deviation of a population based on a sample of numbers, text, and logical values.

STDEVP – returns the standard deviation of a population based on an entire population of numbers.

STDEVPA – returns the standard deviation of a population based on an entire population of numbers, text or logical values.

VAR – returns the variance of a population based on a sample of numbers.

VARA – returns the variance of a population based on a sample of numbers, text, or logical values.

VARP – returns the variance of a population based on an entire population of numbers.

VARPA – returns the variance of a population based on an entire population of numbers, text, or logical values.