#VALUE! Error
Posted by Patrick on March 11, 2001 6:46 AM
I am trying to get this to work
I have this
B65=IF(ISNUMBER(SEARCH("Jan",List1a)),"Jan","None")
B66=IF(ISNUMBER(SEARCH("Jan",List1b)),"Jan","None")
B67=IF(ISNUMBER(SEARCH("Jan",List1c)),"Jan","None")
B68=IF(ISNUMBER(SEARCH("Jan",List1d)),"Jan","None")
they return
B65 Jan
B66 None
B67 None
B68 None
I have defined a name Holidays...$B$65:$B$68
all is great except now
B64=IF(Holidays=Jan,"yes","no")
B64 RETURNS #VALUE!
Thanks in advance!!
Posted by Mo on March 11, 2001 9:55 AM
Hello Patrick, try this formula
B64=IF(Holidays="Jan","yes","no")
Posted by Aladin Akyurek on March 11, 2001 10:25 AM
Hi Guys,
I'm sorry to say it, but this is not the way you can use the IF-function. You can't feed a 2 or more cells as you guys do and expect a correct result. If the first cell of the range named Holidays contains Jan, you'll get a Yes. If the first cell does not contain Jan but any other cell does, you'll get No.
What Patrick wants can be done with an array formula (which you enter by hitting CONTROL+SHIFT+ENTER) like the following:
=IF(EXACT(UPPER("Jan"),UPPER(Holidays)),"Yes","No")
Another point: the way Patrick builds up Holidays is also questionable. I'd advise using a similar formula that I just gave.
Aladin
Posted by Dave Hawley on March 11, 2001 3:55 PM
Hi all
Just my two cents worth, but I wouldn't use an array formula unless really necessary. My reasons being, like loops in VBA they seem to become habit forming and too many of them really slow down re-calculation. Here is another way without an array.
=IF(LOOKUP("Jan",Holidays)="Jan","Yes","No")
Dave
OzGrid Business Applications
Posted by The Stalker on March 11, 2001 4:26 PM
I think(?) that the formula =IF(LOOKUP("Jan",Holidays)="Jan","Yes","No") will return #N/A if "Jan" does not appear in Holidays.
I think the following structure is required :-
=IF(ISNA(LOOKUP("Jan",Holidays)),"No","Yes")
or an alternative :-
=IF(ISNA(MATCH("Jan",Holidays,0)),"No","Yes")
Stalker
Posted by Dave Hawley on March 11, 2001 4:31 PM
You Think Wrong!
That's because you didn't try it first, isn't it Stalker :o)
Dave
OzGrid Business Applications
Posted by The Stalker on March 11, 2001 4:37 PM
Re: You Think Wrong!
I didn't try it first but have now and get #N/A. What am I doing wrong?
Stalker
Posted by Dave Hawley on March 11, 2001 4:41 PM
Sorry Stalker
I didn't try it first but have now and get #N/A. What am I doing wrong?
You aren't doing anything wrong Stalker, It's me. I doing too many things at once my apologies.
Dave
OzGrid Business Applications
