#N/A in calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm trying to work around some #N/A values in calculations. Columns A and B
are populated with a conditional formula, and I'm trying to sum them in
column C; so data could look like:

A1 = 3, B1 = 4 C1= sum(A1:B1)
A2 = 2, B2 = #N/A C2= sum(A2:B2)

I would like to have C2 = 2, disregarding the #N/A, but can't seem to do it.
I need to keep the #N/A in the cells in order to have zero values ignored in
some graphs built off this data.

Any hints?

TIA
JonR
 
Replace the #N/A values with an empty string, "", in your conditional
formulas and then column C will calculate.

Dave
 
Replace the #N/A values with an empty string, "", in your conditional
formulas and then column C will calculate.

Dave
 
in C1: ctrl/shift/enter:
=SUM(IF(ISNA(A1:B1),0,A1:B1))
then fill to C2
Bob Umlas
Excel MVP
 
in C1: ctrl/shift/enter:
=SUM(IF(ISNA(A1:B1),0,A1:B1))
then fill to C2
Bob Umlas
Excel MVP
 
Brilliant

So far so good, but I have one other case that I need to deal with:

A3= #N/A B3 = #N/A

In this case I would like C3 to also be #N/A, instead of the zero that your
formula returns. I tried putting NA() into the function where the zero is,
but then if there is an #N/A in either of the two values, then #N/A is the
result, rather than the numeric value of the one cell that is populated with
a number.
 
Brilliant

So far so good, but I have one other case that I need to deal with:

A3= #N/A B3 = #N/A

In this case I would like C3 to also be #N/A, instead of the zero that your
formula returns. I tried putting NA() into the function where the zero is,
but then if there is an #N/A in either of the two values, then #N/A is the
result, rather than the numeric value of the one cell that is populated with
a number.
 
The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))
 
The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))
 
That worked!

Thanks everyone!
--


JonR


Kevin Vaughn said:
The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))
Click to expand...
 
That worked!

Thanks everyone!
--


JonR


Kevin Vaughn said:
The following formula SEEMED to work in my limited testing (and limited
understanding.) Note, the original formula seemed to need to be array
entered and as you stated it worked for you, I will assume that you already
knew that.

=IF(AND(ISNA(A1),ISNA(B1)),NA(),SUM(IF(ISNA(A1:B1),0,A1:B1)))
Click to expand...
 
Back
Top