Need help with an IF statement
Posted by Fred on April 11, 2001 5:24 AM
I have a worksheet where cells A1 thru A10 contain certain dates. I want to key in a date on cell B1 and if it matches any of the dates on cells A1 thru A10, then I want to return a value of TRUE on cell C1. If it doesn't match, then return FALSE.
Posted by Aladin Akyurek on April 11, 2001 5:33 AM
====================
=OR(A1:A4=B1)
Aladin
Posted by Dave Hawley on April 11, 2001 5:40 AM
Hi Fred
Put this formula in cell C1 and copy down as far as needed.
=(IF(B1="","",IF(COUNTIF($A$1:$A$10,B1)=1,TRUE,FALSE)))
Dave
OzGrid Business Applications
Posted by Dave Hawley on April 11, 2001 5:51 AM
Fred, if you into abreviated formulas (which I'm not) you could use:
=B1=$A$1:$A$10
Dave
OzGrid Business Applications
Posted by Mark W. on April 11, 2001 6:26 AM
Aladin's formula will do the trick!
Posted by Dave Hawley on April 11, 2001 8:08 AM
Err, no it doesn't
You guys really should read the questions :o) my other post was a subtle dig, that you have missed.
The request was to show FALSE if the date ENTERED does not match. This will show FALSE when the cell is blank.
...Oh and you also forgot to say it is an Array formula (Ctrl+Shift+Enter).
As I have said many times before arrays have their place, but you guys keep dishing them out for nearly all uses. These arrays when used willy nilly cause a MAJOR slow down in Excels re-calculation.
It really giving Excel users that come here for help a VERY short sighted solution and starts them off on a road of bad habits. The idea here is not to post any old answer that works. Arrays in the wrong situtations are a bit like taking a sledge hammer to a thumb tac!
DaveOzGrid Business Applications
Posted by Mark W. on April 11, 2001 8:54 AM
Frank, some contributors to this forum have a
knee-jerk adversion to the use of array formulas
(those entered using Control+Shift+Enter). This
is unfortunate because array formulas are one of
the most powerful aspects of Excel that sets it
apart from other spreadsheet products.
It's true that Aladin's formulation,
=OR(A1:A10=B1), should be entered as an array
formula. It should also be noted that Dave's
suggested formulation, =B1=$A$1:$A$10, will only
work correctly if the contents of A1 equals B1.
Even if Dave's formula is entered as an array
formula it will only work for the circumstance
described above. What Dave fails to recognize is
that when the results of a comparison between a
single cell and an array of cells is returned to
a cell such a C1 only the results of the 1st
comparison is returned (i.e., B1=A1). It is
essential that the OR() function be used to
summarized the results of all the comparisions
between the single cell and the array.
I hope you'll continue to expand your
understanding of the full power Excel,
and not be dissuaded by a few naysayers.
Posted by Dave Hawley on April 11, 2001 10:23 AM
Sorry Mark, Your Wrong, Wrong Wrong
As I said, my second formula was a subtle dig that went straight over your head.
Array formulas are VERY powerful (fully agree) which why they should be used sparingly and not as a first port of call (as you and Aladin keep doing). To say I'm against them is totally ludicrous, as I have several examples of them on my Webpage. It is knowing when to use them that seperates a the novice from the rest.
Fred, In my occupation I recieve many problems from Excel users that have overloaded their spreadsheets with array formulas and because of this recalculation, saving and opening slows down to a crawl. Please heed my advise and do not fall into the trap of using array formulas for simple calculations. Don't just take my word for it:
Their are lots more out there!
Dave
OzGrid Business Applications
Posted by Mark W. on April 11, 2001 1:10 PM
I don't know about you Fred, but I'm still waiting
for Dave to produce a formula that works!
His 1st attempt, =(IF(B1="","",IF(COUNTIF($A$1:$A$10,B1)=1,TRUE,FALSE))).
returns FALSE if the datevalue in cell B1 appears
more than once in the range, $A$1:$A$10.
His 2nd attempt, =B1=$A$1:$A$10, will only return
TRUE if B1=A1.
When entered as an array formula Aladin's
formulation, =OR(A1:A10=B1), suffers from neither
of these debilitations.
Posted by Dave Hawley on April 12, 2001 12:02 PM
Arhh Fred. Mark has finally go something right! the formula (as he knows) should be:
=(IF(B1="","",IF(COUNTIF($A$1:$A$10,B1)=0,TRUE,FALSE)))
It good to see I have him lost for words on the array debate.
Dave :o)
OzGrid Business Applications
Posted by Dave Hawley on April 12, 2001 12:03 PM
Arhh Fred. Mark has finally go something right! the formula (as he knows) should be:
=(IF(B1="","",IF(COUNTIF($A$1:$A$10,B1)>0,TRUE,FALSE)))
It good to see I have him lost for words on the array debate.
Dave :o)
OzGrid Business Applications
Posted by Mark W. on April 12, 2001 1:03 PM
> It good to see I have him lost for words
> on the array debate.
Never lost for words on this subject. I just
have better things to do with my time.
Posted by Dave Hawley on April 12, 2001 1:36 PM
>Never lost for words on this subject
Now that I do believe! I have a parrot that is never lost for words too. Perhaps I could interest you in some Advanced Excel training Mark, one of the lessons covers arrays and WHEN to use them and when NOT to use them!
But seriously Mark, you really should read up on when to use these powerful formulas.
Sorry, I just can't stop! your just too easy. Now what was you solution to the problem again, oh that's right, you haven't offered one :o)
XXX
OzGrid Business Applications
Posted by Mark W. on April 12, 2001 2:55 PM
Best laugh I've had all day!
Posted by Plato on April 14, 2001 7:52 PM
Try the following formula in C1:
=IF(ISNA(VLOOKUP(B1,A1:A10,1,FALSE)),"FALSE","TRUE")
Plato
