MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Value Error...again


Posted by Patrick on April 25, 2001 6:46 AM

I have a cell that contains this formula
=IF((E1=37197),"",CK1+1)
It produces a value error....that's fine with me..
what I would like to do is just to hide that there
is an error......I tried ISERROR but guess I never
put it together right...I have also used up the 3
conditional formats allowed for this cell
thanks in advance


Posted by IML on April 25, 2001 7:11 AM

How about

=IF((AND(E1=37197,ISNUMBER(CK1))),CK1+1,"")

good luck

Posted by IML on April 25, 2001 7:18 AM

Not awake yet...

How about

=IF(E1=37197,"",IF(ISNUMBER(CK1),+CK1+1,""))

Posted by Patrick on April 25, 2001 7:27 AM

Re: Not awake yet...

The second works great!!
Thanks so much......I was about to write a book
report on how I have things set up....wow!!
Thanks again

Posted by Aladin Akyurek on April 25, 2001 7:55 AM

It seems to me that the condition arg of IF should give trouble:

Simply change it to:

=IF(E1=37197,"",CK1+1)

I read this as when E1 is equal to 37197 (which I assume to be a number, then nothing (""), else add 1 to the value of CK1 which must also be a number.

Aladin

Posted by IML on April 25, 2001 2:46 PM

Re: Not awake yet...

No problem, glad to help. I was looking at it again and the following may be a little cleaner than nesting if statements.

=IF(AND(E1<>37197,ISNUMBER(CK1)),+CK1+1,"")

I think this what I was thinking of on my unsuccessful first shot.