## AVERAGE ignore NA()

#N/A error is sometimes used to show gaps in charts, however, the AVERAGE function can't process errors, shown in C11 in the picture above.

There is an easy workaround, the AVERAGEIF function allows you to ignore #N/A errors. It was introduced in Excel 2007.

You need to provide a condition, the larger and smaller than signs are the same as "not equal to".

If you have an Excel version earlier than 2007, the ISERROR function works just as fine. The downside is that you need to enter the formula as an array formula.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Download Excel *.xlsxÂ file

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell rangesÂ $A$1:$A$25 in formula below. […]

The AVERAGE function is designed to ignore blank cells but there are instances where it fails. The picture above seems […]

Column C contains both date and time, to calculateÂ the average of only time we need to extract the hours, minutes […]

The array formula in cell D14 calculates an average based on multiple criteria in cell range B14:B15. If value in […]

How to use the AVERAGEIF function

The AVERAGEIF function returns the average of cell values that are valid for a given condition. Formula in cell D3: […]

### One Response to “AVERAGE ignore NA()”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

My is Pablo and I would like to ask you about this situation. I have got a column with several values and some of them are zeros. As they are dB measurements this is the array formula I use to get the average: =10*LOG(AVERAGE(10^(C3:C66/10)))

My problem is that I am trying to get with a formula that does not take in account the zeros.

I have tried the next formula but it seems that does not work for my situation: =10*LOG(AVERAGEif(C3:C66,"0",[10^(C3:C66/10)]))

It would be very apprecited if you could give me a hint to solve this problem.

Thank you in advance,

Pablo.