How to Calculate Average If Criteria Not Blank/Ignore Blank Cell

In daily work we often need to calculate the average of some numbers based on given conditions or criteria. To calculate average with criteria, we can apply AVERAGEIF of AVERAGEIFS function. AVERAGEIFS function can handle more than one group of criteria range and criteria. In this article, we will show you how to calculate average of numbers whose corresponding cells in another group are not blank. Thus, we need to know some knowledge of AVERAGEIFS function.

In this article, we will let you know the syntax, arguments, and basic usage about AVERAGEIFS function, and apply it to build a formula, also let you know the calculation steps of the formula.

1. EXAMPLE

How to Calculate Average If Criteria Not Blank 1

Refer to “Score” column, some numbers are listed in range B2:B10. “Level” column is used for providing a level based on scores. In E2 and F2, we calculate the average of all scores and qualified scores separately. To calculate average of all scores, we can directly use AVERAGE function. To calculate average of qualified scores (cell is not blank in Level group), we need to add criteria to filter them, we can apply AVERAGEIFS function, it is also one of the most common used functions in Excel.

First, In C2, enter “=AVERAGE(Score)”. Then press Enter, AVERAGE function returns 73.33333333.

How to Calculate Average If Criteria Not Blank 2

You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.

How to Calculate Average If Criteria Not Blank 11

Keep clicking on “Decrease Decimal” button till decimal places is ok for you. Keep two decimal places in this case.

How to Calculate Average If Criteria Not Blank 3

Now we can apply AVERGAEIFS function to calculate the average of qualified scores.

Before creating the formula, name range “B2:B10” to “Score”, “C2:C10” to “Level” in Name Box.

How to Calculate Average If Criteria Not Blank 4

2. CREATE A FORMULA with AVERAGEIF FUNCTION

Step 1: In F2, enter the formula:

 =AVERAGEIFS(Score,Level,"<>").
How to Calculate Average If Criteria Not Blank 5

Step 2: Press Enter after typing the formula.

How to Calculate Average If Criteria Not Blank 6

Only B2, B4, B5, B6, B8, B9 and B10 meet our given criteria “level is A or B or C”, so we calculate the average of numbers in above cells, total 7 numbers. (60+62+90+83+88+74+90)/7=78.14285714, keep two decimal places 78.14. The formula works correctly.

a. FUNCTION INTRODUCTION

AVERAGEIFS function is AVERAGE+IFS. It returns the average of some numbers in a range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],…)

It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical expressions like ‘>=’,’<=’. If wildcards or logical operators are required to build criteria, they should be enclosed into double quotes (““). In this case we entered “<>” to represent criteria.

b. EXPLANATION

After expanding values, the formula is displayed as:

=AVERAGEIFS({60;55;62;90;83;58;88;74;90},{"C";0;"C";"A";"B";0;"A";"C";"A"},"<>")

Note: 0 is recorded in this array to represent blank cells.

In the formula, the criteria is “<>” (“not equals to” operator), based on this criteria, cells without any level in “Level” column cannot meet our condition, so for the corresponding values in the average range, they are excluded in calculation.

{60;55;62;90;83;58;88;74;90} -> {60;0;62;90;83;0;88;74;90} – Ignore blank cells

Now this new array only contains numbers. We can calculate the average now (60+62+90+83+88+74+90)/7=78.14.

3. Related Functions

  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

How to Calculate Average Ignore Non-Numeric Values and Errors

In daily work we often need to calculate the average of some numbers in a range for further analysis. Thus, we need to know some basic functions of calculate average in Excel. From now on, we will introduce you some basic functions like AVERAGE, AVERAGEIF, and show you some common formulas to calculate average value.

In this article, we will let you know how to calculate average by AVERAGE function easily and the way to calculate average based on a given criteria by AVERAGEIF function. We will introduce you the syntax, arguments, and basic usage about the two functions, and let you know the working process of our created formulas.

1. EXAMPLE

How to Calculate Average Ignore Non-Numeric Values and Errors 1

Refer to “Numbers” column, some numbers are listed in range A2:A11. C2 is used for entering a formula which can calculate the average of numbers in range A2:A11. In fact, Excel has amount of built-in functions and they can execute most simple calculations properly, and AVERAGE is one of the most common used functions.

In C2, enter “=AVERAGE(A2:A11)”.

How to Calculate Average Ignore Non-Numeric Values and Errors 2

Then press Enter, AVERAGE function returns 63.8.

How to Calculate Average Ignore Non-Numeric Values and Errors 4

You can adjust decimal places by “Increase Decimal” or “Decrease Decimal” in “Number” section.

How to Calculate Average Ignore Non-Numeric Values and Errors 4

But if some errors or blank or non-numeric values exist in the list, how can we only calculate average for numbers ignoring the invalid values?  See example below.

How to Calculate Average Ignore Non-Numeric Values and Errors 5

We get an error when calculating average for range A2:A11, the difference is there are some invalid values in the list. To calculate average ignoring the errors, that means we need to calculate average of numbers in a range with one or more criteria, thus, we can apply AVERGAEIF function instead of basic AVERAGE function.

2. CREATE A FORMULA with AVERAGEIF FUNCTION

Step1: In C2, enter the formula =AVERAGEIF(A2:A11,”>0″).

How to Calculate Average Ignore Non-Numeric Values and Errors 6

You can also name range “Numbers” for A2:A11, then you can enter =AVERAGEIF(Numbers,”>0″).

Step2: Press Enter after typing the formula.

How to Calculate Average Ignore Non-Numeric Values and Errors 7

Ignore the improper cells, and only calculate the average of numbers 54, 40, 88, 76, 100, 90 and 44, total 7 numbers. (54+40+88+76+100+90+44)/7=70.3. The formula works correctly.

a. FUNCTION INTRODUCTION

AVERAGEIF function is AVERAGE+IF. It returns the average of some numbers in a range based on given criteria.

Syntax:

=AVERAGEIF(range, criteria, [average_range])

It supports wildcards like asterisk ‘*’ and question mark ‘?’, also supports logical operators like ‘>’,’<’. If wildcards or logical operators are required, they should be enclosed into double quotes (““), in this case we entered “>0” to show the criteria.

AVERAGEIF – RANGE

This example is very simple, we have only one list. A2:A11 is criteria range and average range.

In the formula bar, select “A2:A11”, press F9, values in this range are expanded in an array.

How to Calculate Average Ignore Non-Numeric Values and Errors 8

If average range=criteria range, average range can be omitted.

AVERAGEIF – CRITERIA

Obviously, the criteria in our case is “>0”, this condition can filter numbers from criteria range.

b. HOW THE FORMULA WORKS

After expanding values, the formula is displayed as:

=AVERAGEIF({54;40;#N/A;88;"ABC";76;0;100;90;44},">0") 

Because of criteria “>0”, so invalid cells are ignored. Replace all invalid values with 0. Keep all numbers.

{54;40;#N/A;88;"ABC";76;0;100;90;44} -> {54;40;0;88;0;76;0;100;90;44}

Now this new array only contains numbers. We can calculate the average now.

3. Calculate Average Ignore Non-Numeric Values and Errors with VBA Code

You can also create a User Defined Function to calculate the average of a range of cells, ignoring any non-numeric values or error values, just do the following steps:

Step1: Click on the “Visual Basic” button in the Developer tab to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor, click on “Insert” in the menu and select “Module” to create a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Paste the VBA code provided in the new module.

VBA Calculate Average Ignore Non-Numeric Values and Errors1.png
Function AverageIgnoreNonNumeric(rng As Range) As Variant
    Dim cell As Range
    Dim sum As Double
    Dim count As Long
    
    sum = 0
    count = 0
    
    For Each cell In rng
        If IsNumeric(cell.Value) And Not IsError(cell.Value) And Not IsEmpty(cell.Value) Then
            sum = sum + cell.Value
            count = count + 1
        End If
    Next cell
    
    If count > 0 Then
        AverageIgnoreNonNumeric = sum / count
    Else
        AverageIgnoreNonNumeric = CVErr(xlErrNA)
    End If
End Function

Step4: Save the workbook as a macro-enabled workbook by selecting “Excel Macro-Enabled Workbook” from the “Save as type” drop-down menu.

Step5: Close the Visual Basic Editor and return to the Excel worksheet.

Step6: Enter the below formula in a blank cell where you want to calculate the average

=AverageIgnoreNonNumeric(A2:A11)

Step7: Press “Enter” to calculate the average ignoring non-numeric values.

VBA Calculate Average Ignore Non-Numeric Values and Errors2.png

4. Related Functions

  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….

How to Calculate Average Ignore Blank and Zero Cells in Excel

Sometimes we need to calculate the average for a list or a range of numbers, but blank cells and zero cells may be also included in the selected range, so if these invalid cells are included, we often get error when calculating the average.

This post will guide you on how to calculate average while ignoring blank and zero cells in Excel. By default, Excel’s AVERAGE function includes all cells in the specified range, including blank cells and cells that contain zero values. However, you can use formulas such as AVERAGEIF or AVERAGEIFS to exclude these cells from the calculation.

1. Video: Calculate Average Ignore Blank and Zero Cells in Excel

In this video, you’ll learn how to use the AVERAGEIF formula to calculate the average while ignoring blank or zero cells in Excel.

2. Average Cells in Excel

If you only want to average a range of cell in Excel, and you can use the AVERAGE function. Just type the following formula:

=AVERAGE(A1:C5)
How to Calculate Average Ignore Blank and Zero Cells in Excel 11.png

This formula will calculate the average of all the cells in the given range, including  blank cells and cells that contain zero values.

3. Calculate Average Ignore Blank and Zero Cells by Formula

Precondition:

Prepare below table. There are some zero cells and blank cells. We will ignore them when calculating the average.

How to Calculate Average Ignore Blank and Zero Cells in Excel1.png

This is the most convenient way to calculate the average for selected range without zero and blank cells. You can use the AVERAGEIF function with a criteria that excludes blank and zero cells. Just do the following steps:

Step 1: Select a blank cell where you want to display the last average result, for example E2.

Step2: Enter the following formula in the formula bar.

 =AVERAGEIF(A1:C5,"<>0").
How to Calculate Average Ignore Blank and Zero Cells in Excel2.png

Step 3: Press Enter to get the average. Verify that average is calculated properly.

How to Calculate Average Ignore Blank and Zero Cells in Excel3.png

This formula will exclude blank cells and cells that contain a zero value from the calculation.

4. Calculate Average Ignore Blank Only

Excel can calculate the average ignore the blank cells automatically. Zero numbers are still included. You can use the AVERAGEIF function with the criteria “<>” or AVERAGE function only to exclude blank cells.

Type one of the below formula in a blank cell where you want to display the average result:

=AVERAGE(A1:C5)

Or

=AVERAGEIF(A1:C5,"<>")
How to Calculate Average Ignore Blank and Zero Cells in Excel 10.png

You may think if we remove all zero values and left them as blank cells, then we can use AVERAGE function directly without other index or parameter. Well, if you remove them totally, you can apply AVERAGE function directly, but, if you just make these zero numbers invisible in the table, you may get improper value by formula.

See example below:

Step1: Click File (it is adjacent to Home) in the ribbon, then select Options to enter Excel Options window.

How to Calculate Average Ignore Blank and Zero Cells in Excel5.png

Step2: On Excel Options window, in the left panel, click Advanced, then drag the scrollbar to the middle, under Display options for this worksheet, uncheck ‘Show a zero in cells that have zero value’ option. Then click OK.

How to Calculate Average Ignore Blank and Zero Cells in Excel6.png

Step3: Then you will find that zero value is ‘removed’ from your table. Only blank cells exist now.

How to Calculate Average Ignore Blank and Zero Cells in Excel7.png

Step4: In E2 enter the formula =AVERAGE(A1:C5). Then press Enter to check the result.

How to Calculate Average Ignore Blank and Zero Cells in Excel8.png

See, we still get the average with zero included. So, you cannot apply AVERAGE function without zero cells by disable showing them in the table.

5. Ignore or Hide Blank Cells in Excel Pivot Table

You can also filter out the blank cells or rows in a pivot table by using the filter options.

ignore blank cells in pivot table1.png

You just need to click on the drop-down arrow next to the column or row header that you want to filter, and then uncheck the “(blank)” option in the filter menu.

ignore blank cells in pivot table2.png

This will hide all the cells or rows that have blank values in that field.

ignore blank cells in pivot table3.png

If you want to filter out multiple columns or rows, you can use the “Filter” or ” Insert Slicer” buttons on the ribbon to apply more advanced filters to your pivot table.

6. Conclusion

In your daily work, excluding blank and zero cells when calculating averages is an very useful task that can save you time and provide more accurate results by the above steps in my article.

Just enjoy it.

Calculating Average Of The Numbers

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed on the right article.

In this article, you will get to know the smarter ways to do these cumbersome tasks in a matter of seconds.

So without any delay, let’s dive into the article,

Average Of The Numbers By Including 0

_3 Average numbers1

General Formula

Use the formula below to calculate the average of a set of numbers in Excel:

=AVERAGE(range)

Explanations for Syntax:

  •  AVERAGE: In Excel, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.

Explanation

Use the AVERAGE function to calculate the average of a set of numbers.

In the example, the formula in E3 is:

=AVERAGE(A3:C3)

_3 Average numbers1

which is then copied down in the table.

AVERAGE is a built-in function in MS Excel in which you supply a range of cells to average, and this function returns the result. In cases where cells are not adjacent, you can also provide individual arguments to the Function.

The AVERAGE function automatically ignores all the blank text and cells, but zero values are included.

Average Of The Numbers By Ignoring 0

_3 Average numbers2

General Formula

The formula below will assist you in calculating the average of a number that excludes zero:

=AVERAGEIF(range,"<>0")

Explanations for Syntax:

AVERAGEIF: This Function returns the average of a set of input values that satisfy a single condition or criteria. Learn more about the AVERAGEIF Function.

Range: This is the input value from your worksheet.

Comma symbol (,): It acts as a separator that helps to separate a list of values.

Parenthesis (): The main Function of this symbol is to group the elements.

Operator (>): The supplied criteria is “>0,” which means “not equal to zero.”

Explanation

The AVERAGEIF function computes the average of a set of numbers while excluding or ignoring zero values. In the example, the formula in E10 is:

=AVERAGEIF(A10:C10,"<>0")

_3 Average numbers2

The formula in E3 in the example is based on the AVERAGE Function:

=AVERAGE(A3:C3)

Because (129+299+0)/ 3 = 166, the answer is 166.

To remove the zero from the calculated average, the formula in E10 employs the AVERAGEIF function, as shown below:

=AVERAGEIF(A10:C10,"<>0") / returns 249

The provided criterion is “>0,” which means “not equal to zero”.

Data with no values

Because the AVERAGE, AVERAGEIF, and AVERAGEIFS functions all ignore blank cells (and cells with text values), there isn’t any need to provide criteria to filter out empty cells.

Average Of The Top 3 Numbers

_3 Average numbers3

General Formula

The formula below will assist you in calculating the average of the top 3 numbers:

=AVERAGE(LARGE(range,{1,2,3}))

Explanations for Syntax:

  • AVERAGE: In Excel, the AVERAGE Function can calculate the arithmetic mean of a set of numbers.
  • Range: This is the input value from your worksheet.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main Function of this symbol is to group the elements.
  • LARGE: This Function returns the Nth largest value from the given range of data. Learn more about the LARGE Function.
  • Values: This is the range of cells that contain values.

Explanation

To average the top three values in a data set, use a formula based on the LARGE and AVERAGE functions. In this example, the formula in G18 is mentioned below:

=AVERAGE(LARGE(A18:E18,{1,2,3}))

_3 Average numbers3

The LARGE function is used to find the top nth value in a set of numbers. For example, the LARGE(A18:E18,{1,2,3})will return the highest value, LARGE(A18:E18,2) will return the second-highest value, and so on:

=LARGE(A18:E18,1)/ First largest value
=LARGE(A18:E18,2)/ 2nd largest value
=LARGE(A18:E18,3)/ 2nd largest value

_3 Average numbers4

In this case, we are requesting multiple values by passing an array constant 1,2,3 into LARGE as the second argument. This causes LARGE to return an array of the top three values.

=LARGE(A18:E18,{1,2,3})

returns an array similar to this:

{299,199,54}

_3 Average numbers5

This array is passed directly to the AVERAGE function:

=AVERAGE(299,199,54) / returns 184

_3 Average numbers6

The AVERAGE Function then returns the average of these values.

Because the AVERAGE Function can handle arrays natively, there is no need to enter this formula with control + shift + enter.

Related Functions

  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel LARGE function
    The Excel LARGE function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array. The syntax of the LARGE function is as below:= LARGE (array,nth)…

      

How to Average Ignore Zero Number

AVERAGE function is a frequently used function in our office work. Except this basic function, Excel also provides some other Average related functions like AVERAGEIF, AVERAGEIFS. In this article, we will show you applying AVERAGEIF function to get average with zero numbers ignored from average range. We will calculate average for the same range by AVERAGE and AVERAGEIF separately, troughing comparing the two results, you can find out the difference between them. In this article, we will introduce you the syntax, arguments, and basic usage of above two functions.

EXAMPLE

How to Average Ignore Zero Number 1

There are some numbers saved in range A1:C4. Zero numbers are included in this range. E2 is used for saving the average of given numbers in this range with zero numbers included. F2 is used for saving the average with zero numbers excluded. Through setting proper criteria for AVERAGEIF function, we can get correct average with zero numbers ignored.

FORMULA with AVERAGE & OFFSET & COUNT FUNCTIONS

In E2, enter the formula =AVERAGE(A1:C4), then press Enter, average of all numbers with zero numbers included is 46.25.

How to Average Ignore Zero Number 2

In F2, enter the formula =AVERAGEIF(A1:C4,”<>0″), then press Enter, average of numbers with zero numbers excluded is 61.67.

How to Average Ignore Zero Number 3

You can see that we apply different functions to get average. The difference is AVERAGEIF function can calculate average with condition.

FUNCTION INTRODUCTION

AVERAGE function returns the average of numbers from a given range reference.

Syntax:

 =AVERAGE(number1, [number2], …)

 AVERAGEIF function returns the average of a set of numbers from a given range based on one condition or criteria. We can split the function as AVERAGE + IF.

Syntax: =AVERAGEIF(range, criteria, [average_range])

 EXPLANATION

=AVERAGEIF(A1:C4,"<>0")

In this case, a set of numbers are saved in range A1:C4, some zero numbers are included in this range. As we want to calculate average with zero number ignored, so criteria range and average range are the same. For AVERAGEIF function, average range is optional, so if it is omitted, criteria range is equal to average range.

“<>0” is the criteria value in this AVERGAEIF function. “<>” is “not equals to” operator. “<>0” is not equal to 0, so set “<>0” as criteria can make zero numbers excluded in calculation.

How to Average Ignore Zero Number 4

Actually, some average related functions like AVERAGE, AVERGAEIF and AVERAGEIFS can ignore blank cells and cells contain texts automatically, so if user want to ignore blank cells or cells contain texts, you can directly apply AVERAGE function to get average ignoring these invalid cells. See example below.

How to Average Ignore Zero Number 5

But AVERAGE function cannot handle errors like #N/A automatically, so we need to apply AVERGAIF or AVERGAIFS function to filter errors by adding proper criteria.

 SUMMARY

  1.  AVERAGE function is used for returning the average of a set of numbers in Excel.
  2. AVERAGEIF function returns the average of a set of numbers refer to one given condition or criteria.
  3. AVERAGE/AVERAGEIF/AVERAGEIFS functions can ignore blank cells and cells contain texts.

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

 

How to Average and Ignore Errors in Excel

This post will guide you how to average a list of values and ignoring any errors in the given range in Excel 2013/2016 or Excel office 365. You can use the AVERAGEIF function or AGGREGATE function to calculate the average of a range of cells which might include some errors in Excel.

Average Cells Ignoring Errors


Assuming you want to calculate the average of the cells that excluding any errors in range B1:B6, and you can use the following formula based on the AVERAGEIF function, like this:

=AVERAGEIF(B1:B6,”>10”)

average and ignore errors1

Let’s See How This Formula Works:

The AVERAGEIF function can be used to calculate an average of some numeric values with one or more criteria. In the above example, the criteria is the expression of “>10”, and it means that you want to average all cell value in range B1:B6 which might include error values.

If you want to calculate the average of the cell values that ignoring any errors that might exist in the given range. And you can also use another array formula based on the AVERAGE function, the IF function and the ISERROR function. Like this:

=AVERAGE(IF(ISERROR(B1:B6),””,B1:B6))

Then you need to put this formula in a cell, and press Ctrl+Shift+Enter keys to make it as array formula. and you should see that the average result would be calculated.

average and ignore errors2

Related Functions


  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel ISERROR function
    The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….

 

 

How to Ignore Error Values When Calculating the Average in Excel

This post will guide you how to ignore error values when getting average for a range of cells in Excel. How do I average a range of cells ignoring error values, such as: #div/0!.

Assuming that you have a list of range of cells that you want to calculate the average of those cells which including error values , such as: #div/0 or #NAME?, etc. When you use the Average function to calculate those cells, you will get an error result. So How to handle this in Excel.

average cells ignore error1

If you want to ignoring any errors that might exist in the given range, you can use AVERAGEIF function or AVERAGE function with IF function to achieve the result. Like this:

=AVERAGEIF(A1:C4,”>=0”)

Or

=AVERAGE(IF(ISERROR(A1:C4),””,A1:C4))

OR

=AVERAGE(IF(ISNUMBER(A1:C4),A1:C4))

 

average cells ignore error2

average cells ignore error3

Note: The second and third formula need to press Ctrl + Shift +  Enter keys, as those formulas are Array Formula. A1:C4 is the data range that you wish to average. And you need to change it as you need.

Let’s see how the first formula works: the AVERAGEIF function can be used to calculate an average of numeric values with one or more criteria for a given range. And In this example, the criteria is the expression “>=0”, it will filter out all error values, and just to calculate the positive numeric values in the given range.

 

Related Functions


  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel AVERAGE function
    The Excel AVERAGE function returns the average of the numbers that you provided.The syntax of the AVERAGE function is as below:=AVERAGE (number1,[number2],…)….
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel IFERROR function
    The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

How to Average a Range and Ignore Zero in Excel

This post will guide you how to average a range of cells and ignoring all zero values in Excel. How do I Average numbers in a given range and ignore zero values with a formula in Excel. How to ignore zero when averaging a range of data in Excel.

Average a Range and Ignore zero


Assuming that you have a list of data in range B1:B4, which contain numbers. And you want to get the average value of those values, but excluding all zero values. How to do it. You can use a formula based on the AVERAGEIF function to ignore zero when taking an average in Excel. Like this:

=AVERAGEIF(B1:B4,"<>0")

Type this formula into a blank cell and press Enter key on your keyboard. Then it would return the average result which has ignored all zero values.

average a range ignore zero1

Let’s see how this formula works:

The AVERAGEIF function will perform an average based on your criteria, the values should not equal to zero value. And it will ignore all blank cells and text or zero values.

You can also use another formula to achieve the same result of calculating an average value for a given range ignoring zero values. Like this:

=SUM(B1:B4)/COUNTIF(B1:B4,">0")

Type this formula into a blank cell and press Enter key on your keyboard.

average a range ignore zero2

Related Functions


  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel AVERAGEIF function
    The Excel AVERAGEAIF function returns the average of all numbers in a range of cells that meet a given criteria.The syntax of the AVERAGEIF function is as below:= AVERAGEIF (range, criteria, [average_range])….
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…

 

Excel Statistical Functions

This section will learn  how to use Excel’s  Statistical Functions such as: Avedev, Average, AverageA, AverageIF, AverageIFS, etc.

AVEDEV – returns the average of the absolute deviations of the numbers that you provided.

AVERAGE – returns the average of the numbers that you provided

AVERAGEA – returns the average of its arguments, including numbers, text, and logical values.

AVERAGEIF – returns the average of all numbers in a range of cells that meet a given criteria.

AVERAGEIFS – returns the average of all numbers in a range of cells that meet multiple criteria.

BETA.DIST – calculate the cumulative beta distribution or beta probability density function.

BETADIST – returns the cumulative beta probability density function.

BETA.INV – returns the inverse of the beta cumulative probability density function.

BETAINV – returns the inverse of the beta cumulative probability density function.

BINOM.DIST – returns the individual term binomial distribution probability.

BINOM.INV – returns the inverse of the Cumulative Binomial Distribution that is greater than or equal to a criterion value.

BINOMDIST – returns the individual term binomial distribution probability.

CHIDIST – returns the right-tailed probability of the chi-squared distribution.

CHIINV – returns the inverse of the right-tailed probability of the chi-squared distribution.

CHITEST – returns the value from the chi-squared distribution for the statistic and the appropriate degrees of freedom.

COUNT – counts the number of cells that contain numbers, and counts numbers within the list of arguments.

COUNTA – counts the number of cells that are not empty in a range.

COUNTBLANK – use to count the number of empty cells in a range of cells.

COUNTIF – count the number of cells in a range that meet a given criteria.

COUNTIFS -returns the count of cells in a range that meet one or more criteria.

COVAR – returns covariance, the average of the products of deviations for each data point in two given sets of values.

FORECAST – used to calculate or predict a future value by using existing values.

FREQUENCY – calculates how often values occur within a range of values.

GROWTH – calculates the predicted exponential growth based on existing data.

INTERCEPT – calculates the point at which a line will intersect the y-axis by using existing x-values and y-values.

LARGE -returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.

LINEST – calculates the statistics for a line by using the “least squares” method to calculate a straight line that best fits your data, and then returns an array that describes the line.

MAX – returns the largest numeric value from the numbers that you provided.

MAXA – returns the largest numeric value from a range of values.

MEDIAN – returns the median of the given numbers.

MIN – returns the smallest numeric value from the numbers that you provided.

MINA -returns the smallest numeric value from the numbers that you provided, while counting text and the logical values.

MODE – returns the most frequently occurring number found in an array or range of numbers.

MODE.MULT – returns a vertical array of the most frequently occurring number found in an array or range of numbers.

MODE.SNGL -returns the most frequently occurring number found in an array or range of numbers.

PERCENTILE -returns the kth percentile from a supplied range of values.

PERCENTRANK – returns the rank of a value in a set of values as a percentage of the set.

PERMUT – returns the number of permutations for a given number of items.

QUARTILE – returns the quartile from a supplied range of values.

RANK – returns the rank of a given number in a supplied range of cells.

SLOPE – returns the slope of the linear regression line through data points in known_y’s and know_x’s.

SMALL -returns the nth smallest numeric value from the numbers that you provided.

STDEV – returns the standard deviation of a population based on a sample of numbers.

STDEVA – returns the standard deviation of a population based on a sample of numbers, text, and logical values.

STDEVP – returns the standard deviation of a population based on an entire population of numbers.

STDEVPA – returns the standard deviation of a population based on an entire population of numbers, text or logical values.

VAR – returns the variance of a population based on a sample of numbers.

VARA – returns the variance of a population based on a sample of numbers, text, or logical values.

VARP – returns the variance of a population based on an entire population of numbers.

VARPA – returns the variance of a population based on an entire population of numbers, text, or logical values.