Ask Your Question
0

How do I ignore cells with #N/A in a function?

asked 2015-04-17 10:18:48 +0200

orschiro gravatar image

I have thousands of rows of which a number contain #N/A. Running functions such as SUM or AVERAGE report an error when selecting a row sample that includes cells with #N/A. It's time consuming to remove these lines individually. Furthermore, I prefer to keep them for transparency reasons.

Is there any way to ignore these cells?

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
1

answered 2015-04-17 13:58:04 +0200

razon_22 gravatar image

Change your sum and average formulas to include error checking using the iserror or isna functions. Something like this will check for errors, then do your calculation:

=IF(ISNA(A5), 0, SUM(A5))

If there is an error, the result will be 0 and your further column functions will work properly.

edit flag offensive delete link more

Comments

Sorry! I don't understand how to add contents from "thousands of rows" this way?

Another suggestion concerning averages: {=AVERAGEIF(ISNUMBER(A7:A10333),TRUE(),A7:A10333)}

Or; {=AVERAGE(IFNA(A7:A10333;""))}

In contrary to the 0 @pierre-yves samyn (correctly) used with SUM the empty string will not spoil the counting of numbers for the average calculation. ( @karolus also used it with SUM.)

Lupp gravatar imageLupp ( 2015-04-18 22:46:37 +0200 )edit

@Lupp, this is an example formula. I know summing one cell will not add many lines. The range is meant to be replaced as needed. I acknowledge that the zero may affect average functions, but it can be modified as needed.

razon_22 gravatar imagerazon_22 ( 2015-04-19 01:25:18 +0200 )edit
2

answered 2015-04-17 17:58:28 +0200

pierre-yves samyn gravatar image

updated 2015-04-17 17:59:35 +0200

OMG :)

Why not =SUM(IFNA(A2:A51;0)) ?

(as Matrixformula)

edit flag offensive delete link more

Comments

Best solution for SUM. Note though that AVERAGE (or any function where the number of elements matters) will count the 0 values as well.

erAck gravatar imageerAck ( 2018-02-09 15:52:46 +0200 )edit

=SUM(IFNA(A2:A51;"")) should do anyway since SUM and AVERAGE and similar functions ignore text.

Lupp gravatar imageLupp ( 2018-02-09 18:14:30 +0200 )edit
1

answered 2015-04-17 16:45:15 +0200

karolus gravatar image

@razon_22 useless use of sum !!

something like

=SUM(IF(ISNA(A4:A12);"";A4:A12))

as Matrixformula ctrl+shift+enter

edit flag offensive delete link more

Comments

As I stated above, it is simply an example formula.

razon_22 gravatar imagerazon_22 ( 2015-04-19 01:29:33 +0200 )edit
-1

answered 2018-02-09 15:15:45 +0200

vlz gravatar image

Another possiblity, that does not use a matrix formula is simply using "<>#N/A" as the if-condition.

=AVERAGEIF(O2:O124;"<>#N/A")
=SUMIF(O2:O124;"<>#N/A")
edit flag offensive delete link more

Comments

That does not work if the document is loaded in another UI language where the error message is different. Also, it is slower because every value has to be converted to its string representation first before comparing.

erAck gravatar imageerAck ( 2018-02-09 15:45:58 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-04-17 10:18:48 +0200

Seen: 29,174 times

Last updated: Feb 09 '18