A lot of user experience issues and problems in Excel are related to formula issues. One of these issues is having the #N/A values in your range, which can be quite disturbing. These values mean that there is no value available.
We will show a couple of formulas that can help you to sum or to find the average of your range even if you have certain values that cannot be found.
Ignore #N/A Values with Excel Formulas
The first thing that we need to do is create a range with some values that cannot be found:
If we would go on and input the following formula:
|
1 |
=SUM(A2:A11) |
To sum all the values, we would end up with the results shown in the picture below:
This is pretty much expected. We need to tweak this formula a little bit, and there are a couple of options. We will type them in a range C2:C4:
|
1 2 3 4 |
=SUMIF(A1:A11,"<>#N/A") =SUM(IFNA(A2:A11, "")) =SUM(IFERROR(A2:A11,"")) |
All of these formulas listed above will get us the same result:
Which is exactly the result that we want and would have expected.
If we want to find out the average of our range, we would resort to the similar formulas:
|
1 2 3 |
=AVERAGE(IFERROR(A2:A11,"")) =AVERAGE(IFNA(A2:A11, "")) |
We will input these formulas in cells D2 and D3, and get the following results:
Personally, the best option to use is the IFNA formula, which takes into consideration that we will have #N/A values in our ranges.