Solved

Excel IF statement

Posted on 2014-09-05
39
75 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:Iammontoya
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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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:Iammontoya
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:Iammontoya
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:Iammontoya
Iammontoya 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

759 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now