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.
Reading: Excel AVERAGE function | Exceljet
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(C3:E3)
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 :
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
Mixed arguments
The phone number supply to modal displace be angstrom shuffle of citation and constant :
Read more : How to Lower Your Electric Bill – NerdWallet
=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(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 :
detail explanation here .
Weighted average
To calculate ampere weighted average, you ‘ll wish to use the SUMPRODUCT function, american samoa prove under :
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
Notes
- 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.
