A problem with "IF" statements

[Peter]

I have discovered a problem with IF statements that I need to create a way around. Here are two examples

1) Let's say I have 3 columns of numbers in A, B and C, and I want to add a column D as follows: in each row in column D, I want to take the sum of A and B data in that row, and divide by column C in that row for those instances where Col C data is non-zero. If C data is zero, I want a blank in column D so as not to mess up the averaging of COLUMN D when done. So I write

if(C1<>0,(A1+B1)/C1,"")

The problem is, if C1 is zero you still get #Div/0! as the result from Excel in cell D1

2) Similarly, I want to take the standard deviation of A, B, and C column data in column D, but in some rows there are instances where A,B and C are blanks. Let's say A2,B2 and C2 are blanks. If I write =isnumber(sum(A2..C2)) in D2, I get FALSE as expected, but if I write in D2 the following: =if(isnumber(sum(A2..C2)),stdev(A2..C2),""), I STILL get #Div/0! even though I am trying to test for this condition and avoid have this#Div/0! come in

It does not seem to matter that the "false" condition in the IF statement has been achieved. Excel seems to get stuck on the fact that I am potentially asking for a divide by zero, even though this is precisely what I am seeking to avoid

Certainly I could add an extra step in extra cells but it gets very cumbersome this way. Is there any direct "one-cell way" of getting around this

Thanks

 

[Ron Rosenfeld]

I have discovered a problem with IF statements that I need to create a way around. Here are two examples:

1) Let's say I have 3 columns of numbers in A, B and C, and I want to add a column D as follows: in each row in column D, I want to take the sum of A and B data in that row, and divide by column C in that row for those instances where Col C data is non-zero. If C data is zero, I want a blank in column D so as not to mess up the averaging of COLUMN D when done. So I write;

if(C1<>0,(A1+B1)/C1,"").

The problem is, if C1 is zero you still get #Div/0! as the result from Excel in cell D1.

Reverse your logic: =IF(C1=0,"",(A1+B1)/C1)

2) Similarly, I want to take the standard deviation of A, B, and C column data in column D, but in some rows there are instances where A,B and C are blanks. Let's say A2,B2 and C2 are blanks. If I write =isnumber(sum(A2..C2)) in D2, I get FALSE as expected,

I cannot reproduce that. But perhaps you can figure out your problem, or post
more information.

The IF statement evaluates from Left to Right, so in your first equation, the
"value if true" will always be evaluated, even if the condition is FALSE, hence
you get your Div/0 error. The "value if false" is only evaluated if the
condition is FALSE.





--ron

 

[Frank Kabel]

Ron Rosenfeld wrote:
[...]

Reverse your logic: =IF(C1=0,"",(A1+B1)/C1)


I cannot reproduce that. But perhaps you can figure out your
problem, or post more information.

The IF statement evaluates from Left to Right, so in your first
equation, the "value if true" will always be evaluated, even if the
condition is FALSE, hence you get your Div/0 error. The "value if
false" is only evaluated if the condition is FALSE.

Hi Ron
interesting. For me the OP's first equation works fine. No problem with
evaluation the TRUE statement?
version: Excel 2003

Frank

 

[Ron Rosenfeld]

Hi Ron
interesting. For me the OP's first equation works fine. No problem with
evaluation the TRUE statement?
version: Excel 2003

Frank

Perhaps that was a change in 2003? I have XL 2002

For me the OP's second statement (=ISNUMBER(SUM(A1:C1))) results in TRUE even
if the cells are blank. But that seems to be in accord with the definition of
SUM. Although SUM may not ignore error values, which is also in the
definition.


--ron

 

[Peter]

Thanks guys. For the first statement, the reversing of the IF statement works and is a great idea. For the second one, it looks like we need to do an "if(and(isnumber(a1),isnumber(b1),isnumber(c1)) as the pre-concdition, rather than the SUM, to get around the SUM accepting blanks issue, and do the IF reversing if needed...I have Excel 2000 version 9.0.2720 by the way...

Thanks again.

 

[Peter]

Actually in the second example with the STDEV, it would not for me at least be if(and(isnumber....but rather if(and(isblank(a1),isblank(a2) ....etc. since the reversing of the IF is still needed, in my version of Excel at least

Interesting stuff--thanks again

Regards
Peter

 

[Ron Rosenfeld]

Thanks guys. For the first statement, the reversing of the IF statement works and is a great idea. For the second one, it looks like we need to do an "if(and(isnumber(a1),isnumber(b1),isnumber(c1)) as the pre-concdition, rather than the SUM, to get around the SUM accepting blanks issue, and do the IF reversing if needed...I have Excel 2000 version 9.0.2720 by the way...

Thanks again.

IF the number of columns is a variable, then you could use something like this:

=IF(COUNT(A11)<>1+COLUMN(D1)-COLUMN(A1),"",stdevp(a1:d1))




--ron

 

[Peter]

Interesting--I'll try that out and add it to my tool set

Thanks Ron

Regards
Peter