Excel IF statement

For the first one. You can simply accomplish this by

=IF($A1="Logic",1,0)

the TEXT(   part of your formula is supposed to convert a numerical value to TEXT. You have no numerical values in $A1, furthermore, the format_text is used incorrectly.

I wasn't sure what you were trying to achieve, but here's a reference to the TEXT function in Excel

http://office.microsoft.com/en-us/excel-help/text-function-HP010062580.aspx

What is "format_text" in the formula in B2? I think you must take it away. What are you trying to do there... passing the value "Logic" as the format text actually has no meaning and will likely only return the string "Logic" anyway - making the whole use of TEXT questionable to begin with? The syntax is incorrect.
Your long complex formula is fine (Assuming you have a sheet called 'Off on Peak'). But it is what is currently in B2 that has issues.

To all who responded; I apologize.  The second IF statement is one that I had worked on about 2 months ago and it work.
I have just forgotten how the "dddd" part works.

Subramani, I tried what you wrote and for some reason all I get is this; = IF($A2="Logic",1,0)  
I tried this as well; =IF($A2="Logic",1,0)    The cell has a number format.

The "dddd" just returns the name of the day of the week for the given value. Saturday, Sunday, Monday, Tuesday, etc

=TEXT(0,"dddd") wou'd return "Saturday"  (depending on your first day of the week of course)

MilandaT, thank you for your response.  At the moment I need to figure out why = IF($A2="Logic",1,0)   is not giving me what I want.  Just to clarify; I changed the $A1 to the cell $A2, at A2 I have the word "Logic."

I have recreated your sample in attached file, and the formula seems to work. Do you not have "spaces" around the work in A2?
sample.xlsx

MlandaT, interesting, I'm using Excel 2013 and I guess the cell needed to have a format of "General."  I changed that and  it worked.  Would like to view your comment about this.  Also, I need to add a couple of "OR" to it.  May I keep this question open?  Even though you and the others helped on it.

I don't see why the formatting should matter. I've just tried changing my formats here, and everything still just works. I'm also using Excel 2013 here.

You can perhaps close this question if the issues have been resolved. I don't know. Up to you.

This works and I am happy with it (no biggie!); =IF($A5 ="Logic",1,IF(A5="java",1,IF(A5="fun",1,0)))
Your comments?

That's how I would also nest the IFs :)

Darn, why would this not work;
=IF($A16062 ="throws",A16062,IF(A16062="throwable",A16062,IF(A16062="Exception",A16062,0)))

You are doing a check for equality where a check for whether A1062 contains the word throws should be done.
http://office.microsoft.com/en-za/excel-help/check-if-a-cell-contains-text-HP003056106.aspx#BMcheck_if_part_of_a_cell_matches_speci

MlandaT, thanks for pointing that out, and for the link.  I had realized that too, at the same time.  The link helped.
It likes this; =IF(SEARCH("throws",A15),IF(OR(SEARCH("Exception",A15)),0))   but the parenthesis are off by one "("

The result is not correct!

I do need an "OR" in there, and with this statement;
=IF(SEARCH("throws",A15),IF(OR(SEARCH("Exception",A15),1,0,0)  I do not get an error, it simply displays the statement in the cell.  It is the last right side that is the problem!

Got it...almost.  This works, the trick is the double )) after the last ALL;
=IF(SEARCH("Exception", A11),IF(OR(SEARCH("throws",A11)),"yes",no),2)
The problem now, is that when there isn't the word throws or the word Exception, then I get #VALUE!

This works; =IF(SEARCH("Exception", A13), IF(OR(SEARCH("Exception", A13)), "yes", no), 2)
It seems that the OR is a "stand alone" after it does the first search.

I tried several ways, the first search works if there is the word Exception;
=IF((SEARCH("Exception", A22)),"yes",(  IF(OR(SEARCH("throws", A22)),"y","n")    ))
If Exception is not present, then the first search is not "false" but is #VALUE!, otherwise there is no error.
Any suggestions?

MLindaT, thank you for helping out.  I tried this two statements, based on my understanding as to how to use it;
IF((IFERROR(SEARCH("Exception", A26),"")),"yes",(  IF(OR(SEARCH("throws", A26)),"y","n")    ))      - got #VALUE!
            
IFERROR(IF((SEARCH("Exception", A27)),"yes",(  IF(OR(SEARCH("throws", A27)),"y","n")    )),"")      - with this I got an "empty cell"

Am I using the command correctly?

IFERROR does not appear to tell me much;

chima,

Please give me the contents of cell A15, and a clear statement of what you want to achieve in cell B15.

Thanks! :)

=IFERROR(IF(SEARCH("Exception",A8),"yes","no"),"Error reported")

If you evaluate this, you would never really get the answer "no".. you would get "yes" or error reported.

When building IFERROR, try building backwards.

For example:

IFERROR(value, value_if_error)   ..... I would start with  =IFERROR(value, "Error reported")
that way I don't get confused with everything else...

Now, I want to add my logical statement... it goes inside of value. Build it separately.

=IF(SEARCH("Exception",A15),"yes","no")

this says... if I look in A15 for the word "Exception" and I find it, say "yes", if I don't find it, say no. However, if it doesn't find it, it really is just going to throw a #VALUE error, as you have seen.

A previous expert was saying,  "Then, use IFERROR"

When I use IFERROR, I can eliminate the "no" statement, because I'ts returning a #VALUE error anyway. So I can just incorporate the statements together:


As before, I started with   =IFERROR(value, "Error reported")
Now, I can add the other statement I build, because I still have to find the value.

=IFERROR(IF(SEARCH("Exception",A8),"yes"),"Error reported")

it says:  If I find an error anywhere, type "Error reported". Otherwise, search for the term Exception in cel A8. If you find it, type yes.

Netminder and Iammontoya, thank you for responding.  IFERROR is not the problem.  Here is a recap;
My objective is to get an "IF statement" output of "yes" if the cell evaluated contains the word "Exception."  Or, if there isn't the word Exception, to have the IF statement evaluate the "falseness" of it, checking to see if the word "throws" is in that same cell.  In the statement below, the IF statement's output would be "y", if it works, or "n" if neither words are found;

=IF((SEARCH("Exception", A22)),"yes",(  IF(OR(SEARCH("throws", A22)),"y","n")    ))

If Exception is not present, then the first search is not "false" but is #VALUE!, otherwise there is no error.

What does happen, is that the first evaluation of SEARCH("Exception", A22 "when the word is not present" evaluates to "#VALUE!" and stops.  The SEARCH evaluates to "true" and the IF statement should evaluate to "yes" or go on to the second part of the IF statement.  Please do not focus on the cell number "A22" or what ever, because I am just troubleshooting it.  In fact, let me submit/add the file I have.
Thank you for the help.  Call me crazy, because I'm sure there are other ways of accomplishing this, but once on got curious as to how to make this IF statement work, I could not let it go.  In the file the cell filled in green are working statements.
error-throws-sends-email-r2.xlsx

Perhaps.

=IF(NOT(ISERROR(SEARCH("Exception",A2))),"Exception",IF(NOT(ISERROR(SEARCH("throws",A2))),"throws",0))

imnorie, perhaps "NOT"  Whether the cell has the word Exception or not, it still has Exception as an outcome!

That's strange, it's not what I'm seeing, can you give some examples where the wrong result is returned.

Perhaps even upload some sample date with expected results.

By the way, how are you regarding ClassCastException? That will be flagged as 'Exception' as SEARCH looks for partial matches.

If you only want to find the single word 'Exception', or '@throws', add a space at the start and end of the word you are looking for.

imnorie, On an earlier post (D: 40317207) I submitted the test file.  When I got your IF statement, I simply "copied and pasted" your solution into line 32 (not that it matters which line).  Your solution initially gave me the word "Exception."
When I deleted Exception, I was hoping to get throws, but I did not get anything, the word Exception remained (even when I deleted the word Exception from the test cell.  Then I deleted throw and got the same result;
Test cell was:
104: * @throws ClassCastException if the specified object's type prevents it  

Changed:
104: * @ ClassCast if the specified object's type prevents it                           Exception

I downloaded the file from that post, and it does matter where you copy/paste/put the formula.

If you copied/pasted a formula that refers to row 2 into row 32 then the formula will still refer to row 2.

Also that file doesn't really show the expected results.:)

PS The formulas in column B seem to be doing different things.

imnorie, Oh dare, it got me.  i forgot to change the cell location.  Let me go try again.  My apologies.

imnorie, awesome!
=IF(NOT(ISERROR(SEARCH("Exception",A32))),"Exception",IF(NOT(ISERROR(SEARCH("throws",A32))),"throws",0))
I reckon that the NOT and ISERROR are essential?  I struggled with the parenthesis, is there a function within Excel that helps with this.  I know that the colors change, yet it seemed to me that it was not very useful.
Once again thanks.  Points coming shortly.

imnorie; I have a couple of questions, if I may ask;
If a SEARCH returns a "true" or "false," why is it a 25?  I checked the format and it is "General"
When I changed it to text, the 25 was still displayed.

The other strange behavior, is when I change the A3 to what it should be A5, then I get the "statement" displayed.
I changed the format to "General" and the statement is still displayed.
Is this why the ISERROR is needed?
Thanks

Here is the file...forgot to up load it.
error-throws-sends-email-r4.xlsx

SEARCH doesn't return True/False,  it returns the character position the search term is found in the string being searched, or, if it doesn't find the search term, an error.

As to your second question,  I'll have a look when I get a chance to download the new workbook you attached.

One would think that an other expert could take over for imorie?

imnorie, thank you for the solution.  I'll have to look at the final question later.  I do not have time now.  
IamMontoya, iamMartinez thank you for your input.
MirandaT you gave me a lot of your time, I greatly appreciated your effort.  Hopefully next time you will solve my question,