In case you wish to round only the displayed average without changing the underlying value, make use of the Decrease Decimal command on the ribbon or the Format Cells dialog box as explained in How to round average in Excel. The result of the comparison is an array of TRUE and FALSE values, where TRUEs represent matches: The logical test of the IF function compares each subject in C3:C11 against the target one in F2. Or you can input the condition in some cell and reference that cell (F2 in our case): In Excel 3, a normal formula will work nicely.Īs an example, let's find an average Math score in the table below. In Excel 2019 and lower, this only works as an array formula, meaning you need to press the Ctrl + Shift + Enter keys to complete it correctly. To get an average of top 3, 5, 10 or n values in a range, use AVERAGE in combination with the LARGE function:ĪVERAGE(IF( criteria_range= criteria, average_range)) =AVERAGEIF(B3:D3, "0") Average top or bottom N values To exclude zeros, use the AVERAGEIF or AVERAGEIFS function instead. However, the zero value in C7 is included in the average in E7, since it is a valid numeric value. In the image below, notice that the average in cells E4, E5 and E6 is the same as in E3 as a blank cell and invalid values in column C are ignored and only the numbers in columns B and D are processed. The Excel AVERAGE function skips blank cells, text and logical values, but not zeros. =AVERAGE(B3:B13) Excel average without zeros For the time average to display correctly, just remember to apply an appropriate time format to the formula cell.įor instance, to find an average time in the below dataset, the formula is: =AVERAGE(C2:C11) Get average time in ExcelĬalculating different time units manually, would be a real pain… Luckily, the Excel AVERAGE function copes with times perfectly. The key thing is to set the Percent format for the formula cell.įor example, to calculate an average percentage in cells C2 through C11, the formula is: To get an average of percentages, you use a normal Excel formula for average. How to use AVERAGE function in Excel - examplesĪpart from numbers, Excel AVERAGE can easily find an arithmetic mean of other numeric values such as percentages and times, as demonstrated in the following examples. In the below dataset, we use 3 different formulas for calculating an average - in the entire range, in each row and in each column: For example:Īnd here is a real-life scenario. To average multiple ranges, use several range references in a single formula:Īnd naturally, nothing prevents you from including values, cell and range references in the same formula as your business logic requires. To return a mean of non-adjacent cells, each cell reference should be supplied individually: To average a range of cells, specify that range in your formula: To average a row, use a whole-row reference: To average a column in Excel, supply a whole-column reference: For example, to average the numbers 1,2,3, and 4, the formula is: To calculate an average of certain numbers, you can type them directly in a formula. This can be done in a few different ways. To build a basic Excel formula for average, all you need to do is to supply the source values. You can find this option under Excel Options > Advanced > Display options for this worksheet. This might be especially confusing if the " Show a zero in cells that have a zero value" option is unchecked in a given worksheet. When using the AVERAGE function in your Excel sheets, please do keep in mind the difference between cells with zero values and blank cells - 0's are counted, but empty cells are not. To avoid this, please see how to average ignoring errors. Arguments that are error values cause an AVERAGE formula to return an error.If the specified arguments do not contain a single valid numeric value, a #DIV/0! error occurs.For example, the formula AVERAGE(TRUE, FALSE) returns 0.5, which is the average of 1 and 0. Boolean values typed directly in a formula are counted.If you want to include Boolean values and text representations of numbers in the calculation, use the AVERAGEA function. Cells containing text strings and logical values TRUE and FALSE are ignored.
Cells with zero values are included in the average.However, there are a few nuances that you should be aware of. AVERAGE function - 6 things to know aboutįor the most part, using the AVERAGE function in Excel is easy. In one formula, you can include up to 255 arguments.ĪVERAGE is available in all versions of Excel 365 though Excel 2007. The first argument is required, subsequent ones are optional. They can be supplied in the form of numeric values, arrays, cell or range references. are numeric values for which you want to get the average.
0 kommentar(er)
