 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006One of lifes little mystery?
IF function if you omit the false parameter, the function returns "FALSE" =IF(A1="Bob",TRUE) so why doesn't it return "TRUE" so you can omit the true parameter e.g =IF(A1="Bob") whilst everyone reading this post knows the answer (clue: it's either TRUE or FALSE) - excel doesn't? - it kicks up syntax error Doh! But you can use =A1="Bob"  SignatureKind regards, Niek Otten Microsoft MVP - Excel | IF function | [quoted text clipped - 12 lines] | | Doh! > But you can use > [quoted text clipped - 17 lines] > | > | Doh! You forgot one: =A!<>"Bob" A B C D E 1 Bull FALSE =IF(A1="Bob") FALSE TRUE 1 Bob TRUE =IF(A1="Bob") TRUE FALSE Formulas =IF(A1="Bob",TRUE) =IF(A1="Bob") =A1="Bob" =A!<>"Bob" A1="Bob" will return FALSE then SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > > But you can use > > [quoted text clipped - 23 lines] > 1 Bob TRUE =IF(A1="Bob") TRUE FALSE > Formulas =IF(A1="Bob",TRUE) =IF(A1="Bob") =A1="Bob" =A!<>"Bob" > A1="Bob" will return FALSE then > [quoted text clipped - 25 lines] >> 1 Bob TRUE =IF(A1="Bob") TRUE FALSE >> Formulas =IF(A1="Bob",TRUE) =IF(A1="Bob") =A1="Bob" =A!<>"Bob" Conversely: =A!<>"Bob" will return TRUE / FALSE as well. it still seems strage that the IF function doesn't need a false argument but does need a true one | > A1="Bob" will return FALSE then | > [quoted text clipped - 40 lines] | > | Conversely: =A!<>"Bob" will return TRUE / FALSE as well. The whole point of our posts is that it doesn't. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > it still seems strage that the IF function doesn't need a false argument but > does need a true one [quoted text clipped - 44 lines] > | > > | Conversely: =A!<>"Bob" will return TRUE / FALSE as well. | The whole point of our posts is that it doesn't. a 'work around' doesn't answer the IF function enigma - (e.g 99% of alllllllll XL functions need not exist at all - thay all have a work around) that being that the IF function returns boolean logic (Heads or tails) so why does it insist on one parameter and not the other logic dictates it should insist on both or neither! - this defys logic i'm sure they'll fix it one day | -- | HTH [quoted text clipped - 53 lines] | > | > | > | Conversely: =A!<>"Bob" will return TRUE / FALSE as well. > a 'work around' doesn't answer the IF function enigma - (e.g 99% of > alllllllll XL functions need not exist at all - thay all have a work around) > > that being that the IF function returns boolean logic (Heads or tails) No, it evaluates the first term to a boolean, then returns one of two optional parameters. > so why does it insist on one parameter and not the other logic > dictates it should insist on both or neither! - this defys logic Neither parameter is required. An omitted parameter evaluates to zero: =IF(TRUE,) ==> 0 =IF(TRUE,,) ==> 0 =IF(TRUE,,3) ==> 0 =IF(FALSE,,) ==> 0 =IF(FALSE,) ==> FALSE The latter evaluates to FALSE because no false parameter was supplied - i.e., the result of the statement is formally undefined. In a language that supplies parameters by position only, the True parameter being undefined isn't possible. I'll admit that it seems to me that returning an error would be more appropriate... > i'm sure they'll fix it one day Since it operates exactly like Help says it does, I wouldn't hold my breath. | > a 'work around' doesn't answer the IF function enigma - (e.g 99% of | > alllllllll XL functions need not exist at all - thay all have a work around) [quoted text clipped - 22 lines] | I'll admit that it seems to me that returning an error would be more | appropriate... i can only say 'whatever' =IF(A1="Bob") suggests obvious boolean logic obvious to anyone bar XL - for some reason it needs a poke in the right direction: =IF(A1="Bob",TRUE) regarding parameter placing - there's no reason both parameters can't be optional (if one is) the engima still stands | > i'm sure they'll fix it one day | | Since it operates exactly like Help says it does, I wouldn't hold my | breath. despite it behaving contrary to logic (ironic since it's a logical function) > it still seems strage that the IF function doesn't need a false argument > but [quoted text clipped - 46 lines] > | > > | Conversely: =A!<>"Bob" will return TRUE / FALSE as well. I think the point is you don't need to use the IF statement 1) to return the results you want, 2) that is designed on the bias you perceive. | > it still seems strage that the IF function doesn't need a false argument | > but [quoted text clipped - 49 lines] | I think the point is you don't need to use the IF statement 1) to return the | results you want, 2) that is designed on the bias you perceive. as i expressed in another post - 'most' of Xl functions can be made redundant (can be worked around) - that doesn't stop the IF function having a fundimental design flaw > | > it still seems strage that the IF function doesn't need a false > argument [quoted text clipped - 58 lines] > having > a fundimental design flaw It isn't flawed. If it was flawed it wouldn't work. Just because you feel it should work another way doesn't qualify for being flawed. I see you may have been looking for an explanation on if the logic was bolean or not and why the design of the function was as is. All the examples use a bolean 0 or 1 - False or True wether the function is designed to consider False instead of True or vice versa is irrelevant (although it's design seems to indicate the predominance of a False outcome, ergo needing a definition for True condition) and we certainly don't need two functions to do the same thing. However, the point is if you are really insistent on a function designed with the predominance of a True outcome (as in nearly all of your test conditions will default to True) then use a work around (which in essence is another if function without the if. | It isn't flawed............ All the examples use a bolean 0 | or 1 - False or True wether the function is designed to consider False | instead of True or vice versa is irrelevant...... indicate the predominance of a False outcome........needing a definition for |.....True condition......we certainly don't need two functions ......a function ....designed with the predominance of a True |outcome........then use a work around If you say so - but just for the very deep bowels of google groups =IF(A1="Bob") has an obvious boolean answer that a well trained chimp could figure there is no logical reason to 'design' the false argument as 'optional' and not the true one whilst several people have attempted to defend the design - none of them stand up to scrutiny & i do consider it's no mare than just that 'a defence' Microsoft are not about to change it's parameters (fix it properly) because it would/could lead to backward compatability problems neither of these things can change the fact that the function is fundimentally flawed It must be hard being the only sane man amongst a host of idiots. > | It isn't flawed............ All the examples use a bolean 0 > | or 1 - False or True wether the function is designed to consider False [quoted text clipped - 21 lines] > neither of these things can change the fact that the function is > fundimentally flawed The chimp would just use =A1="Bob". using =IF(A1="Bob") to return a TRUE/FALSE result makes as much sense as =+SUM(A1) versus =A1 > | It isn't flawed............ All the examples use a bolean 0 > | or 1 - False or True wether the function is designed to consider False [quoted text clipped - 21 lines] > neither of these things can change the fact that the function is > fundimentally flawed > | It isn't flawed............ All the examples use a bolean 0 > | or 1 - False or True wether the function is designed to consider False [quoted text clipped - 24 lines] > neither of these things can change the fact that the function is > fundimentally flawed You know, Microsoft is not the original designer of the IF function. The first time I ran into it was on my Mac at the base, IIe at home using VisiCalc back in the early 80s. It was Always =IF(?,True_Cond,False_Cond). Find out if it's true first and default to false if it isn't. You'll have to pin the rap on Software Arts who originated the software. Microsoft as always copied it. Both parameters are optional, but the firt comma is not... =IF(A1="Bob",) SignatureFestina Lente > IF function > [quoted text clipped - 12 lines] > > Doh! Ah ! Sorry, I got it wrong at first. You are right, the first parameter is not really optional. But it would not be a very useful entry with no parameters, would it ? LOL SignatureFestina Lente > Both parameters are optional, but the firt comma is not... > =IF(A1="Bob",) [quoted text clipped - 15 lines] > > > > Doh! | Ah ! | Sorry, I got it wrong at first. | You are right, the first parameter is not really optional. | | But it would not be a very useful entry with no parameters, would it ? Yes! if you merely need a TRUE or FALSE answer | LOL | -- [quoted text clipped - 21 lines] | > > | > > Doh! In that case, no need for the IF() at all, as already mentioned by others... SignatureFestina Lente > | Ah ! > | Sorry, I got it wrong at first. [quoted text clipped - 30 lines] > | > > > | > > Doh! in that case you dont need 'most' of the XL functions | In that case, no need for the IF() at all, as already mentioned by others... | -- [quoted text clipped - 34 lines] | > | > > | > | > > Doh!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.