Results 1 to 5 of 5

Thread: Basic conditional functions in Excel

  1. #1
    Join Date
    Nov 2009
    Posts
    47

    Basic conditional functions in Excel

    I began to learn Excel in detail and according to me its really hard to "make" Excel to use basic functions like conditions. I too led the mode of programming languages such as C, Java or PHP. Could someone explain how to do a test, such as this:
    Code:
     = if ( ( B1<> "" ) and ( C1= "M" or ( C1= "S" ) or ( C1= "C" ) ) )
    I thought to do it this way:

    Code:
    =IF ( AND ( B1<> "" ;OR ( C1= "C" ;C1= "M" ;C1= "S" ) ) ; "modify" ; "" )
    And in my case it works, but I carry too much trial and error instead of it logically. Can I have more explanations?

  2. #2
    Join Date
    Apr 2008
    Posts
    2,005

    Re: Basic conditional functions in Excel

    There are certainly many methods, I opted for the audit function "or" combined with the first checks only on B1. It therefore gives two 'if' knowing that in Excel syntax is:
    If (argument: if True: if False)

    In summary, if cell B1 is not empty then it checks cell C1.

  3. #3
    Join Date
    Nov 2009
    Posts
    47

    Re: Basic conditional functions in Excel

    Thank you this solution works for me.

    If possible, I want it really with an explanation, because I still find it difficult to reason with the IF (OR...). It is like writing reverse polish I flew in a td. It seems much less logical than writing if (condition or status). I would like to change that!

  4. #4
    Join Date
    May 2008
    Posts
    685

    Re: Basic conditional functions in Excel

    I understand that there are 2 things that disturb you:

    1. syntax that seems chaotic on (particular) AND and OR. Conceptually, this is not the same as what you have to be used:

    - I imagine that you would manipulate OPERATORS, which enables you to link elements placed on the left and right of the 'AND'
    => If ((A1 <>"") AND (C1 = "M ").......

    - In Excel, they are not operators but FUNCTIONS! And as we call a function by naming and then passing it parameters separated by semicolons, that gives
    = IF (AND (condition1 ; condition2); ResultIfTrue; ResultIfFalse)

    In fact, for appropriate function AND you can handle it alone, for example like this:
    Code:
      =AND(A1 = "rain", B1 = "sun").
    It returns TRUE or FALSE

    While an operator is more intuitive, because it is closer to the structure of a sentence in spoken language. But you get used to it, you'll see.

    2. readability formulas

    In Excel, when you go out for simple formulas you quickly find yourself with dozens of characters behind a "=" in a cell, with parentheses to separate your levels of nesting, and everything is spread over several lines ...

    This is significantly less readable (understatement) that the structured code with nice indentation and all, but Excel does not leave you much choice.

    Ultimately, you can put line breaks within the formulas - it is by Alt + Enter and it gives this:
    = IF (AND (condition1; condition2);
    ResultIfTrue;
    ResultIfFalse)

    Hoping that it helps you ...

  5. #5
    Join Date
    Nov 2008
    Posts
    1,054

    Re: Basic conditional functions in Excel

    For
    bla AND bli AND blu
    we do in excel as
    Code:
    = AND (bla;bli;blu)
    and we have the result as
    TRUE If bla = TRUE AND bli = TRUE AND blu = TRUE (all conditions are true)
    FALSE if bla = FALSE OR bli = FALSE OR blu = FALSE (at least one condition is false)

    For
    bla OR bli OR blu
    we do in excel as
    Code:
    =OR ( bla;bli;blu )
    and we have the result as
    TRUE If bla = TRUE OR bli = TRUE OR blu = TRUE (at least one condition is true)
    FALSE if bla = FALSE AND bli = FALSE AND blu = FALSE (all conditions are false)

    In both cases, you can put no. of parameter you want and you can nest them.

    AND and OR returns resulting output necessarily a boolean (TRUE / FALSE)

Similar Threads

  1. What are the basic functions of hard drive?
    By Bruno007 in forum Hardware Peripherals
    Replies: 2
    Last Post: 15-02-2012, 06:12 PM
  2. Excel Macros for conditional formatting statement in Excel
    By Kungfu Pandey in forum Microsoft Project
    Replies: 1
    Last Post: 08-01-2012, 08:35 PM
  3. lack of some basic functions in Nokia c3-02
    By Mudhol in forum Portable Devices
    Replies: 4
    Last Post: 29-09-2011, 03:51 AM
  4. Replies: 4
    Last Post: 27-11-2009, 08:55 AM
  5. Conditional formatting in excel
    By RockeЯ in forum Windows Software
    Replies: 4
    Last Post: 27-06-2009, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,722,652.42058 seconds with 17 queries