This function determines the average of the values included in the argument
|
Type an = sign, AVERAGEIF, opening parenthesis (in this example, we are going to evaluate and average the same range of cells, C2 through C5, in the Sales column), comma, then we type the criteria against which the range is evaluated, enclosed in quotes (you put it in quotes so Excel interprets the operator and value correctly), in this example, greater than 60000. Show
Don’t type a comma in 60000. Excel would interpret that as a separator for arguments of the function and return an error. And press Enter. Excel automatically adds the closing parenthesis to the formula. The average for sales greater than $60000 is $71229. Let’s walk through this. The function evaluates how many cells in the Sales column meet the criteria of greater than 60000. There are three cells. The function then averages these cells. Now we’ll use the AVERAGEIF function to determine the average for sales where the number of orders is greater than 50. In this example, one range of cells is evaluated against the criteria and a second range of cells is averaged. We type = AVERAGEIF, opening parenthesis, the range of cells we want evaluated (cells B2 through B5) in the number of orders column, comma, the criteria by which the range is to be evaluated enclosed in quotes (greater than 50), comma, the range of cells we want to average (cells C2 through C5 in the Sales column), and press Enter. The average for sales where the number of orders is greater than 50 is $66487. Let’s walk through this one, too. First, the function evaluates which cells in B2 through B5 meet the criteria of greater than 50. Three cells meet the criteria. The function then averages the cells from the Sales column where the criteria is met by the corresponding cell in column B. This article describes the formula syntax and usage of the AVERAGEIF function in Microsoft Excel. Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. AVERAGEIF(range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments:
Remarks
If range is And average_range is Then the actual cells evaluated are A1:A5 B1:B5 B1:B5 A1:A5 B1:B3 B1:B5 A1:B4 C1:D4 C1:D4 A1:B4 C1:C2 C1:D4 Note: The AVERAGEIF function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:
For a symmetrical distribution of a group of numbers, these three measures of central tendency are all the same. For a skewed distribution of a group of numbers, they can be different. ExamplesCopy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data. Property Value Commission 100000 7000 200000 14000 300000 21000 400000 28000 Formula Description Result =AVERAGEIF(B2:B5,"<23000") Average of all commissions less than 23000. Three of the four commissions meet this condition, and their total is 42000. 14000 =AVERAGEIF(A2:A5,"<250000") Average of all property values less than 250000. Two of the four property values meet this condition, and their total is 300000. 150000 =AVERAGEIF(A2:A5,"<95000") Average of all property values less than 95000. Because there are 0 property values that meet this condition, the AVERAGEIF function returns the #DIV/0! error because it tries to divide by 0. #DIV/0! =AVERAGEIF(A2:A5,">250000",B2:B5) Average of all commissions with a property value greater than 250000. Two commissions meet this condition, and their total is 49000. What function determines the highest cell value included in the argument?MAX: This function determines the highest cell value included in the argument. MIN: This function determines the lowest cell value included in the argument. The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX.
What function counts the number of cells with numerical data in the argument?The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers.
Is an expression that operates on values in a range of cells or a cell?In Microsoft Excel, a formula is an expression that operates on values in a range of cells.
What syntax used in the Averageif function that includes the value or label that determine if the cell is part of the range to be average?=AVERAGEIF(range, criteria, [average_range])
The AVERAGEIF function uses the following arguments: Range (required argument) – This is the range of one or more cells that we want to average. The argument may include numbers or names, arrays, or references that contain numbers.
|
