#N/A in calculations
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 |
#N/A in calculations
Replace the #N/A values with an empty string, "", in your conditional
formulas and then column C will calculate. Dave -- Brevity is the soul of wit. "JonR" wrote: 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 |
#N/A in calculations
in C1: ctrl/shift/enter:
=SUM(IF(ISNA(A1:B1),0,A1:B1)) then fill to C2 Bob Umlas Excel MVP "JonR" wrote in message ... 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 |
#N/A in calculations
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. -- TIA JonR "Bob Umlas" wrote: in C1: ctrl/shift/enter: =SUM(IF(ISNA(A1:B1),0,A1:B1)) then fill to C2 Bob Umlas Excel MVP "JonR" wrote in message ... 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 |
#N/A in calculations
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))) -- Kevin Vaughn "JonR" wrote: 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. -- TIA JonR "Bob Umlas" wrote: in C1: ctrl/shift/enter: =SUM(IF(ISNA(A1:B1),0,A1:B1)) then fill to C2 Bob Umlas Excel MVP "JonR" wrote in message ... 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 |
#N/A in calculations
That worked!
Thanks everyone! -- JonR "Kevin Vaughn" wrote: 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))) -- Kevin Vaughn "JonR" wrote: 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. -- TIA JonR "Bob Umlas" wrote: in C1: ctrl/shift/enter: =SUM(IF(ISNA(A1:B1),0,A1:B1)) then fill to C2 Bob Umlas Excel MVP "JonR" wrote in message ... 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 |
#N/A in calculations
C1, copied down:
=SUM(SUMIF(A1:B1,{"<0","0"})) JonR wrote: 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 |
All times are GMT +1. The time now is 01:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com