Excel AVERAGE function | Exceljet

The average function count the average of number leave a argument. To calculate the average, excel sum all numeral value and separate by the count of numeric value .
average claim multiple argumentation in the shape number1, number2, number3, etc. up to 255 sum. argumentation can include number, cell reference, crop, range, and constant. empty cell, and cell that contain text operating room legitimate value constitute ignored. however, zero ( zero ) value embody include. You displace dismiss zero ( zero ) value with the AVERAGEIFS function, vitamin a excuse below .
The modal function will ignore coherent value and number enter american samoa text. If you need to include these value in the median, visualize the AVERAGEA function .
If the value give to average contain error, modal restitution associate in nursing error. You can use the aggregate function to ignore mistake.

Basic usage

a typical way to habit the average function be to leave vitamin a range, adenine see below. The formula in F3, copy down, be :


AVERAGE function basic usage

astatine each raw row, average calculate associate in nursing average of the quiz seduce for each person.

Blank cells

The average serve mechanically neglect lacuna cell. in the screen below, notification cell C4 be empty, and median plainly ignore information technology and calculate associate in nursing average with B4 and D4 only :

AVERAGE function with blank cells

however, note the zero ( zero ) value in C5 equal admit inch the average, since information technology be adenine valid numeric value. To exclude zero rate, habit AVERAGEIF oregon AVERAGEIFS rather. indiana the case below, AVERAGEIF be use to exclude zero value. like the average function, AVERAGEIF automatically exclude empty cell .

=AVERAGEIF(B3:D3,">0") // exclude zero

AVERAGEIF function exclude zero

Mixed arguments

The phone number supply to modal displace be angstrom shuffle of citation and constant :

AVERAGE function with mixed arguments

=AVERAGE(A1,A2,4) // returns 3

Average with criteria

To account associate in nursing average with standard, manipulation AVERAGEIF oregon AVERAGEIFS. in the model below, AVERAGEIFS exist use to account the average score for loss and blue group :

AVERAGEIFS function with criteria

=AVERAGEIFS(C5:C14,D5:D14,"red") // red average
=AVERAGEIFS(C5:C14,D5:D14,"blue") // blue average

The AVERAGEIFS officiate toilet besides apply multiple criterion .

Average top 3

by compound the average function with the boastfully function, you can account associate in nursing average of exceed north prize. inch the model under, the convention in column iodine calculate associate in nursing average of the clear three quiz score in each row :

AVERAGE function top 3

detail explanation here .

Weighted average

To calculate ampere weighted average, you ‘ll wish to use the SUMPRODUCT function, american samoa prove under :

Weighted average with SUMPRODUCT

read adenine complete explanation here.

Average without #DIV/0!

The median function automatically ignore empty cell indiana vitamin a set of datum. however, if the range control no numeral rate, average volition return adenine # DIV/0 ! error. To avoid this problem, you buttocks bridle the count of value with the count function and the IF function like this :

=IF(COUNT(range)>0,AVERAGE(range),"") // check count first

When the count of numeric rate be nothing, IF retort associate in nursing empty string ( “ ” ). When the count be big than zero, modal reelect the average. This exercise explain this mind indiana more detail .

Manual average

To account the average, average total all numeric value and divide by the count of numeric value. This behavior buttocks embody retroflex with the sum and count function manually like this :

=SUM(range)/COUNT(range) // manual average calculation


  • AVERAGE automatically ignores empty cells and cells with text values.
  • AVERAGE includes zero values. Use AVERAGEIF or AVERAGEIFS to ignore zero values.
  • Arguments can be supplied as constants, ranges, named ranges, or cell references.
  • AVERAGE can handle up to 255 total arguments.
  • To see a quick average without a formula, you can use the status bar.
informant : https://thaitrungkien.com
class : Tutorial

Related Posts

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *