Average Of Numbers In Excel How To Use The

In essence, you use it similarly to AVERAGEIF, except that you can supply more than one criteriarange and criteria in a formula.

Average Of Numbers In Excel How To Use The

You will also learn how to use the AVERAGEIF and AVERAGEIFS functions to average cells that meet certain criteria.

For example, if a few athletes have run a 100m sprint, you may want to know the average result - i.e.

Further on in this tutorial, we will discuss the syntax of each function and illustrate it with examples of uses.

The first argument is required, subsequent ones are optional, and up to 255 arguments can be included in a single formula.

The parameters can be supplied as numbers, cell references, or ranges.

For example, the following average formula calculates the average of 2 ranges and 1 individual cell.

If youd rather exclude zeros, use AVERAGEIF instead, as demonstrated in the following example.

But what if you want to average times that include hours, minutes and seconds Calculating different time units manually, would be a real pain but the AVERAGE formula in Excel copes perfectly.

However, it does have a few specificities that you need to be aware of.

If you want to include Boolean values and text representations of numbers in the calculation, use the AVERAGEA function.

For example, the formula AVERAGE(TRUE, FALSE) returns 0.5, which is the average of 1 and 0.

This might be especially confusing if the Show a zero in cells that have a zero value option is unchecked in a given sheet.

You can find this option under Excel Options Advanced Display options for this worksheet.

The difference is that AVERAGEA includes all non-empty cells in a calculation, whether they contains numbers, text, Boolean values of TRUE and FALSE, and empty strings returned by other formulas.

The first argument is required, others (up to 255) are optional.

The formula AVERAGEA(2,TRUE) returns 1.5, which is the average of 2 and 1.

The criteria can be supplied in the form of a number, logical expression, text value, or cell reference, e.g.

A2.

If omitted, the formula will calculate an average of the values in the range argument.

In this example, lets average only the sales (B2:B8) for the Banana orders (A2:A8).

Please remember, in this case the actual value stored in a cell wont be changed, and the exact non-rounded value will be used in all calculations if you refer to that cell in other formulas.

For this, you would need the not equal to operator in the criteria argument of your AVERAGEIF formula.

It allows for multiple conditions and returns the average (arithmetic mean) of cells that meet all of the specified criteria.

The criteria can be supplied in the form of a number, logical expression, text value, or cell reference.

Average Of Numbers In Excel For Mac