Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel IF statement

Posted on 2014-09-05
39
78 Views
Last Modified: 2014-10-14
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.
If-logic.PNG
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.
0
Comment
Question by:chima
  • 21
  • 7
  • 5
  • +2
39 Comments
 
LVL 1

Expert Comment

by:Subramani N
ID: 40306536
For the first one. You can simply accomplish this by

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

Expert Comment

by:Montoya
ID: 40306540
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
 
LVL 30

Expert Comment

by:MlandaT
ID: 40306586
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.Examples of correct syntaxes (http://www.excelfunctions.net/Excel-Text-Function.html)
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:chima
ID: 40306627
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
 
LVL 30

Expert Comment

by:MlandaT
ID: 40306637
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 Comment

by:chima
ID: 40306754
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
 
LVL 30

Expert Comment

by:MlandaT
ID: 40306803
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 Comment

by:chima
ID: 40306858
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
 
LVL 30

Expert Comment

by:MlandaT
ID: 40306866
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 Comment

by:chima
ID: 40306868
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?
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 40306875
That's how I would also nest the IFs :)
0
 

Author Comment

by:chima
ID: 40306879
Darn, why would this not work;
=IF($A16062 ="throws",A16062,IF(A16062="throwable",A16062,IF(A16062="Exception",A16062,0)))
IF why not?
0
 
LVL 30

Expert Comment

by:MlandaT
ID: 40306898
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 Comment

by:chima
ID: 40306940
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 "("
IF search
0
 

Author Comment

by:chima
ID: 40306941
The result is not correct!
0
 

Author Comment

by:chima
ID: 40306976
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 Comment

by:chima
ID: 40308929
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 Comment

by:chima
ID: 40309134
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
 
LVL 30

Assisted Solution

by:MlandaT
MlandaT earned 175 total points
ID: 40310507
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 Comment

by:chima
ID: 40310724
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 Comment

by:chima
ID: 40310790
IFERROR does not appear to tell me much;
if error
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40316701
chima,

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

Thanks! :)
0
 
LVL 19

Expert Comment

by:Montoya
ID: 40316724
=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.
0
 
LVL 19

Assisted Solution

by:Montoya
Montoya earned 50 total points
ID: 40316730
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 Comment

by:chima
ID: 40317207
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
 
LVL 33

Expert Comment

by:Norie
ID: 40317225
Perhaps.

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

Author Comment

by:chima
ID: 40317656
imnorie, perhaps "NOT"  Whether the cell has the word Exception or not, it still has Exception as an outcome!
0
 
LVL 33

Expert Comment

by:Norie
ID: 40317672
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 Comment

by:chima
ID: 40317834
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
 
LVL 33

Expert Comment

by:Norie
ID: 40318310
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 Comment

by:chima
ID: 40319405
imnorie, Oh dare, it got me.  i forgot to change the cell location.  Let me go try again.  My apologies.
0
 

Author Comment

by:chima
ID: 40319433
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 Comment

by:chima
ID: 40319555
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 Comment

by:chima
ID: 40319559
Here is the file...forgot to up load it.
error-throws-sends-email-r4.xlsx
0
 
LVL 33

Expert Comment

by:Norie
ID: 40320069
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 Comment

by:chima
ID: 40354608
One would think that an other expert could take over for imorie?
0
 
LVL 33

Accepted Solution

by:
Norie earned 275 total points
ID: 40374355
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
 

Author Closing Comment

by:chima
ID: 40380331
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question