Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default One 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!


  #2   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default One of lifes little mystery?

But you can use

=A1="Bob"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JethroUK©" wrote in message ...
| 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!
|
|


  #3   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default One of lifes little mystery?

"Niek Otten" wrote in message
...
But you can use

=A1="Bob"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JethroUK©" wrote in message
...
| 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!
|
|

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"


  #4   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default One of lifes little mystery?

A1="Bob" will return FALSE then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PBalmanno" wrote in message
news:8EOVg.1067$BC6.432@fed1read01...
"Niek Otten" wrote in message
...
But you can use

=A1="Bob"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JethroUK©" wrote in message
...
| 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!
|
|

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"




  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
external usenet poster
 
Posts: 208
Default One of lifes little mystery?

Both parameters are optional, but the firt comma is not...
=IF(A1="Bob",)
--
Festina Lente


"JethroUK©" wrote:

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!





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
external usenet poster
 
Posts: 208
Default One of lifes little mystery?

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
--
Festina Lente


"PapaDos" wrote:

Both parameters are optional, but the firt comma is not...
=IF(A1="Bob",)
--
Festina Lente


"JethroUK©" wrote:

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!



  #7   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default One of lifes little mystery?

"Bob Phillips" wrote in message
...
A1="Bob" will return FALSE then

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"PBalmanno" wrote in message
news:8EOVg.1067$BC6.432@fed1read01...
"Niek Otten" wrote in message
...
But you can use

=A1="Bob"

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JethroUK©" wrote in message
...
| 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!
|
|

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"



Conversely: =A!<"Bob" will return TRUE / FALSE as well.


  #8   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default One of lifes little mystery?

it still seems strage that the IF function doesn't need a false argument but
does need a true one


"PBalmanno" wrote in message
news:RKVVg.1116$BC6.563@fed1read01...
| "Bob Phillips" wrote in message
| ...
| A1="Bob" will return FALSE then
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "PBalmanno" wrote in message
| news:8EOVg.1067$BC6.432@fed1read01...
| "Niek Otten" wrote in message
| ...
| But you can use
|
| =A1="Bob"
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "JethroUK©" wrote in message
| ...
| | 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!
| |
| |
|
| 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"
|
|
|
| Conversely: =A!<"Bob" will return TRUE / FALSE as well.
|
|


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
external usenet poster
 
Posts: 49
Default One of lifes little mystery?


"PapaDos" wrote in message
...
| 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
| --
| Festina Lente
|
|
| "PapaDos" wrote:
|
| Both parameters are optional, but the firt comma is not...
| =IF(A1="Bob",)
| --
| Festina Lente
|
|
| "JethroUK©" wrote:
|
| 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!
|
|
|


  #10   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default One of lifes little mystery?

The whole point of our posts is that it doesn't.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"JethroUK©" wrote in message
...
it still seems strage that the IF function doesn't need a false argument

but
does need a true one


"PBalmanno" wrote in message
news:RKVVg.1116$BC6.563@fed1read01...
| "Bob Phillips" wrote in message
| ...
| A1="Bob" will return FALSE then
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "PBalmanno" wrote in message
| news:8EOVg.1067$BC6.432@fed1read01...
| "Niek Otten" wrote in message
| ...
| But you can use
|
| =A1="Bob"
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "JethroUK©" wrote in message
| ...
| | 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!
| |
| |
|
| 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"
|
|
|
| Conversely: =A!<"Bob" will return TRUE / FALSE as well.
|
|






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
external usenet poster
 
Posts: 208
Default One of lifes little mystery?

In that case, no need for the IF() at all, as already mentioned by others...
--
Festina Lente


"JethroUK©" wrote:


"PapaDos" wrote in message
...
| 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
| --
| Festina Lente
|
|
| "PapaDos" wrote:
|
| Both parameters are optional, but the firt comma is not...
| =IF(A1="Bob",)
| --
| Festina Lente
|
|
| "JethroUK©" wrote:
|
| 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!
|
|
|



  #12   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default One of lifes little mystery?

JethroUK©" wrote in message
...
it still seems strage that the IF function doesn't need a false argument
but
does need a true one


"PBalmanno" wrote in message
news:RKVVg.1116$BC6.563@fed1read01...
| "Bob Phillips" wrote in message
| ...
| A1="Bob" will return FALSE then
|
| --
| HTH
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "PBalmanno" wrote in message
| news:8EOVg.1067$BC6.432@fed1read01...
| "Niek Otten" wrote in message
| ...
| But you can use
|
| =A1="Bob"
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "JethroUK©" wrote in message
| ...
| | 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!
| |
| |
|
| 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"
|
|
|
| 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.


  #13   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default One of lifes little mystery?


"Bob Phillips" wrote in message
...
| 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
|
| Bob Phillips
|
| (replace somewhere in email address with gmail if mailing direct)
|
| "JethroUK©" wrote in message
| ...
| it still seems strage that the IF function doesn't need a false argument
| but
| does need a true one
|
|
| "PBalmanno" wrote in message
| news:RKVVg.1116$BC6.563@fed1read01...
| | "Bob Phillips" wrote in message
| | ...
| | A1="Bob" will return FALSE then
| |
| | --
| | HTH
| |
| | Bob Phillips
| |
| | (replace somewhere in email address with gmail if mailing direct)
| |
| | "PBalmanno" wrote in message
| | news:8EOVg.1067$BC6.432@fed1read01...
| | "Niek Otten" wrote in message
| | ...
| | But you can use
| |
| | =A1="Bob"
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "JethroUK©" wrote in message
| | ...
| | | 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!
| | |
| | |
| |
| | 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"
| |
| |
| |
| | Conversely: =A!<"Bob" will return TRUE / FALSE as well.
| |
| |
|
|
|
|


  #14   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default One of lifes little mystery?


"PBalmanno" wrote in message
news:6g7Wg.1133$BC6.249@fed1read01...
| JethroUK©" wrote in message
| ...
| it still seems strage that the IF function doesn't need a false argument
| but
| does need a true one
|
|
| "PBalmanno" wrote in message
| news:RKVVg.1116$BC6.563@fed1read01...
| | "Bob Phillips" wrote in message
| | ...
| | A1="Bob" will return FALSE then
| |
| | --
| | HTH
| |
| | Bob Phillips
| |
| | (replace somewhere in email address with gmail if mailing direct)
| |
| | "PBalmanno" wrote in message
| | news:8EOVg.1067$BC6.432@fed1read01...
| | "Niek Otten" wrote in message
| | ...
| | But you can use
| |
| | =A1="Bob"
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "JethroUK©" wrote in message
| | ...
| | | 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!
| | |
| | |
| |
| | 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"
| |
| |
| |
| | 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.
|
|

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


  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
external usenet poster
 
Posts: 49
Default One of lifes little mystery?

in that case you dont need 'most' of the XL functions


"PapaDos" wrote in message
...
| In that case, no need for the IF() at all, as already mentioned by
others...
| --
| Festina Lente
|
|
| "JethroUK©" wrote:
|
|
| "PapaDos" wrote in message
| ...
| | 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
| | --
| | Festina Lente
| |
| |
| | "PapaDos" wrote:
| |
| | Both parameters are optional, but the firt comma is not...
| | =IF(A1="Bob",)
| | --
| | Festina Lente
| |
| |
| | "JethroUK©" wrote:
| |
| | 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!
| |
| |
| |
|
|
|




  #16   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,624
Default One of lifes little mystery?

In article ,
"JethroUK©" wrote:

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.
  #17   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default One of lifes little mystery?


"JE McGimpsey" wrote in message
...
| In article ,
| "JethroUK©" wrote:
|
| 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 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)


  #18   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default One of lifes little mystery?

"JethroUK©" wrote in message
...

"PBalmanno" wrote in message
news:6g7Wg.1133$BC6.249@fed1read01...
| JethroUK©" wrote in message
| ...
| it still seems strage that the IF function doesn't need a false
argument
| but
| does need a true one
|
|
| "PBalmanno" wrote in message
| news:RKVVg.1116$BC6.563@fed1read01...
| | "Bob Phillips" wrote in message
| | ...
| | A1="Bob" will return FALSE then
| |
| | --
| | HTH
| |
| | Bob Phillips
| |
| | (replace somewhere in email address with gmail if mailing direct)
| |
| | "PBalmanno" wrote in message
| | news:8EOVg.1067$BC6.432@fed1read01...
| | "Niek Otten" wrote in message
| | ...
| | But you can use
| |
| | =A1="Bob"
| |
| | --
| | Kind regards,
| |
| | Niek Otten
| | Microsoft MVP - Excel
| |
| | "JethroUK©" wrote in message
| | ...
| | | 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!
| | |
| | |
| |
| | 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"
| |
| |
| |
| | 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.
|
|

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 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.


  #19   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default One of lifes little mystery?


"PBalmanno" wrote in message
news:vUUWg.1235$BC6.242@fed1read01...

| 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


  #20   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default One of lifes little mystery?

It must be hard being the only sane man amongst a host of idiots.


"JethroUK©" wrote in message
...

"PBalmanno" wrote in message
news:vUUWg.1235$BC6.242@fed1read01...

| 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






  #21   Report Post  
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers,microsoft.public.excel
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default One of lifes little mystery?

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


"JethroUK©" wrote:


"PBalmanno" wrote in message
news:vUUWg.1235$BC6.242@fed1read01...

| 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



  #22   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default One of lifes little mystery?

"JethroUK©" wrote in message
...

"PBalmanno" wrote in message
news:vUUWg.1235$BC6.242@fed1read01...

| 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

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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mystery Cell Jim H. Excel Discussion (Misc queries) 4 March 24th 06 06:40 PM
Mystery ALT key character illudes all methods of extraction! HELP! bxc2739 Excel Discussion (Misc queries) 6 March 16th 06 09:12 PM
Print Preview Mystery KemS Charts and Charting in Excel 0 November 11th 05 12:22 AM
Mystery: System Error and blank squares Bettergains Excel Discussion (Misc queries) 0 September 7th 05 05:56 PM
mystery function called 'TABLE' SongBear Excel Worksheet Functions 7 January 7th 05 05:56 AM


All times are GMT +1. The time now is 09:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"