#Taking average of percentages mac

Therefore, the cells in the range are used to calculate the average. Note that the third (optional) argument is omitted. The above formula will find the average of the values in the range B4 to H4 that are not equal to zero. The criteria in an AVERAGEIF function may also be in the form of a logical expression, as in the example below: = AVERAGEIF ( B4 : H4, '0' ) If the average_range is omitted, the range is used.ĪVERAGEIF and AVERAGEIFS find the mean of only the values that satisfy certain criteria.įor example, from this list of various fruit prices, we can ask Excel to extract only the cells that say “apples” in column A, and find their average price from column B. This is the range of cells where the values to be averaged are located. Average_range is an optional argument.The criteria are the value or expression that Excel should look for within the range.The range is the location where we can expect to find cells that meet the criteria.The syntax of the AVERAGEIF function is: = AVERAGEIF (range ,criteria, ) With the AVERAGEIF function, Excel looks within the specified range for a stated condition, and then finds the arithmetic mean of the cells that meet that condition. There are ways to find the average of only the numbers that satisfy certain criteria. The averages for Student E and Student J are now similar when using the AVERAGEA function.Ĭheck out the Microsoft Excel Basic & Advanced course Text is treated as zero.Ĭompare the difference in results between using AVERAGE and AVERAGEA in the example below. The logical value FALSE is considered a zero.ĪVERAGEA calculates the mean using all values within a list, including text. )Ī range or cell references may be used instead of explicit values.ĪVERAGEA evaluates text values as zero, while the logical value TRUE is evaluated as 1.

The format is similar: = AVERAGEA (value1. In order to eliminate this discrepancy, the AVERAGEA function may be used to include all values within a distribution, including text. Notice that Student J’s average is quite different from Student E’s average, even though their grade totals were similar. When determining the number of values to divide the total by, zeros are considered valid amounts and will therefore reduce the overall average of the distribution. This means that the average score in cell I6 was computed using the values in the range C8 to H8, and the total was divided by 6 subjects instead of 7. The word “Sick” in cell B6 (below) causes the AVERAGE function to ignore that cell altogether. Text values and empty cells are ignored.When using the AVERAGE function, bear the following in mind. If typing the cell references directly into the cell or formula bar, non-contiguous references are separated by commas.

#Taking average of percentages mac

In order to calculate the average of non-contiguous (non-adjacent) cells, simply hold the Control key (or Command key - Mac users) while making the selections. This can be typed directly into the cell or formula bar, or selected on the worksheet by selecting the first cell in the range, and dragging the mouse to the last cell in the range. To calculate the average of values in cells B2, B3, B4, and B5 enter: = AVERAGE ( B2 : B5 ) The formula below will calculate the average of the numbers 100, 95, and 80. It ignores text and empties, but includes zeros. Only one argument is required, but of course, if you’re using the AVERAGE function, it’s likely you have at least two.ĪVERAGE calculates the statistical mean from a list of numbers. The AVERAGE function can handle up to 255 arguments, each of which may be a value, cell reference, or range. Ranges or cell references may be used instead of explicit values. The AVERAGE function in Excel is straightforward. It is calculated by adding all the numbers in the list, then dividing the total by the number of values within the list. The Excel AVERAGE function is used to generate a number that represents a typical value from a range, distribution, or list of numbers. The most universally accepted average is the arithmetic mean, and Excel uses the AVERAGE function to find it. Bonus: Nesting AVERAGE, LARGE and SMALL functions.

The following methods are covered in this resource: Believe it or not, there are many different kinds of averages, and different ways to go about calculating them.