# Excel IF statement

Hello, yes i know that the IF statement is simple, yet allow me to ask; what kind of error am I generating here, and what is the fix?  I get a #NAME? and to me the equation is correct, and my cell format is correct.

This IF statement will get a little more completed.  Believe me or not, but i came up with this IF statement;
=IF(TEXT(\$E1,"dddd")="Sunday",0,IF(OR(E1=\$B\$1,E1=\$B\$2,E1=\$B\$3,E1=\$B\$4,E1=\$B\$5,E1=\$B\$6),(SUM('Off On Peak'!E2:AA2)+'Off On Peak'!AB1),(SUM('Off On Peak'!L1:AA1))))

Am I bragging, yes I am.  I worked hard to get it to work!  And now I can't see the problem with this one.
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
For the first one. You can simply accomplish this by

=IF(\$A1="Logic",1,0)
0
Process Improvement MgrCommented:
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
0
Commented:
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.
0
Author Commented:
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.
0
Commented:
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)
0
Author Commented:
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."
0
Commented:
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
0
Author Commented:
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.
0
Commented:
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.
0
Author Commented:
This works and I am happy with it (no biggie!); =IF(\$A5 ="Logic",1,IF(A5="java",1,IF(A5="fun",1,0)))
0
Commented:
That's how I would also nest the IFs :)
0
Author Commented:
Darn, why would this not work;
=IF(\$A16062 ="throws",A16062,IF(A16062="throwable",A16062,IF(A16062="Exception",A16062,0)))
0
Commented:
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
0
Author Commented:
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 "("
0
Author Commented:
The result is not correct!
0
Author Commented:
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!
0
Author Commented:
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.
0
Author Commented:
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?
0
Commented:
Use the IFERROR function to check whether SEARCH is returning something meaningful. http://office.microsoft.com/en-za/excel-help/iferror-function-HA001231765.aspx
0
Author Commented:
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?
0
Author Commented:
IFERROR does not appear to tell me much;
0
Process Improvement MgrCommented:
chima,

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

Thanks! :)
0
Process Improvement MgrCommented:
=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:

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.
0
Process Improvement MgrCommented:
If I only use =(IFERROR(SEARCH("Exception",A10),"Error reported"))
it's going to return the place where it found Exception.. that's a numerical value. Instead, you're looking for a 'true' or 'false' answer, which is why you need the logical operator (your IF  function)
0
Author Commented:
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
0
VBA ExpertCommented:
Perhaps.

=IF(NOT(ISERROR(SEARCH("Exception",A2))),"Exception",IF(NOT(ISERROR(SEARCH("throws",A2))),"throws",0))
0
Author Commented:
imnorie, perhaps "NOT"  Whether the cell has the word Exception or not, it still has Exception as an outcome!
0
VBA ExpertCommented:
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.
0
Author Commented:
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
0
VBA ExpertCommented:
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.
0
Author Commented:
imnorie, Oh dare, it got me.  i forgot to change the cell location.  Let me go try again.  My apologies.
0
Author Commented:
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.
0
Author Commented:
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
0
Author Commented:
Here is the file...forgot to up load it.
error-throws-sends-email-r4.xlsx
0
VBA ExpertCommented:
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.
0
Author Commented:
One would think that an other expert could take over for imorie?
0
VBA ExpertCommented:
I've had a look at the new file but I'm not sure I understand what the problem is.

You have the formula I posted in B1 but in B2:B5 you have formulas that appear to be have been extracted from the formula in A1 with some variations.

Also, in B2:B5 the formulas don't seem to be referencing the correct cells in column A.

For example in B2 you have a formula that references A1 and in B3 you have a, different, formula referring to A2.

As for the B5, I'm not 100% sure why you see the formula but if I i 're-enter' the formula by pressing F2 followed by enter the result of the formula is displayed.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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,
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.