Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement question
Can someone translate the following IF statement for me?
=IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),"A","B") I'm confused abut the first part, specifically the asterisk separating the two criteria. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement question
Nel post
*KarenH* ha scritto: Can someone translate the following IF statement for me? =IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),"A","B") I'm confused abut the first part, specifically the asterisk separating the two criteria. Thanks! Hi Karen, The formula can be written also in this way: =IF(AND((A2<""),(ISNA(MATCH(A2,$A$1:A1,0)))),"A", "B") Its mean that: IF A2<"" (i.e. A2 is not empty) AND the value in A2 doesn't match the value in A1(the formula MATCH is used to search the value in A2 in A1, if the two values are different, MATCH function returns #N/A! error the formula ISNA convert the error in TRUE) THEN write A in the cell the formula is written ELSE write B in the cell. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement question
Karen, =IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),"A","B") IF A2 is not blank AND (*) A2 is not (ISNA) found (MATCH) in the range $A$1:A1 then return the letter A IF both are not true then return the letter B. You could write the formula like this, =IF(AND(A2<"",ISNA(MATCH(A2,$A$1:A1,0))),"A","B") Having the $ in the first A1 and not the second allows you to expand the range when you copy down without losing the starting point. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=555027 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement question
Thanks to Franz and SteveG for this explanation -- that helped a lot! I'd
never seen the asterisk used as an "and" before, I always think of it as a multiplication symbol. I appreciate the helpful answers! "Franz Verga" wrote: Nel post *KarenH* ha scritto: Can someone translate the following IF statement for me? =IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),"A","B") I'm confused abut the first part, specifically the asterisk separating the two criteria. Thanks! Hi Karen, The formula can be written also in this way: =IF(AND((A2<""),(ISNA(MATCH(A2,$A$1:A1,0)))),"A", "B") Its mean that: IF A2<"" (i.e. A2 is not empty) AND the value in A2 doesn't match the value in A1(the formula MATCH is used to search the value in A2 in A1, if the two values are different, MATCH function returns #N/A! error the formula ISNA convert the error in TRUE) THEN write A in the cell the formula is written ELSE write B in the cell. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement question
You're welcome and thanks for the feedback. Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=555027 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF statement question
Yes the * is the multiplication symbol. In this instance, it is multiplying
two boolean values (true or false.) If both values are true, that works out to be the same as AND. True and True = True. True * True = 1 (which is true.) Anything else will be 0 or false. False * true (same as 0 * 1 and as we all know, 0 times anything is 0.) True * False or False * False will be 0 (or false.) -- Kevin Vaughn "KarenH" wrote: Thanks to Franz and SteveG for this explanation -- that helped a lot! I'd never seen the asterisk used as an "and" before, I always think of it as a multiplication symbol. I appreciate the helpful answers! "Franz Verga" wrote: Nel post *KarenH* ha scritto: Can someone translate the following IF statement for me? =IF((A2<"")*(ISNA(MATCH(A2,$A$1:A1,0))),"A","B") I'm confused abut the first part, specifically the asterisk separating the two criteria. Thanks! Hi Karen, The formula can be written also in this way: =IF(AND((A2<""),(ISNA(MATCH(A2,$A$1:A1,0)))),"A", "B") Its mean that: IF A2<"" (i.e. A2 is not empty) AND the value in A2 doesn't match the value in A1(the formula MATCH is used to search the value in A2 in A1, if the two values are different, MATCH function returns #N/A! error the formula ISNA convert the error in TRUE) THEN write A in the cell the formula is written ELSE write B in the cell. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Statement Question | Excel Discussion (Misc queries) | |||
Nested IF Statement Question | Excel Discussion (Misc queries) | |||
IF Statement question | Excel Worksheet Functions | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
If Statement Question | Excel Worksheet Functions |